SQLCmd Task

The SQLCmdTask allows you to run .sql scripts through SQL Server’s “sqlcmd.exe” tool. Right now it only supports a few basic options that my projects are using. If you need additional feature support, feel free to submit a patch or add it to the issues list.

How to use the SQLCmdTask

Here is an example of how to use the SQLCmdTask

desc "Create the initial R1 database"
sqlcmd :create_initial_db do |sql|
  sql.command = "sqlcmd.exe"
  sql.server = "some_server"
  sql.database = "some_database"
  sql.username = "some_user"
  sql.password = "SHH!!! it's a secret!"
  sql.variables :New_DB_Name => "Albacore_Test"
  sql.scripts "RunCreateDatabase.sql", "RunUpdateDatabase.sql"
end

All of the settings are optional, except the command.

command (required)

Specify the location and name of the sqlcmd executable.

server (optional)

Specify the name of the server to connect to

database (optional)

Specify which database to use

username (optional)

Specify the user to log in and run the scripts as

password (optional)

Specify the password for the user to log in as

variables (optional)

Specify a set of variables to pass to sqlcmd, via a hash table. These are passed to sqlcmd using the “-v name=value” format. See the “http://msdn.microsoft.com/en-us/library/ms162773.aspx”SQLCmd documentation for more information.

scripts (optional)

Specify a set of script files for sqlcmd to execute, via an array. These are passed to sqlcmd using the “-i filename.sql” format. See the “http://msdn.microsoft.com/en-us/library/ms162773.aspx”SQLCmd documentation for more information.

Logging

The SQLCmdTask uses the built in logging options to provide some potentially useful information at run-time. By default, no additional information is logged, other than what sqlcmd.exe produces.

verbose mode

When the log_level is set to :verbose, the full command line call for sqlcmd will be logged. This includes the path to the sqlcmd exe as well as the command line parameters that are passed to it.

YAML configuration

This task supports configuration via an external YAML file. For more information, see the yamlconfig page.

Command Line Options

This task supports additional command line options, including a .parameters collection for passing in options that are not directly supported. For more information, see the commandline task options documentation.