Execute SSIS Package with SQLCMD and Parameters


By:   |   Updated: 2020-11-24   |   Comments   |   Related: More > Integration Services Execute Package Options


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.

sample package

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.

configure new jobstep

In the configuration tab, you have the option to change the value of the parameter using one of two methods:

  1. Override the design-time value by entering a new value.
  2. 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.
job step configuration

For the first option, if you click the ellipsis, you'll get a pop-up where you can change the value:

edit literal 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.

start job with sqlcmd

The result:

result with agent job

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
sqlcmd window

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


Last Updated: 2020-11-24


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips



Comments For This Article





download





Recommended Reading

Different ways to execute a SQL Server SSIS package

DTEXEC Command Line Parameters Using Command Files

How To Execute an Integration Services Package from a SQL Server Stored Procedure

Execute SSIS Package from Stored Procedure in SQL Server

How to Execute SSIS Packages from SQL Server Agent














get free sql tips
agree to terms