Deploy an SSIS project from SSMS with Transact-SQL
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).
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).
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.
Open SQL Server Management Studio.
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.
Click Connect. The Object Explorer window opens in SSMS.
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.
In SSMS, open a new query window and paste the following code.
Update the parameter values in the
catalog.deploy_projectstored procedure for your system.
Make sure that SSISDB is the current database.
Run the script.
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
- Consider other ways to deploy a package.
- Run a deployed package. To run a package, you can choose from several tools and languages. For more info, see the following articles: