How to deploy and execute an SSIS package from the SSISDB catalog

Stan Kulp, 2018-12-24 (first published: 2016-05-24)

In this walk-through we will create a simple SSIS package that writes to a database table, deploy the SSIS project containing the package to the SQL Server Integration Service’s catalog and execute the package (1) from the SSIS Catalog’s Execute Package GUI and (2) from a T-SQL script generated by the Execute Package GUI.
1. Create a destination table
Create a table for our test package to populate by executing the following T-SQL code… 
CREATE TABLE TestDB.dbo.Test (
[TimeStamp] [datetime] NULL
in SQL Server Managment Studio.
2. Create an SSIS project
Create an SSIS project in Business Intelligence Development Studio named SSIS Catalog Demo.
3. Modify the project’s default SSIS package
A default SSIS package named Package.dtsx was created by the project.
Double-click the Execute SQL Task node of the SSIS Toolbox to add an Execute SQL Task component to the default package.
Right-click in the Connection Managers panel and select New OLE DB Connection from the popup menu.
Click the New button to create a new connection manager.
Enter the relevant connection information and click the OK button.
Right-click the Execute SQL Task component and select Edit from the popup menu.
Click on the Connection node of the Execute SQL Editor panel and select the TestServer connection manager.
Click on the SQLStatement node of the Execute SQL Task Editor…
…to bring up the Enter SQL Query panel.
Paste the following T-SQL code…
INSERT INTO TestDB.dbo.Test (TimeStamp) VALUES (GETDATE())
…into the Enter SQL Query panel and click the OK button.
Click the OK button on the Execute SQL Task Editor panel.

4. Execute the SSIS package from Development Studio

Click on the Start Debugging  green arrow button of the package editor.
The green check mark indicates that the package ran successfully and inserted a record into the TestDB.dbo.Test table.
5. Confirm that the package worked
Enter the following T-SQL code…
SELECT * FROM TestDB.dbo.Test
…into SQL Server Management Studio and execute it to confirm that a record was added to the table.
6. Create the SSISDB Integration Services catalog
Browse to the Integration Services Catalogs of SQL Server 2012 or above in Management Studio.
Right-click on the Integration Services node and select Create Catalog from the popup menu.
Populate the Password and Retype Password fields and click the OK button.
An empty folder named SSISDB has been created under the Integration Services Catalog folder.
Right-click on the SSISDB folder and select the Create Folder menu item.
Enter the folder name Test and click the OK button.
Empty folders named Projects and Environments have been created under the Test folder.
7. Deploy the SSIS project to the SSIS catalog
Right-click on the SSIS Catalog Demo project name in Development Studio and select the Deploy menu item.
Click on the Next button in the Integration Services Deployment Wizard panel.
Enter the name of the server and the path to the catalog to be created and click Next.
Click the Deploy button.
Click the Close button of the deployment wizard panel after obserivng the four green checkmarks that confirm the deployment was successful.
Right-click the SSISDB folder and select the Refresh menu item.
Expand the folders under the Test folder to confirm that the package has been deployed to the server.

8. Execute the SSIS package from the catalog with the Execute Package GUI.

Right-click on the package and select Execute from the popup menu.
Click the OK button on the Execute Package GUI panel.
Click the Yes button on the dialog box that asks if you wish to open the overview report.
A report opens in SQL Server Management Studio that indicates our package was successfully executed.
Execute the previous SELECT query again to confrim that another record has been added to the table by the SSIS package.
9. Create the SSIS catalog execution script from the Execute Package GUI
Right-click the package and select Execute once again.
This time select New Query Editor Window from the Script menu at the top of the Execute Package panel, then click the OK button.
Click the No button on the dialog box to prevent a report from being created.
Observe that a new query editor window containg SQL code has been opened in SQL Server Management Studio 

10. Run the script

Execute the script to run the SSIS package from the catalog.

Execute the SELECT query again to confirm that the package inserted another record into the table.


In this article we demonstrated how to deploy an SSIS package to the SSIS catalog and how to execute the package directly from the Execute Package GUI and from a SQL Script generated by the Execute Package GUI.


4.72 (18)




4.72 (18)

Related content

Reproduced with kind permission from the blog of Ashvini Sharma (MSFT)

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

Additional Articles


1,328 reads

How to Asynchronously Execute a DTS package from ASP or ASP.NET

The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.

To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.

Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!

Additional Articles


2,663 reads

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

Additional Articles


1,761 reads