Creating and Running the Child Packages
New: 5 December 2005
When you implement load balancing using Integration Services, child packages are installed on other servers to take advantage of the available CPU or server time. To create and run the child packages requires the following steps:
- Designing the child packages.
- Moving the packages to the remote server.
- Creating a SQL Server Agent Job on the remote server that contains a step that runs the child package.
- Testing and debugging the SQL Server Agent Job and child packages.
When you design the child packages, the packages have no limitations in their design, and you can put in any functionality you desire. However, if the package accesses data, you must ensure that the server that runs the package has access to the data.
After the child packages have been designed, the next step is to deploy them on the remote servers.
Moving the Child Package to the Remote Instance
There are multiple ways to move packages to other servers. The two suggested methods are:
- Exporting packages by using SQL Server Management Studio. For more information, see How to: Export a Package Using Integration Services Service.
- Deploying packages by building a deployment utility for the project that contains the packages you want to deploy, and then running the Package Installation Wizard to install the packages to the file system or to an instance of SQL Server 2005. For more information, see Deploying Integration Services Packages.
You must repeat the deployment to each remote server you want to use.
Creating the SQL Server Agent Jobs
After the child packages have been deployed to the various servers, create a SQL Server Agent job on each server that contains a child package. The SQL Server Agent job contains a step that runs the child package when the job agent is called. The SQL Server Agent jobs are not scheduled jobs; they run the child packages only when they are called by the parent package. Notification of success or failure of the job back to the parent package reflects the success or failure of the SQL Server Agent job and whether it was called successfully, not the success or failure of the child package or whether it was executed.
For more information about how to create a SQL Server Job Agent for each package on each remote server, see How to: Run a Package Using a SQL Server Agent Job.
Debugging the SQL Server Agent Jobs and Child Packages
You can test the SQL Server Agent jobs and their child packages by using one of the following methods:
- Running each child package in SSIS Designer, by clicking Debug / Start Without Debugging.
- Running the individual SQL Server Agent job on the remote computer by using SQL Server Management Studio, to make sure that the package runs.
For information about how to troubleshoot packages that you run from SQL Server Agent jobs, see the Microsoft Support Knowledge Base article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.
Security on the SQL Server Agent Job
The SQL Server Agent checks subsystem access for a proxy and gives access to the proxy every time the job step runs. For more information, see Creating SQL Server Agent Proxies.
You can create a proxy in SQL Server Management Studio. For more information, see How to: Create a Proxy (SQL Server Management Studio).
See Also
Concepts
Execute SQL Server Agent Job Task