Modularize SSIS Package

By : Kasim Wirama, MCDBA, MVP SQL Server


Sometimes, your package is too big and some of the tasks are reusable. You can break your big package down into smaller packages so that you can reuse your smaller package in other SSIS projects. SSIS supports this requirement by provide Execute Package task. Let’s examine more detail what configuration that the task offers.

The important configuration is located on Package section on list box of Execute Package Task Editor window, some properties are important to get familiarize with.

First is Location properties, you need to know where your package is save to, in SQL Server or in File System. When it is in SQL Server, the package is stored into msdb database. If it is in File System, you need to specify where it is located in file system.

Specify password property, when the called package is in encrypted form for security purpose.

Other property is ExecuteOutOfProcess. By default the value is false. When it is true, the referenced package will be executed in separate process and memory space.

How about setting up input parameters on child package. In DTS, you need the re-supply them to the child packages, but in SSIS, it is not necessary, because the child package will read input parameters through configuration of main package.

It is good practice if you break your large package into smaller ones, there is issue of memory (message : OutOfMemory exception) when you develop your SSIS package too big (let’s more than 10 MB). So to address this issue, the workaround is break the big SSIS package into smaller ones, and get arranged by main SSIS package, the Execute Package Task comes into play for this scenario.