通过从C＃SSIS参数 [英] Pass SSIS parameters from C#

问题描述

我有有一些Project.params设置SSIS包。

I have an SSIS package which has some Project.params set.

我如何通过C＃传递这些参数SSIS包价值？

How do I pass values for those parameters to the SSIS package via C#?

我想以下内容：

const string pkgLocation = @"export.dtsx";
var app = new Application();
var pkg = app.LoadPackage(pkgLocation, null);

var results = pkg.Execute();

这返回失败，包含错误收集变量$项目:: CONNSTRING不是

This returns a failure, with Errors collection containing "The variable "$Project::connString" was not found in the Variables collection. The variable might not exist in the correct scope."

所以我尝试添加

var param = pkg.Parameters.Add("connString", TypeCode.String);
param.Value = "test"; 
var results = pkg.Execute();

但是，这将引发DtsGenericException。

But this throws a DtsGenericException.

推荐答案

我想我知道了。关键是要反序列化ispac文件（VS建立这一点，但你的可以的通过的 MSBuild的）成项目对象。 Project对象允许我们设置项目级别的参数（以及获得项目级别的连接管理器）。

I think I got it. The trick is to deserialize your ispac file (VS builds this but you can do it via msbuild) into a Project object. The Project object allows us to set project level parameters (as well as access project level connection managers).

从那里，我们需要获得特定软件包的参考，我们想要的，但它将是一个 PackageItem 。 PackageItems不能运行，但他们有一个包属性，我们将用它来实例化套餐类确实的有一个执行法

From there we will need to get a reference to the specific package we want but it will be a PackageItem. PackageItems can't run but they do have a Package property we will use to instantiate the Package class which does have an Execute method

   public static void final()
    {
        string isPacPath = @"C:\sandbox\so_31812951\so_31812951\bin\Development\so_31812951.ispac";
        string packageName = "Package.dtsx";
        Application app = new Application();
        Package pkg = null;

        // https://msdn.microsoft.com/en-us/library/ff930196(v=sql.110).aspx
        Project proj = null;
        PackageItem pi = null;
        DTSExecResult results;

        ///////////////////////////////////////////////////////////////////
        // Run an SSIS package that has a Project parameter
        ///////////////////////////////////////////////////////////////////
        proj = Project.OpenProject(isPacPath);

        // Yes, I can see the packages in there
        foreach (var item in proj.PackageItems)
        {
            Console.WriteLine(string.Format("Project {0} contains package {1}", proj.Name, item.StreamName));
        }

        //Also able to see the project level parameters
        foreach (Parameter item in proj.Parameters)
        {
            Console.WriteLine(string.Format("Project {0} contains parameter {1} type of {2} current value {3}", proj.Name, item.Name, item.DataType, item.Value));
        }

        // assign a value to my project level parameter
        proj.Parameters["ProjectParameter"].Value = 10;

        // Get the package from the project collection
        pi = proj.PackageItems[packageName];

        // Convert the package into a package object
        pkg = pi.Package;

        // This is how we specify a package parameter value
        pkg.Parameters["PackageParam"].Value = 777;

        results = pkg.Execute();

        Console.WriteLine(results);
    }

这是假设你有一个名为SSIS项目 so_31812951 它编译成位于C的ispac：\sandbox\so_31812951\so_31812951\bin\Development\so_31812951.ispac这个项目有一个单一的包叫做Package.dtsx。将会有一个项目级参数的Int32，名为 ProjectParameter 以及一个包级别参数的Int32，名为 PackageParam

This assumes you have an SSIS project called so_31812951 which compiled to an ispac located at C:\sandbox\so_31812951\so_31812951\bin\Development\so_31812951.ispac This project has a single package called Package.dtsx. There will be a Project level parameter, Int32, named ProjectParameter as well as a package level parameter, Int32, named PackageParam

这篇关于通过从C＃SSIS参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！