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 will 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.
The screenshot below shows that we have already published the Aggregate Transformation in the MSDB database.
You can see screenshot below the [Aggregate Transformation Basic Mode] table is empty. If not, Please truncate the table using T-SQL or add an Execute SQL Task.
Execute Packages in SQL Server using SSIS Execute Package Task Example
Drag and drop the Execute Package Task from the toolbox to the Control Flow Region.
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 to Execute Package Task from SQL Server.
Click on the package tab to configure the package location and connection string
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 to the Execute Package Task Project Reference article for calling packages inside the same project and Execute Packages in File System for calling packages in the File System.
Here, let us select the SQL Server as a package source
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…>
Once you click on the <New Connection…>, it will open the Connection Manager Editor to select the Provider Name, Server Name, and Database Name.
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
It will open the Select Package window to select the required package. From the screenshot below, you can observe we are selecting AGGREGATE BASIC from the list. We have only one package at the moment. So it displays one, but in real-time, it may be more.
Click ok to select the package. If the Aggregate Transformation package is secured with a password (In general, Yes), enter an appropriate password.
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.
It seems our Execute Package Task has not thrown any errors. Let us open the Management Studio and check the result.
We successfully called the package present in the SQL Server using the SSIS Execute Package Task.
