Problem
We have a maintenance solution where we use the SQLCMD command line tool to automate some dynamic SQL. At some point during the workflow, we need to execute an Integration Services Package. Is this possible using SQLCMD?
Solution
The SQLCMD utility is a command line tool (although you can use in SQL Server Management Studio as well), to connect to a SQL Server instance and execute T-SQL statements, stored procedures and script files. It’s also available on Linux. If you have installed SQL Server on your machine, SQLCMD should be installed as well and its location added to the Windows Path variable. You can find an introduction to the tool in this tip.
In this tip, we’ll find out how we can use this utility to start an Integration Services (SSIS) package.
Execute an SSIS Package from SQLCMD
Since the purpose of SQLCMD is to run T-SQL statements against a SQL Server instance, it makes sense to start the execution of an SSIS package using T-SQL. In our sample set-up, we use the same test package as in the tip Execute SSIS Package from PowerShell – Part 1. This package will write a single line of text to a table in a SQL Server database. The contents of the line can be changed using a parameter. Please refer to this tip and how to create this package and how to create the destination table where the lines will be written.
Start Package with SQL Server Agent
Perhaps the most straight forward method of executing a package is creating a SQL Server Agent job and starting this job with SQLCMD. Create a new job and add a new job step. Choose for the “SQL Server Integration Services Package” type, choose your server and locate the SSIS package.
In the configuration tab, you have the option to change the value of the parameter using one of two methods:
- Override the design-time value by entering a new value.
- Using an environment pre-defined in the SSIS catalog. This environment will contain an environment variable which will override the parameter value. You can find more info in the tip Setup Environment Variables in SQL Server Integration Services.
For the first option, if you click the ellipsis, you’ll get a pop-up where you can change the value:
To start SQLCMD and connect to the server in interactive mode, execute the following command in a command prompt:
SQLCMD -S myservername
When connected, we can use the system stored procedure sp_start_job to start the job which will then execute the SSIS package:
EXEC msdbo.dbo.sp_start_job 'TestJob';
GO
Don’t forget the GO batch separator, as this will tell SQLCMD to execute the previous lines of T-SQL.
The result:
Albeit simple to set-up, using a SQL Server Agent job has a couple of drawbacks:
- Starting a job is asynchronous. This means the success message you’ll see in SQLCMD will only tell you the job has successfully started. It’s not an indication if the job (or the SSIS package) actually succeeded or even finished at all. You can retrieve the information out of the msdb database (for the job) or the SSISDB (for the package), but this means extra work. If you’re looking for a synchronous solution, where SQLCMD waits till the job is finished, take a look at the tip Custom sp_start_job to delay next task until SQL Agent Job has completed.
- You have the possibility to change the parameter value when creating the job by either entering a new literal value, or by specifying an environment. It’s harder though to change the value at runtime. You could use the sp_update_jobstep system stored procedure to modify the job step and either specify a new literal value or another environment. If other processes use the Agent job or the SSIS environment, this might not be a good option as you might change the behavior of those processes well.
Start Package with Stored Procedures
When using the SSIS project deployment model – introduced in SQL Server 2012 – there’s a whole bunch of stored procedures available in the SSISDB database to manage the SSIS catalog. With those stored procedures, we can start an SSIS package. However, this requires multiple steps:
- If needed, retrieve the ID of the reference that binds the SSIS package to an environment.
- Create an execution object.
- Optionally set execution parameter value, which is for example needed to execute the package synchronously.
- Start the execution.
To simplify all this, we can encapsulate the different steps into one stored procedure:
CREATE PROCEDURE [dbo].[RunPackage] (@PackageName VARCHAR(150) ,@FolderName VARCHAR(150) ,@ProjectName VARCHAR(150) ,@EnvironmentName VARCHAR(150) ,@Synchronized BIT = 1 -- run synchronously by default ) AS DECLARE @execution_id BIGINT ,@ref_id INT -- find the environment reference ID SELECT @ref_id = er.[reference_id] FROM SSISDB.[catalog].[environment_references] er JOIN SSISDB.[catalog].[projects] p ON er.[project_id] = p.[project_id] JOIN SSISDB.[catalog].[folders] f ON p.[folder_id] = f.[folder_id] WHERE er.[environment_name] = @EnvironmentName AND p.[name] = @ProjectName AND f.[name] = @FolderName EXEC [SSISDB].[catalog].[create_execution] @package_name = @PackageName ,@execution_id = @execution_id OUTPUT ,@folder_name = @FolderName ,@project_name = @ProjectName ,@use32bitruntime = False ,@reference_id = @ref_id EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id ,@object_type = 50 ,@parameter_name = N'SYNCHRONIZED' ,@parameter_value = @Synchronized EXEC [SSISDB].[catalog].[start_execution]@execution_id RETURN @execution_id GO
This stored procedure assumes an environment is setup and linked to the SSIS package. With the following script, we can use this procedure to start the execution of an SSIS package:
DECLARE @execid INT;
EXEC @execid = dbo.[RunPackage]
@PackageName = 'ExecuteFromSQLCMD.dtsx'
,@FolderName = 'MSSQLTIPS'
,@ProjectName = 'MSSQLTIPS'
,@EnvironmentName = 'TEST';
SELECT @execid;
GO
With the execution ID returned by the procedure, you can search for the result of the package in the SSISDB database, e.g. in the catalog view catalog.executions.
Using stored procedures to execute the SSIS package mitigates the shortcomings of a SQL Server Agent job:
- The SSIS package can be executed synchronously.
- It’s easier to modify input parameters.
- You also get the execution ID of the package, which makes it easier to find if the package executed successfully.
Using the Package Deployment Model
If you’re not using the project deployment model, but rather the package deployment model of SSIS, which was the standard way of working before SQL Server 2012, you can execute a package using the DTEXEC utility or the Azure-DTEXEC utility. Since both are command line utilities, it doesn’t make much sense to try to execute those from within SQLCMD (which is also a command line utility). You’d rather use those in the workflow where SQLCMD is used. Of course, you can also use the SQL Server Agent method to execute a package stored in a package deployment model.
Next Steps
- The following tips explain how you can execute SSIS packages from PowerShell: part 1, part 2 and part 3.
- Execute SSIS Package from Stored Procedure in SQL Server
- Getting started with SSIS? Check out this tutorial.
