SSIS (SQL Server Integration Services) : Execute package and pass variable to package from Script Task

Sample Scenario:
SSIS Project consists of 2 Packages.
Package 1 = Startup.dtsx
Package 2 = Main.dtsx

Startup.dtsx = Load encrypted connection string, decrypt accordingly and pass decrypted connection string to Main.dtsx package.
Main.dtsx = Execute from Startup.dtsx and Main.dtsx will use decrypted connection string to proceed.

Startup.dtsx Package

Double Click Script Task and press Edit Script…

Don’t forget to pass the read only variable and read write variable if you need to use it in your script.

Script Task – Load XML Configuration & Decrypt encrypted Connection Strings
public void Main()
{
    Crypto crypto = new Crypto();
    crypto.InitializeEngine(Crypto.AlgorithmType.TripleDES);
    string encryptCode = "EncCde";
    // TODO: Add your code here
    Dts.TaskResult = (int)ScriptResults.Success;
    if (Convert.ToBoolean(Dts.Variables["IsEncrypted"].Value))
    {
        Dts.Variables["ActualSourceConnectionString"].Value = crypto.Decrypt(Dts.Variables["SourceConnectionString"].Value.ToString(), encryptCode);
        Dts.Variables["ActualDestConnectionString"].Value = crypto.Decrypt(Dts.Variables["DestConnectionString"].Value.ToString(), encryptCode);
    }
    else
    {
        Dts.Variables["ActualSourceConnectionString"].Value = Dts.Variables["SourceConnectionString"].Value.ToString();
        Dts.Variables["ActualDestConnectionString"].Value = Dts.Variables["DestConnectionString"].Value.ToString();
    }

}

Script Task – Run Main Package and received decrypted connection string then proceed accordingly
public void Main()
{
    // TODO: Add your code here
    string pkgLocation;
    Package pkg;
    Microsoft.SqlServer.Dts.Runtime.Application app;
    DTSExecResult pkgResults;
    pkgLocation =
    @"D:\SampleSSIS\SampleSSIS\bin\Main.dtsx";
    app = new Microsoft.SqlServer.Dts.Runtime.Application();
    pkg = app.LoadPackage(pkgLocation, null);
    MessageBox.Show(Dts.Variables["ActualDestConnectionString"].Value.ToString());
    pkg.Variables["ActualDestConnectionString"].Value = Dts.Variables["ActualDestConnectionString"].Value.ToString();
    pkg.Variables["ActualSourceConnectionString"].Value = Dts.Variables["ActualSourceConnectionString"].Value.ToString();
    MessageBox.Show(pkg.Variables["ActualSourceConnectionString"].Value.ToString());
    pkgResults = pkg.Execute();

    Dts.TaskResult = (int)ScriptResults.Success;

}

in Main.dtsx, Set connection string properties by using expression. Assign with variable ActualDestConnectionString for DestinationDS and ActualSourceConnectionString for SourceDS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s