以编程方式加载 SSIS 包配置 [英] Programmatically load SSIS package configurations

问题描述

我正在 SSIS 中创建一个框架，以从可配置文件夹加载文件并将它们与数据库中的逻辑作业相匹配.在此作业中，配置了包名称，并在 SSIS 中在运行时执行此包.

I am making a framework in SSIS to load files from configurable folders and match them to a logical job in the database. In this job a package name is configured and in SSIS I execute this package in runtime.

我想根据加载的作业以编程方式加载此包的包配置.SSIS SQL Server 包配置不是一个选项，因为它在运行时只为包本身加载一次值到这个包，但我想在运行时加载一个特定的包配置，该配置已与作业一起存储(作业有一个包，但是有很多包配置)...

I want to programmatically load a package configuration for this package, depending on the job loaded. SSIS SQL Server package configuration is not an option, because that loads values to this package just once in runtime for the package itself, but I want to load a specific package configuration in runtime that has been stored with the job (job has one package, but has many package configurations)....

示意图:folderA -> 文件 A.1 -> 作业 A -> 为作业 A 加载包配置 -> 在作业 A 中执行包.

Schematically: folderA -> file A.1 -> job A -> load package configuration for job A -> execute package in job A.

这可能吗?

推荐答案

我现在找到了解决方案.只能通过使用脚本任务使用 SSIS 对象模型在运行时基于 SQL Server 应用程序类创建包，您可以在其中按文件名加载包.从文件加载包后，我可以通过xml或SQL Server从文件中读取配置，并在运行时将其添加到子包配置列表中.

I found the solution now. It is only possible by using a script task that uses the SSIS object model to create a package in runtime based on the SQL Server Application class where you can load the package by filename. After loading the package from file, I can read the configuration from file by xml or by SQL Server and add it in runtime to the child package configuration list.

两个重要的注意事项:

1) 父变量不会自动传递给子包.仅当使用执行包任务时，父变量才会自动传递给子变量.为了让它工作，我在运行时搜索变量并在其中写入值，因为我知道我想要传递给每个子包的确切变量.

1) Parent variables are not passed to child package automatically. Only when an execute package task is used the parent variables are passed to the child automatically. To get this working I search the variables in runtime and write the values in it, because I know the exact variables I want to pass to each child package.

2) 当使用 SQL Server 作为子包的包配置时，还必须在运行时创建连接管理器并将其添加到包的连接管理器集合中.将包配置添加到子包时，请确保该连接管理器的名称是连接字符串的一部分.

2) When using SQL Server as a package configuration for a child package, you must also create a connection manager in runtime and add it to the connection manager collection of the package. when adding the package configuration to the child package, be sure that the name of that connection manager is part of the connection string.

这是证明它有效的代码:

Here is the code to prove it works:

//load the information of the job into these variables. Package is the File system deployed package on a share. Package configuration can be the package configuration in an xml file on a share, or a connection string when using SQL Server (this one is used here).
            string package = this.Dts.Variables["Package"].Value.ToString();
            string packageConfiguration = this.Dts.Variables["PackageConfiguration"].Value.ToString();


            //create a package from package factory, by file.
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            Package packageToRun = app.LoadPackage(package, null);

            //------------------------------------------ CHILD PACKAGE VARIABLES PASSING
            packageToRun.EnableConfigurations = true;

            //add one extra package configuration for child package specific configuration
            Configuration config = packageToRun.Configurations.Add();
            config.Name = "MyConfig";
            config.ConfigurationType = DTSConfigurationType.SqlServer;
            config.ConfigurationString = packageConfiguration;

            //use the name 'MyConnectionManager' in your packageConfiguration
            ConnectionManager cm = packageToRun.Connections.Add("OleDb");
            cm.Name = "MyConnectionManager";
            //TODO: retrieve this from an environvariable to allow change in data source for DEV, QA, PROD, now temporarly fixed to this value
            cm.ConnectionString = "Data Source=.;Initial Catalog=YYYYYYYYYY;Provider=SQLNCLI10.1;Integrated Security=SSPI;";

            //For Parent-Child var passing, I used the technique to let all the parent variables being defined in the child packages. 
            //Other technique could be to allow the child package not define the parent variables, but then the child packages have to reference them from code

            //------------------------------------------  PARENT VARIABLES PASSING
            //Now check if these parent variables exist in child package and write the actual values in them
            try
            {
                Variables vars = null;
                VariableDispenser variableDispenser = packageToRun.VariableDispenser;

                if (
                    packageToRun.Variables.Contains("User::XXXXXXXXXXXX") &&
                    )
                {
                    packageToRun.VariableDispenser.LockForWrite("User::XXXXXXXXXXXX");

                    variableDispenser.GetVariables(ref vars);

                    packageToRun.Variables["User::XXXXXXXXXXXX"].Value = this.Dts.Variables["User::XXXXXXXXXXXX"].Value;

                    vars.Unlock();

                    packageToRun.Execute();

                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    this.Dts.Events.FireError(0, string.Empty, "Child package: " + package + " has no required master variables defined or unable to unlock.", string.Empty, 0);
                }
            }
            catch (Exception ex)
            {
                this.Dts.Events.FireError(0, string.Empty, ex.Message, string.Empty, 0);

                Dts.TaskResult = (int)ScriptResults.Failure;
            }

这篇关于以编程方式加载 SSIS 包配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！