DATASTAR: Command Line

Overview

The task is used to execute the Command Line Utility. This can be run one of the following modes:

Deployment - this provides the ability to deploy a packaged build of scripts to a target environment and optionally create the scripts necessary to reverse the changes.

Reversal - the reversal mode will apply the scripts necessary to back out a previous deployment where reversal scripts were generated.

Generate - the generate mode allows you to generate the scripts from a template so that you can automate the extract from an environment.

Export - the export mode allows to export scripts and deployment logs that have been persisted in the deployment tables from previous deployments.

Dependencies

The DataStar Command Line utility requires .NET Framework Version 4.7.2. This is required as .NET 4.7.2 is the first version of the full .NET Framework that is fully .NET Standard 2.0 compliant without any additional dependencies.

Contact Information

Please report a problem to support@absolute.technology if you are facing problems in making this task work. You can also share feedback about the task like, what more functionality should be added to the task, what other tasks you would like to have, at the same place.

Task Parameters

General

  • Mode: Specify the mode depending on the purpose of the task. This controls what options can be specified and will result in sections and options being shown or hidden.
  • Manifest Directory: [Deployment, Reversal] Specify the directory where the manifest files (for deployments this is where the build is extracted to), the manifest file is the instruction file that controls the execution order of the scripts.
  • History Id: [Reversal, Extract] Specify the database deployment history identifier, this optional and is used to identify deployment to be reversed or extracted. If not specified and you have provided the work item reference then this history id will be taken as the most recent history record for the work item.
  • Artifacts: [Deployment, Reversal] Specify the artifact files that should be executed and their order. This is an optional setting and if left blank all manifest files (*.mf) will be processed in alphanumeric order.
  • Work Item: [Deployment, Reversal, Extract] Specify the numeric work item reference that identifies the deployment package (this does not have to be a TFS Work Item reference, but must be an integer).
  • Version Reference: [Deployment, Reversal] Optionally you can include a version reference used to identify a specific version being deployed. This must also be an integer.

Database Connection Settings

The task needs to connect to a database, the connection settings are specified with the following parameters. The parameters listed with a * are required parameters for the task.

Microsoft Settings

  • Data Source: The server (and instance if applicable) in the format "server\\instance".

  • Initial Catalog: The name of the database associated with the connection.

  • User Name: The user to be used when connecting to the database. If not specified then the connection will be made using Integrated Security.

  • Password: Optional password if not using default credentials. You should use a variable to encrypt the password by marking the variable as secure.

Reversal Options

[Deployment, Reversal] If reversal is enabled then the task will take a snapshot of the target database prior to executing the scripts and will generate the scripts necessary to revert whatever changes are made.

With Reversal: Tick the check box to enable the reversal mode.

Template Directory: The directory holding the templates that should be used to generate the reversal scripts. The templates can be included in the release task as a build artifact, we suggest using a location such as $(System.ArtifactsDirectory)/DataStar.Templates/Templates

Output Directory: The location where the output scripts should be generated. As an example this could be set to: $(System.ArtifactsDirectory)/DataStar.Output

Auditing Options

[Deployment, Reversal] If auditing is enabled then the task will store details of the deployment is a set of audit tables, including deployment log files and reversal scripts.

Enable Auditing: Tick the check box to enable auditing.

Audit Table Name: The name of the audit table can be overridden, otherwise it would be set to [dbo].[ADS_DEPLOYMENT_HISTORY]

CREATE TABLE [dbo].[ADS_DEPLOYMENT_HISTORY] (
    ID IDENTITY(1,1) NOT NULL,
    WORK_ITEM NOT NULL,
    VERSION NOT NULL,
    ARTIFACT(50) NOT NULL,
    DATE_STAMP NOT NULL,
    LOG_OUTPUT(max) NULL,
    REVERSAL_ID NULL,
    [STATUS] char(1) NULL,
    CONSTRAINT [ADS_DEPLOYMENT_HISTORY_PK] PRIMARY KEY CLUSTERED ([ID] ASC));

Reversal Table Name: The name of the audit table containing the reversal scripts can be overridden, otherwise it would be set to [dbo].[ADS_DEPLOYMENT_REVERSAL]​

CREATE TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL] (
    ID NOT NULL,
    SEQUENCE NOT NULL,
    CATEGORY(100) NOT NULL,
    NAME(100) NOT NULL,
    SCRIPT(max) NULL,
    CONSTRAINT [ADS_DEPLOYMENT_REVERSAL_PK] 
    PRIMARY KEY CLUSTERED ([ID] ASC, [SEQUENCE] ASC))

ALTER TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL]
ADD CONSTRAINT ADS_DEPLOYMENT_REVERSAL_FK1
FOREIGN KEY ([ID])
REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
ON DELETE CASCADE

Summary Table Name: The name of the audit summary table contains details of the number of rows inserted, updated or deleted against each table. It is optional, otherwise it would be set to [dbo].[ADS_DEPLOYMENT_SUMMARY]

CREATE TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY] (
    ID NOT NULL,
    TABLE_NAME(255) NOT NULL,
    INSERTED NOT NULL,
    UPDATED NOT NULL,
    DELETED NOT NULL
    CONSTRAINT [ADS_DEPLOYMENT_SUMMARY_PK] 
    PRIMARY KEY CLUSTERED ([ID] ASC, [TABLE_NAME] ASC))

ALTER TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY]
ADD CONSTRAINT ADS_DEPLOYMENT_SUMMARY_FK1
FOREIGN KEY ([ID])
REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
ON DELETE CASCADE

Generate Options

The generate option enables the automatic extraction of components from a database environment. If you extract the files to a file system the extract will only modify those files have have changed (or been deleted). This can be then be used to determine if anything has changed and report differences.

Template Directory: The directory holding the templates that should be used to generate the reversal scripts. The templates can be included in the release task as a build artifact, we suggest using a location such as $(System.ArtifactsDirectory)/DataStar.Templates/Templates

Template File Name: The name of the template file that is to be used to generate the extract. This can be the actual name or a regular expression if you want to match to multiple templates. For example use the regular expression ".*.xml?$" to match all files ending in ".xml". Note: you cannot specify a dynamic query override if the selection includes multiple template files.

Dynamic Query: The optional override to the dynamic query in the template. If this is specified the components to be extracted will be identified using the specified SQL query. This can only be used if you are processing a single template.

Formatting Rules: The optional formatting rules for the component file names, this can be specified as XML as per the element defined in the DataStar UI workspace file as show below:

<FormattingRules CaseFormatting="Lowercase" SingleSpace="true">
  <SearchReplaceItem SearchText=" " ReplaceText="_" />
  <SearchReplaceItem SearchText=":" ReplaceText="" />
</FormattingRules>

Alternatively if you prefer you can use a JSON string format, as shown in the example below:

{ 
    "caseFormatting": "Lowercase",
    "singleSpace": "true",
    "searchReplaceItems": [
      { "searchText": " ", "replaceText": "_" },
      { "searchText": ":", "replaceText": "" }
    ]
}

Output Directory: The location where the output scripts should be generated. As an example this could be set to: $(System.ArtifactsDirectory)/DataStar.Output

Include Delete Check : If this is true any additional files (i.e. those not identified in the template dynamic queries) will be re-extracted using the parameters contained in those files. If these no longer contain data the the script will be modified even though it was not in a dynamic query result set. This allows you to also identify items that have been removed.

License Settings

The license key is managed under the Services option in TFS, you should create a new service connection using the DataStar License Key connection type. Note that this does not connect to an external URL, it is only used to contain the license key as a token so that it can be decoupled from a release version. When you create the service key enter the a connection name and paste you license key in the API Token.

License Key: Select the service connection containing the license key from the drop down list.

Advanced Options

The following parameters are selectively shown under the Advanced Options:

  • Base Path: The optional path to the DataStar.CmdLine Executable, if not specified the working directory is used, you should only use this setting if you wanted to run a different version of the DataStar.CmdLine tool.
  • Fail on Standard Error: If this is true, this task will fail if any errors are written to the Standard Error stream.

Known Issues:

None.