Edit

Deploy an SSIS project from SSMS with Transact-SQL

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

This quickstart demonstrates how to use SQL Server Management Studio (SSMS) to connect to the SSIS Catalog database, and then use Transact-SQL statements to deploy an SSIS project to the SSIS Catalog.

SQL Server Management Studio is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. For more info about SSMS, see SQL Server Management Studio (SSMS).

Prerequisites

Before you start, make sure you have the latest version of SQL Server Management Studio. To download SSMS, see Download SQL Server Management Studio (SSMS).

Supported platforms

You can use the information in this quickstart to deploy an SSIS project to the following platforms:

  • SQL Server on Windows.

You cannot use the information in this quickstart to deploy an SSIS package to Azure SQL Database. The catalog.deploy_project stored procedure expects path to the .ispac file in the local (on premises) file system. For more info about deploying and running packages in Azure, see Lift and shift SQL Server Integration Services workloads to the cloud.

You cannot use the information in this quickstart to deploy an SSIS package to SQL Server on Linux. For more info about running packages on Linux, see Extract, transform, and load data on Linux with SSIS.

Supported authentication method

Refer to authentication methods for deployment.

Connect to the SSIS Catalog database

Use SQL Server Management Studio to establish a connection to the SSIS Catalog.

  1. Open SQL Server Management Studio.

  2. In the Connect to Server dialog box, enter the following information:

    Setting Suggested value More info
    Server type Database engine This value is required.
    Server name The fully qualified server name
    Authentication SQL Server Authentication
    Login The server admin account This account is the account that you specified when you created the server.
    Password The password for your server admin account This password is the password that you specified when you created the server.

  3. Click Connect. The Object Explorer window opens in SSMS.

  4. In Object Explorer, expand Integration Services Catalogs and then expand SSISDB to view the objects in the SSIS Catalog database.

Run the T-SQL code

Run the following Transact-SQL code to deploy an SSIS project.

  1. In SSMS, open a new query window and paste the following code.

  2. Update the parameter values in the catalog.deploy_project stored procedure for your system.

  3. Make sure that SSISDB is the current database.

  4. Run the script.

  5. In Object Explorer, refresh the contents of SSISDB if necessary and check for the project that you deployed.

DECLARE @ProjectBinary AS varbinary(max)
DECLARE @operation_id AS bigint
SET @ProjectBinary =
    (SELECT * FROM OPENROWSET(BULK '<project_file_path>.ispac', SINGLE_BLOB) AS BinaryData)

EXEC catalog.deploy_project @folder_name = '<target_folder>',
    @project_name = '<project_name',
    @Project_Stream = @ProjectBinary,
    @operation_id = @operation_id out

Next steps