The Execute Package task in SSIS is the most useful task in SQL Server Integration Services because, it allows us to call other packages from the workflow. Following are few advantages of using Execute Package task:
- We can break down the complex packages into multiple packages. And then, using Execute Package task we can call them at any time. For example, Instead of performing 10 transformations in a single SSIS packages. We can break down the single package into 10 packages and then using Execute Package task we can call them.
- If we break down the complex packages into multiple packages, we can reuse them multiple 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 whole team where each individual can work on single package, and module lead can access all the packages by creating the parent package. Any package that calls other packages (child packages) is called to as parent package.
Configuring Execute Package Task in SSIS
Double click on the 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 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 ReferenceTypeproperty to Project Reference then Execute Package task allows us to select 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 ReferenceTypeproperty to External Reference then Execute Package task provides 2 options to choose: File System and SQL Server
- File System: If we selected the File System then, Execute Package task allow us to select the child package present 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, Execute Package task will allow us to select the child package stored within the MSDB Database inside our SQL Server. The Execute Package task in SSIS uses OLE DB Connection Manager to access the packages present in the SQL Server. Please refer Execute Packages in SQL Server using SSIS Execute Package Task for calling packages in SQL Server.
Password: While we selecting the child package. If the package is protected by 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 in the separate process. If you set this option to Flash then, both parent package and child package will run in the same process. If you set this option to True then, both parent package and child package will run in the different process.
Thank You for Visiting Our Blog