以编程方式加载 SSIS 包配置
我正在 SSIS 中制作一个框架,用于从可配置文件夹加载文件并将它们与数据库中的逻辑作业相匹配。在此作业中配置了包名称,并在 SSIS 中我在运行时执行此包。
我想根据加载的作业以编程方式加载此包的包配置。 SSIS SQL Server 包配置不是一种选项,因为它只在运行时为包本身加载一次值,但我想在运行时加载已与作业一起存储的特定包配置(作业有一个包,但有许多封装配置)....
示意性地: 文件夹A ->文件A.1->工作A->加载作业 A 的包配置 ->在作业 A 中执行包。
这可能吗?
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.
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)....
Schematically:
folderA -> file A.1 -> job A -> load package configuration for job A -> execute package in job A.
Is that possible?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我们使用父包和子包执行类似的操作,为具有不同配置值的不同客户端运行标准包。父包使用环境变量和我们的配置表来提取该特定进程的配置值。子表配置为接受在执行包任务中从父包发送的配置变量。如果需要的话,这还允许我们为父包中的特定客户端执行一些自定义步骤(这里大约是 100% 的时间)。因此,如果您从一个客户端获取一个文件,但他们无法以标准子导入使用的格式提供,您可以执行转换步骤以使文件为标准导入做好准备,然后运行标准。或者,您可以在标准包之后添加步骤,以向客户发送电子邮件,但需要在数据中修复例外情况,例如,如果只有一个客户需要这样做。
您可以在父包中为要发送的每条配置信息创建变量,通常发送到子包中连接的其他变量或连接字符串。然后,您放入一个使用与子包的连接的 Excute 包任务。
然后,在子包中,转到 SSIS 菜单并选择包配置和添加。然后对于配置类型,您选择父包变量。您将为要发送到子包的每个配置项创建一个父包变量。我们发送的内容包括 client_id、客户端特定数据库的连接字符串、可能因客户端而异的变量等。
我们还将所有配置存储在元数据库的表中,在该表中存储有关导入的信息。因此,我们将父包设置为使用环境变量来告诉它连接到哪个数据库来获取配置信息。然后第二个配置是存储配置信息的 SSISConfiguration 表。我们通过在测试包之前运行的插入脚本按服务器填充该信息(通常会因服务器而异,dev、qa 和 prod 的连接字符串不同)。
有关更多详细信息,请参阅联机丛书中的执行包任务,它将向您展示如何设置包以传递变量。
We do something simliar using parent and child packages to run a standard package for differnt clients with differnt configuration values. The parent packge uses and enviroment variable and our configuration table to pull the configuration values for that particular process. The child table is configured to accept variables for the configuration which are sent from the parent package inthe execute package task. This also allows us to do some custom steps for a particular client in the parent package if need be (which is about 100% of the time here). So of you get one file form one client that they just cannot provide in the format the standard child import uses you can do transformation steps to get teh file ready for the standard import and then run the standard. Or you can add steps after the standrd package to send an email to the client with exceptions that they need to fix in their data for instance if only one client requires that.
You create Variables in the parent package for each piece of configuration information you want to send, typically to other variables or connection strings for the conmnections in the child package. You then put in an Excute package task that uses a connection to the child package.
In the child package you then go to the SSIS menu and choose package configurations and Add. Then for the type of configuration, you choose Parent Package variable. You will create one Parent package variable for each configuration item you want to send to the Child package. Things we send are things like the client_id, the connection strings to a client specific database, variables for things that might vary by client, etc.
We also store all our configurations in a table in a meta database where we store information about imports. So we set up our parent pacakge to use an environment variable to tell it which database to connect to to get the configuration information Then the second confiuration is to the SSISConfiguration table that stores the configuration information. We populate that information by server (it will vary by server generally, connection strings are different for dev, qa and prod) through an insert script that we run before testing the package.
For further detail, look in Books Online for execute package task and it wil show you how to set up the packages to pass variables.
我现在找到了解决方案。只能通过使用 SSIS 对象模型的脚本任务在运行时基于 SQL Server 应用程序类创建包,您可以在其中按文件名加载包。从文件加载包后,我可以通过 xml 或 SQL Server 从文件中读取配置,并在运行时将其添加到子包配置列表中。
两个重要注意事项:
1) 父变量不会自动传递给子包。
仅当使用执行包任务时,父变量才会自动传递给子变量。为了使其正常工作,我在运行时搜索变量并在其中写入值,因为我知道要传递给每个子包的确切变量。
2)当使用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.
Two important notes:
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) 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: