Executing child package in SSIS package store from script task, with a twist

Question

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

I've 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;
    }

Problem is, my packages are stored in the SSIS package store of my SQL 2008 R2 server and I can't figure out how to reference them; every code sample I've seen is for a physical location. This is on an HA cluster so having a physical location for the package will be difficult to maintain.

So I either need to (a) figure out how to change the value of ParentVariable every time an Execute Package task is kicked off for this child package or (b) figure out how to reference the proper package inside the SSIS package store, at which point I can safely pass the proper value. Anyone have any ideas?

Another related question by me, with an answer - stackoverflow.com/questions/20671920/… — Steam

billinkc billinkc · Accepted Answer · 2013-04-05T11:51:08

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

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

The documentation on Application and Package usually have examples in the methods I've needed to use.

Executing child package in SSIS package store from script task, with a twist

1 Answers