Hello, I was wondering if it is possible to retain the same connection across Parent/Child packages using SSIS 2012. I know that I can simply pass the connection string to the child packages, but the children will still be creating their own connections. I would like to be able to tell the project connection to retain the connection. Seems like it should be possible since the child packages are running in the same processes, I just can't seem to figure out how to do it. Thanks, Chris

Hello Chris, That is not possible. Passing the connection string is just sharing the connection particulars. Now please tell why would you need to share the connection. Thinking perhaps we can offer you an alternative or solve the issue in another way. Arthur My Blog

I built an Incremental Load project that pulls data from about 50 tables from several source systems and pushes them into a central data store. Here are the details of the different iterations of this project and why I made specific changes along the way. Iteration 1: The way that I initially structured the project was by putting all of the tables into sequence containers in a single package. The issue with this is that the package would take several minutes to load and would often crash during validation. This was because we had 5 source systems it was validating against for 50 tables, meaning we had over 250 sources and 50 destinations steps. Iteration 2: To solve these issues, we removed all of the duplicate sources and made the source database connection string a project variable and we scheduled several jobs on the SQL Server configuring the connection string for each source database. We really liked the way this worked since we could reuse the package with ease if we built a new source server (we would simply need to configure a new SQL job and modify the parameters). The issue that we started running into with this approach is that validation was still taking a long time (since there are still 50 sources and 50 destinations) and also we could only have a single developer work on the package at any given point in time. Team collaboration simply was not possible as merging a package using TFS is nearly impossible. Iteration 3: To solve these issues, we read that it is common practice to have each table in it's own package and then have a master package that executes the child packages. This would allow multiple developers to work on the process at the same time and would bring validation down to mere seconds since each package would have 1 to 2 sources and destinations. This also gave us the flexibility to create new processes in the same project that without affecting the production master package, easing development. The issue that I am now running into is that instead of having a single connection to each SQL Server, we are opening 50+ connections to each source server and 250+ connections to the destination server, which is causing the process to fail quite often (Our SQL Job's a failing with an 'Unexpected Termination' state). I have looked through the SSISDB logs and there is no valid reason for the 'Unexpected Termination', so my only guess is it has to do with the massive amounts of connections being initiated all at once. Thanks, Chris