Execute Packages in SQL Server using SSIS Execute Package Task

The Execute Package Task allows us to call other packages present in SQL Server, File System, and Packages present in the same project as a part of its execution. In this article, we are executing Packages present in the SQL Server using SSIS Execute Package Task.

In this example, we are going to execute the SSIS Aggregate Transformation Package stored inside the SQL Server using the Execute Package Task. We already explained this Aggregate Transformation in Basic Mode. So, Please refer to it.

From the below screenshot, you can see that we already published the Aggregate Transformation in the MSDB database.

Execute Packages in SQL Server using SSIS Execute Package Task 0

You can see from the below screenshot, [Aggregate Transformation Basic Mode] table is empty. If not, Please truncate the table using T-SQL or add Execute SQL Task.

Execute Packages in SQL Server using SSIS Execute Package Task 1

Execute Packages in SQL Server using SSIS Execute Package Task Example

Drag and drop the Execute Package Task from the toolbox to Control Flow Region.

Execute Packages in SQL Server using SSIS Execute Package Task 1

Double click on the Execute Package Task to configure the package. In the General Tab, we can change the name and description. As of now, we changed the Name as Execute Package Task from SQL Server.

Execute Packages in SQL Server using SSIS Execute Package Task 2

Click on the package tab to configure the package location and connection string

Execute Packages in SQL Server using SSIS Execute Package Task

In this example, We are executing the package located in the SQL Server so, Please change the ReferenceType from Project Reference to External reference. Please refer SSIS Execute Package Task Project Reference article for calling packages inside the same project and Execute Packages in File System using SSIS Execute Package Task for calling packages in File System

Execute Packages in SQL Server using SSIS Execute Package Task

Here let us select the SQL Server as a package source

Execute Packages in SQL Server using SSIS Execute Package Task 3

Click on the New Connection to configure or select the Server settings. If you already created the connection before then, it will display the list. You can choose the required connection. For now, click on the <New Connection…>

Execute Packages in SQL Server using SSIS Execute Package Task 4

Once you click on the <New Connection…>, it will open the Connection Manager Editor to select the Provider Name, Server Name, and Database Name.

Execute Packages in SQL Server using SSIS Execute Package Task 5

Click ok to finish selecting the Database. Now we have to select the package we want to execute. So, click on the Browse button (…) beside the PackageName option

Execute Packages in SQL Server using SSIS Execute Package Task 6

It will open the Select Package window to select the required package. From the below screenshot, you can observe, we are selecting AGGREGATE BASIC from the list. We have only one package at the moment. So it is displaying one, but in real-time, it may be more.

Execute Packages in SQL Server using SSIS Execute Package Task 7

Click ok to select the package. If the Aggregate Transformation package secured with a password (In general, Yes), enter an appropriate password.

Execute Packages in SQL Server using SSIS Execute Package Task 8

Click ok to finish configuring the SSIS Execute Package Task. Let us Run the Package and see whether we successfully called the package present in the SQL Server using Execute Package Task or not.

Execute Packages in SQL Server using SSIS Execute Package Task 9

It seems our Execute Package Task not thrown any errors. Let us open the SQL Server Management Studio and check the result.

Execute Packages in SQL Server using SSIS Execute Package Task 10

Well, we successfully called the package present in the SQL Server using SSIS Execute Package Task.