Skip to end of metadata
Go to start of metadata

Warning

Icon

SQL Automation Pack v1.1.x.x releases contain a rewritten version of SQL CI which is not compatible with 1.0.x.x versions. If you're upgrading from a 1.0.x.x version, you must reconfigure your existing SQL CI build steps after installation. For details of how to upgrade for different CI systems, see Upgrading.

Icon
This page applies to SQL Automation Pack v1.1 and later. If you're using an earlier version, see Using the SqlCI.exe command line in v1.0.4.2 and earlier.

SQL CI supports four steps that include default command line options to perform different continuous integration tasks. The steps are:

  1. Build - creates and validates the SQL creation script used to generate the NuGet package. See Build step command line options.
  2. Test - generates test data using SQL Data Generator and runs tSQLt tests against the NuGet package. Results are output in JUnit XML format. See Test step command line options.
  3. Sync - updates the existing database with the latest version in source control. See Sync step command line options.
  4. Publish - publishes the NuGet package to a NuGet feed ready for deployment. See Publish step command line options.
Icon

The test, sync and publish steps use the NuGet package created during the build step. These three steps are optional, but if you want to use them you must complete the build step first. 

There's also a command to activate your SQL Automation Pack license without having to configure a build, test or sync step. See Activate command line option.

Using the sqlCI.exe command line

The command line options for each step are case sensitive, but there's no restriction on the number of options you can use or the order in which you use them.

Options can be preceded by a forward slash '/' or two dashes '--'. In the examples on this page, we've used '/'.

Build step command line options

To run the build step, use this syntax:

For example:

The rest of this section provides examples of each build option.

/scriptsFolder

The path to the database scripts folder in source control, for example:

For the build VCS root, use a period (.):

/packageId

The name of the NuGet package you're creating, for example:

The name must not contain spaces.

/packageVersion

The package build number, for example:

If you're running sqlCI.exe from the command line, you have to specify this number manually, but if you're running sqlCI.exe as part of an automated build, you can use the build number variable. To set the build number variable:

If you're using a different build system, check the documentation provided by the vendor.

/temporaryDatabaseServer

Icon

You don't need to configure this option if you're using SQL Automation Pack 1.1.0.1840 and later, and you have LocalDB installed. SQL CI will use LocalDB by default.

The temporary database server name, for example:

During the build step, SQL CI needs to recreate your database on a temporary server. We recommend using localDB for this.

/temporaryDatabaseName (optional)

Icon

You should only use this option if:

  • you don't have permissions to create databases on your SQL Server.
  • you don't plan to run multiple builds in parallel that use the same temporary database. You must use a separate database for each build definition.

If you don't use this option, SQL CI will create a temporary database by default.

This option is only available if you're using SQL Automation Pack v1.1.1.1950 (includes SQL CI v2.0.1.315).

The name of an existing database on the temporary database server you previously specified. For example:

During the build step, SQL CI needs to recreate your database on a temporary server. You should use an empty database for this, because data will be overwritten during the build and test steps. 

/temporaryDatabaseUserName (optional)

The username for SQL authentication, for example:

If you don't specify this option, Windows authentication is used by default.

/temporaryDatabasePassword (optional)

The password for SQL authentication, for example:

This option is required if you're using the /temporaryDatabaseUserName option.

/outputFolder (optional)

The output folder path, for example:

If you don't specify this option, the current working directory is used by default. Make sure you have permission to create new files and folders in this directory.

/additionalCompareArgs (optional)

To include SQL Compare command line options use ="/options:<option_name>", for example:

To exclude default SQL Compare options, use ="/options:-<option_name>", for example:

The default options included in the build step are:

  • DecryptPost2KEncryptedObjects
  • IgnoreFillFactor
  • IgnoreWhiteSpace
  • IncludeDependencies
  • IgnoreFileGroups
  • IgnoreUserProperties
  • IgnoreWithElementOrder
  • IgnoreDatabaseAndServerName

You can include some options and exclude others in the same command. For example:

To include SQL Compare command line switches:

If the command line option or switch contains a space, you need to enclose the command in double quotes and insert escape characters before each quote. For example:

If you don't escape the double quotes, the option will be invalid.

For more information about SQL Compare command line options and switches, see Using the command line.

/licenseSerialKey (optional)

To activate the SQL Automation Pack license on the machine on which the build agent is running, enter the serial number. For example:

For information on finding your Automation Pack serial number, see Licensing. If you don't enter a serial number, a 28 day free trial of the SQL Automation Pack will start automatically (14 day trial if you're using SQL Automation Pack v1.1.0.1840 and earlier).

If you have multiple serial numbers, separate them using commas without spaces. For example:

Test step command line options

Icon

If you want to run tSQLt tests against the package, you'll need to enable the common language runtime (CLR) integration feature on the /temporaryDatabaseServer. For more information, see Enabling CLR Integration (TechNet article).

To run the test step, use this syntax:

For example:

The rest of this section provides examples of each test option.

/package

The path to the package that was created in the build step, for example:

/temporaryDatabaseServer

Icon

You don't need to configure this option if you're using SQL Automation Pack 1.1.0.1840 and later, and you have LocalDB installed. SQL CI will use LocalDB by default.

The temporary database server name, for example:

During the build step, SQLCI recreates your database on a temporary server. During the test step, tSQLt tests are run against it. We recommend using localDB for this.

/temporaryDatabaseName (optional)

Icon

You should only use this option if:

  • you don't have permissions to create databases on your SQL Server.
  • you don't plan to run multiple builds in parallel that use the same temporary database. You must use a separate database for each build definition.

If you don't use this option, SQL CI will create a temporary database by default.

This option is only available if you're using SQL Automation Pack v1.1.1.1950 (includes SQL CI v2.0.1.315).

The name of an existing database on the temporary database server you previously specified. For example:

During the build step, SQL CI recreates your database on a temporary server. During the test step, tSQLt are run against it.

/temporaryDatabaseUserName (optional)

The username for SQL authentication, for example:

If you don't specify this option, Windows authentication is used by default.

/temporaryDatabasePassword (optional)

The password for SQL authentication, for example:

/outputFolder (optional)

The output folder for test results, for example:

If you don't specify this option, the current working directory is used by default.

/sqlDataGenerator (optional)

Use test data created by SQL Data Generator. If you've already created a SQL Data Generator project file, include the file name. For example:

If you don't have an existing project file, include this option without specifying a value. This will populate the temporary database with test data that's been generated automatically. For more information about SQL Data Generator, see SQL Data Generator 3 documentation.

/additionalCompareArgs (optional)

To include SQL Compare command line options use ="/options:<option_name>", for example:

To exclude default SQL Compare options, use ="/options:-<option_name>", for example:

The default options included in the test step are:

  • DecryptPost2KEncryptedObjects
  • IgnoreFillFactor
  • IgnoreWhiteSpace
  • IncludeDependencies
  • IgnoreFileGroups
  • IgnoreUserProperties
  • IgnoreWithElementOrder
  • IgnoreDatabaseAndServerName

You can include some options and exclude others in the same command. For example:

To include SQL Compare command line switches:

If the command line option or switch contains a space, you need to enclose the command in double quotes and insert escape characters before each quote. For example:

If you don't escape the double quotes, the option will be invalid.

For more information about SQL Compare command line options and switches, see Using the command line.

/licenseSerialKey (optional)

To activate the SQL Automation Pack license on the machine on which the build agent is running, enter the serial number. For example:

For information on finding your SQL Automation Pack serial number, see Licensing. If you don't enter a serial number, a 28 day free trial of the SQL Automation Pack will start automatically (14 day trial if you're using SQL Automation Pack v1.1.0.1840 and earlier).

If you have multiple serial numbers, separate them using commas without spaces. For example:

/runOnly (optional)

To run a specific test class or test instead of every test, enter the test class and test name. For example:

If you want to run every test, don't specify this option.

Icon

This command is only available in SQL Automation Pack 1.1.0.1840 and later.

Sync step command line options

To run the sync step, use this syntax:

For example:

The rest of this section provides examples of each sync option.

/databaseName

The target database to update with the changes in source control. This must be an existing database on the server; the runner does not create the database for you. For example:

/package 

The path to the package that was created in the build step, for example:

/databaseServer

The target database server name, for example:

/databaseUserName (optional)

The username for SQL authentication, for example:

/databasePassword (optional)

The password for SQL authentication, for example:

/additionalCompareArgs (optional)

To include SQL Compare command line options use ="/options:<option_name>", for example:

To exclude default SQL Compare options, use ="/options:-<option_name>", for example:

The default options included in the sync step are:

  • DecryptPost2KEncryptedObjects
  • IgnoreFillFactor
  • IgnoreWhiteSpace
  • IncludeDependencies
  • IgnoreFileGroups
  • IgnoreUserProperties
  • IgnoreWithElementOrder
  • IgnoreDatabaseAndServerName
  • IgnoretSQLt
Icon

If the target database you're syncing to contains tSQLt tests, you must exclude the IgnoretSQLt option.

You can include some options and exclude others in the same command. For example:

To include SQL Compare command line switches:

If the command line option or switch contains a space, you need to enclose the command in double quotes and insert escape characters before each quote. For example:

If you don't escape the double quotes, the option will be invalid.

For more information about SQL Compare command line options and switches, see Using the command line.

/licenseSerialKey (optional)

To activate the SQL Automation Pack license on the machine on which the build agent is running, enter the serial number. For example:

For information on finding your SQL Automation Pack serial number, see Licensing. If you don't enter a serial number, a 28 day free trial of the SQL Automation Pack will start automatically (14 day trial if you're using SQL Automation Pack v1.1.0.1840 and earlier).

If you have multiple serial numbers, separate them using commas without spaces. For example:

Publish step command line options

To run the publish step, use this syntax:

For example:

The rest of this section provides examples of each publish option.

/package

The path to the package that was created in the build step, for example:

/nugetFeedUrl

The fully-qualified URL for your NuGet feed. If your are using Deployment Manager, this is the URL for Deployment Manager with /nuget/ added to the end. For example:

 /nugetFeedApiKey (optional)

The API key for your NuGet feed, for example:

If you are using a public NuGet feed, the API key is not required.

Activate command line option

To activate the SQL Automation Pack license without having to configure a build, test or sync step, use this syntax:

For example:

This also activates SQL CI. 

For information on finding your SQL Automation Pack serial number, see Licensing.

If you have multiple serial numbers, separate them using commas without spaces. For example:

 

  • No labels