Running child package in SSIS package store from swirl script task

Question

I have a parent package that must execute the same child package multiple times. To make things more fun, each instance must have a different value, specific to the parent parameter passed to the child package.

I created a script task using the following script:

Microsoft.SqlServer.Dts.Runtime.Application App = new Microsoft.SqlServer.Dts.Runtime.Application(); Package pkg = new Package(); try { pkg = App.LoadPackage(@"\\server\SSIS Packages\ChildPackage.dtsx", null); pkg.Variables["ChildVariableName"].Value = Dts.Variables["AParentVariableName"].Value; pkg.Execute(); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { Dts.Events.FireError(0, "Run child pkg for parent task", ex.Message, string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; } 

The problem is that my packages are stored in the SSIS package store of my SQL 2008 R2 server, and I cannot figure out how to link them; every sample code i saw is for a physical location. This is in the HA cluster, so having a physical location for the packet will be difficult to maintain.

Therefore, I need to either (a) figure out how to change the ParentVariable value each time the Execute Package task is run for this child package, or (b) find out how to reference the corresponding package inside the SSIS package repository, after which I can safely pass the correct one value. Does anyone have any ideas?

Answer 1

Instead of the App.LoadPackage method, you should use the LoadFromSqlServer method

 app.LoadFromSqlServer("\OptionalFolderButSlashRequired\ChildPackage", "server", null, null, null); 

The documentation for the Application and Package usually has examples in the methods that I need to use.