The Execute Package Task allows us to call other packages present in SQL Server SQL Server, File System and Packages present in the same project as a part of its execution. In this article we are going to show you, Executing Packages present in the SQL Server using SSIS Execute Package Task.
In this example, we are going to execute SSIS Aggregate Transformation Package stored inside the SQL Server using Execute Package Task. We already explained about this Aggregate Transformation in Basic Mode package in our previous article so, Please refer it for understanding the package.
From the below screenshot you can see that, We already published the Aggregate Transformation in the MSDB Database.
You can see from 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 provide a useful description. As of now, we change the Name as Execute Package Task from SQL Server and left the description as it is.
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 as shown in the below screenshot. You can select 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 is secured with a password (In general Yes) then provide 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 is 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.