Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pass variables to an SSIS package from a C# application

Tags:

c#

ssis

Basically i am trying to build an application that uses SSIS to run a series of sql stuff.

Here is my code thus far:

        public JsonResult FireSSIS()
    {
        string x = string.Empty;
        try
        {
            Application app = new Application();                
            Package package = null;

            package = app.LoadPackage(@"C:\ft\Package.dtsx", null);

            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
            if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                {
                    x += string.Concat("Package Execution results: {0}", local_DtsError.Description.ToString());
                }
            }
        }

        catch (DtsException ex)
        {
           // Exception = ex.Message;
        }
        return Json(x, JsonRequestBehavior.AllowGet);
    }

Does anybody know how to pass a variable to the package itself in this way?

like image 470
some_bloody_fool Avatar asked Nov 11 '11 19:11

some_bloody_fool


2 Answers

You need to use the Package.Variables property.

        Package package = null;
        package = app.LoadPackage(@"C:\ft\Package.dtsx", null);
        package.Variables["User::varParam"].Value = "param value";
like image 171
SliverNinja - MSFT Avatar answered Sep 21 '22 08:09

SliverNinja - MSFT


Try that:

Microsoft.SqlServer.Dts.RunTime.Variables myVars = package.Variables;

myVars["MyVariable1"].Value = "value1";
myVars["MyVariable2"].Value = "value2";

Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute(null, myVars, null, null, null);
like image 32
GritKit Avatar answered Sep 20 '22 08:09

GritKit