Passing parameters & variables into SSIS package when invoked from C# .Net

SSIS has 2 confusing concepts around dealing with variable. There were Variables earlier and now we can see Parameters also.
They are not really same and there are many places in internet talking about it. Below is a simple code snippet on how both can be set if we are executing SSIS package from C# application.

Sample setup

This sample uses a simple SSIS package which does calculation of square for all the number from 0 to N

• One parameter named 'number'. This will be used as loop's higher end
• One variable names 'counter'. Used as looping variable
• For Loop Container - This loop from 1 to number parameter. Initate FindSquareAndLog script block.
• FindSquareAndLog script block - This finds the square of variable counter and logs.

There is C# code which invokes this .dtsx file

FindSquareAndLog script block

This is part of .dtsx package. Editing the C# code in SSIS Script task in Visual Studio is simple as clicking on the 'Edit Script' button. As seen this is simple C# script which writes to Dts.Log

C# to pass parameter and variable values

This runs from a .Net application which invoke .dtsx package. Very straight forward. Every execution will create an ExecutionInstanceGUID internally. It is captured here. Very much useful if an operation involves series of SSIS package invocation and logs needs to be correlated.

Experiments

Lets try adding new variables and parameters before invoking from the .Net app.

---

private static void AddParametersIntoPackage(Package pkg)
{
    pkg.Parameters["number"].Value = 3;
    pkg.Variables["counter"].Value = 10;


    pkg.Parameters.Add("dfd", TypeCode.Boolean);
    pkg.Variables.Add("dfd", false, "", 3);
    pkg.Parameters.Add("df", TypeCode.Decimal);
}

---

Can see it just runs though there is no variables or parameters with these names defined in the dtsx pacakge. Try more so that we will end up in a conclusion that dtsx pacakges can be simply generated from our C# code

http://www.sqlchick.com/entries/2013/9/15/getting-started-with-parameters-variables-configurations-in.html
Happy coding