In this article, we will explore the process of executing and deploying SSIS packages in SQL Server. SSIS (SQL Server Integration Services) is a powerful tool that allows you to create and manage data integration workflows. By deploying SSIS packages to the SSIS catalog, you can easily execute them and monitor their execution.

Step 1: Create a Destination Table

The first step is to create a destination table where our SSIS package will write data. In SQL Server Management Studio, execute the following T-SQL code to create the table:

CREATE TABLE TestDB.dbo.Test (
    [TimeStamp] [datetime] NULL
) ON [PRIMARY]

Step 2: Create an SSIS Project

Create a new SSIS project in Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT). Give the project a meaningful name, such as “SSIS Catalog Demo”.

Step 3: Modify the SSIS Package

Modify the default SSIS package in the project. Add an “Execute SQL Task” component to the package. Configure the component to use the appropriate connection manager and execute the desired SQL statement. For example, you can use the following T-SQL code to insert the current timestamp into the destination table:

INSERT INTO TestDB.dbo.Test (TimeStamp) VALUES (GETDATE())

Step 4: Execute the SSIS Package from Development Studio

Execute the SSIS package from within the development studio by clicking on the “Start Debugging” button. This will run the package and insert a record into the destination table. You can verify the success of the execution by executing a SELECT query on the table.

Step 5: Create the SSISDB Integration Services Catalog

In SQL Server Management Studio, browse to the Integration Services Catalogs and create a new catalog named “SSISDB”. This catalog will serve as the repository for our deployed SSIS packages.

Step 6: Deploy the SSIS Project to the SSIS Catalog

Right-click on the SSIS project in the development studio and select the “Deploy” option. Follow the deployment wizard to specify the server and catalog path for the deployment. Once the deployment is complete, you can verify the success by refreshing the SSISDB folder in the Integration Services Catalog.

Step 7: Execute the SSIS Package from the Catalog

Right-click on the deployed package in the SSIS catalog and select the “Execute” option. This will execute the package and you can view the execution report to confirm its success. Execute a SELECT query on the destination table to verify that the package has inserted another record.

Step 8: Create the SSIS Catalog Execution Script

If you want to execute the SSIS package from a T-SQL script, you can generate the script from the Execute Package GUI. Right-click on the package and select “Execute” and then choose “New Query Editor Window” from the Script menu. This will open a new query editor window containing the SQL code to execute the package.

Step 9: Run the Script

Execute the generated script to run the SSIS package from the catalog. Execute a SELECT query on the destination table to confirm that the package has inserted another record.

Conclusion

In this article, we have demonstrated how to deploy an SSIS package to the SSIS catalog and execute it directly from the Execute Package GUI or from a SQL script generated by the GUI. By following these steps, you can effectively manage and execute your SSIS packages in SQL Server.