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 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 package. We can break the single package into ten packages. Then using the Execute Package task, we can call them.
- If we break down 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 among the whole team, where each individual can work on a single package, and the module lead can access all the packages by creating the parent package. Any package that calls other packages (child packages) is called 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.
- Suppose you select the ReferenceType property to Project Reference. In that case, the Execute Package task allows us to choose the child package in the same project containing the parent package. Please refer 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 from: File System and SQL Server
- File System: If we select 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 to the Execute Packages in File System for calling packages in File System
- 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 Server. Please refer to Execute Packages in SQL Server for calling packages.
Password: While choosing the child package, If the package is 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 a separate process. If you set this option to False, both the parent and child packages will run in the same process. If you set this option to True, both the parent and child packages will run in a different process.