Running SSIS package using Script component

We can run the SSIS package in many ways using SSIS package. If you have lot of child package to be called in your master package, We can use the “Execute Package Task”. But if you have to pass some dynamic variables to be passed from the parent package to the child package. We can use the “Script Component” to run the SSIS package. We have Dts.Variables to assign the variables dynamically. Following are Steps.

1. Create SSIS package
2. Drag “Script Component” to control flow.
3. Double click on “Script Component”. You can see “ReadOnlyVariables” and “ReadWriteVariables”. To test, we are selecting two variables.
   1. ChildPackageParam
   2. ChildPkgLocation
4. If you have any parameters to pass, then you can use PackageParam. Otherwise, we don’t need to map package parameter.

Following is the code used to run the SSIS package using the script task.

public void Main()
{
    string pkgLocation = Dts.Variables["ChildPkgLocation"].Value.ToString();

    try
    {
        Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgLocation, null);

        pkg.Variables["Varaible1"].Value = Dts.Variables["ChildPackageParam"].Value.ToString();

        pkgResults = pkg.Execute();

        bool isFieragain = false;
        
        Dts.Events.FireInformation(0, "Calling package", String.Format("Package Execution successfull and package path is:[{0}]", pkgLocation), string.Empty, 0, ref isFieragain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "Calling package", String.Format("Error in calling your master package:[{0}]", ex.Message), string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Hope this helps.

Thanks,

Gowdhaman