The Execute Package task in SSIS is the most useful in SQL Server Integration Services because it allows us to call other packages from the workflow.
The following are a few advantages of using Execute Package task in SSIS:
- We can break down the complex packages into multiple packages. And then, using the Execute Package task, we can call them at any time. For example, Instead of performing 10 transformations in a single SSIS package. We can break the single package into 10 packages. Then using the Execute Package task, we can call them.
- If we break down the complex packages into multiple packages, we can reuse them numerous times. It means Code usability.
- If we break down the complex packages into multiple packages, maintenance will be easy and cheaper.
- If we break down the complex packages into multiple packages, it helps us to divide the work to the whole team where each individual can work on a single package, and module lead can access all the packages by creating the parent package. Any package that calls other packages (child packages) called to as parent package.
Configuring Execute Package Task in SSIS
Double click on the SSIS Execute Package task will open the Execute Package task Editor to configure it.
Within the General page, You can configure the Name and Description of the Execute Package Task.
- Name: Please provide the Unique name for the Execute Package task.
- Description: Please explain what this SSIS task is doing?
Reference Type: The Execute Package task in SSIS allows us to call packages present in the Same Project, File system, and SQL Server.
- If you select the ReferenceType property to Project Reference, then the Execute Package task allows us to choose the child package present in the same project that contains the parent package. Please refer SSIS Execute Package Task Project Reference article to understand how to call packages inside the same project.
- If you select the ReferenceType property to External Reference, the Execute Package task provides two options to choose: File System and SQL Server
- File System: If we selected the File System, the Execute Package task allows us to choose the child package in the file system. To access the files, it uses the File connection manager. Please refer Execute Packages in File System using SSIS Execute Package Task for calling packages in File System
- SQL Server: If we select the SQL Server, the Execute Package task will allow us to choose the child package stored within the MSDB Database inside our SQL Server. The Execute Package task in SSIS uses the OLE DB Connection Manager to access the packages in the SQL Server. Please refer to Execute Packages in SQL Server using SSIS Execute Package Task for calling packages in SQL Server.
Password: While choosing the child package, If the package protected by a password (Which is common in real-time), then please provide the password for that child package.
ExecuteOutOfProcess: Specify whether you want your child package to run in the same process (with the parent package) or the separate process. If you set this option to False, both parent package and child package will run in the same process. If you set this option to True, both parent package and child package will run in a different process.