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.
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 Example
Drag and drop the Execute Package Task from the toolbox to 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 as 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 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
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 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.
Click ok to select the package. If the Aggregate Transformation package 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 not thrown any errors. Let us open the SQL Server Management Studio and check the result.
Well, we successfully called the package present in the SQL Server using SSIS Execute Package Task.