使用 2 种不同的配置运行 SSIS 包 [英] Run SSIS Package with 2 Different Configurations
问题描述
我们有一个名为 ExportData 的 SSIS 作业,它接受ExportType"参数.ExportType 参数可以是schedule"或unschedule".
We have a SSIS job called ExportData and it accepts the 'ExportType' Parameter. The ExportType parameter can be 'schedule' or 'unschedule'.
我创建了名为@ExportType"的变量并创建了 SSIS 配置并在配置文件中公开了该变量并将其称为ScheduleConfig".我复制了该文件并将值更改为unschedule"并将其命名为UnscheduleConfig".
I created the Variable called '@ExportType' and created the SSIS Configuration and expose the variable in the Configuration file and called it 'ScheduleConfig'. I copied that file and change the value to 'unschedule' and called it 'UnscheduleConfig'.
我在 SQL Server 2008 中创建了 SSIS 作业,并为计划"和取消计划"设置了 2 个步骤.我为每个步骤附加了正确的配置文件.但是无论我运行哪一步,它总是执行计划".我确定并仔细检查了配置文件和步骤.
I created the SSIS Job in the SQL Server 2008 and set up 2 steps for both 'Schedule' and 'Unschedule'. I attached the correct config file for each Step. But whichever step I run, it always execute 'Schedule'. I am sure and double checked the Config files and Steps.
如何使用 2 个不同的配置文件运行 2 个不同的作业?
How can I run 2 different jobs with 2 different configuration files?
我确实尝试过使用 SetValues 方法,但它也不起作用.
I did try by using the SetValues method and it doesn't work too.
推荐答案
如果我正确理解您的要求.这是在 SSIS 2008 R2
中创建的示例包,可以满足您的需求.该示例使用单个包,该包在两个不同的 SQL 代理作业步骤下执行,并且这两个步骤使用相同配置文件的不同副本.配置文件为包内使用的变量保存不同的值.
If I understand your requirements correctly. here is a sample package created in SSIS 2008 R2
and does what you are looking for. The sample uses a single package, which is executed under two different SQL agent job steps and both the steps use a different copies of the same configuration file. The configuration files hold different values for the variable used inside the package.
- 创建了一个名为
SO_10177578
的示例 SSIS 包.在包中,创建了一个名为ExportType
的包变量,其数据类型为String
.此外,将Execute SQL Task
放在控制流选项卡上.此任务将有助于识别传递给变量ExportType
的值.
- Created a sample SSIS package named
SO_10177578
. Within the package, created a package variable namedExportType
of data typeString
. Also, placed theExecute SQL Task
on the Control Flow tab. This task will help to identify the value being passed to the variableExportType
.
- 将 OLE DB 连接添加到示例数据库,并将该连接命名为
SQLServer
.我选择为此连接管理器使用 SQL Server 身份验证.
- Added the OLE DB connection to a sample database and named the connection as
SQLServer
. I chose to use SQL Server authentication for this connection manager.
- 在 SQL 数据库中,使用以下结构创建名为
dbo.ExportData
的表.Id
列是标识列.
- Within the SQL database, created a table named
dbo.ExportData
with the following strucutre.Id
column is a identity column.
- 该表不包含任何开始的数据.该表将在执行包时填充数据.
- 在 SSIS 包上,单击
SSIS
菜单 --> 选择Package Configuration
选项.在包配置管理器对话框中,选中启用包配置复选框并单击添加按钮.创建了XML 配置文件
类型的新包配置.选择了存储文件的路径.
- On the SSIS package, clicked on the
SSIS
menu --> selectedPackage Configuration
option. On the Package Configurations Organizer dialog, selected the Enable package configurations checkbox and clicked on the Add button. Created a new package configuration of the typeXML Configuration file
. Selected a path to store the file.
- 将变量
ExportType
的Value属性和连接管理器SQLServer
的ConnectionString属性添加到配置文件.
- Added the Value property of the variable
ExportType
and the ConnectionString property of the connection managerSQLServer
to the configuration file.
- 在执行 SQL 任务上,选择 Connection 为
SQLServer
并将 SQLStatement 属性设置为INSERT INTOdbo.ExportData (PackageName, ExportTypeValue) VALUES (?, ?)
- On the Execute SQL Task, selected the Connection to be
SQLServer
and set the SQLStatement property toINSERT INTO dbo.ExportData (PackageName, ExportTypeValue) VALUES (?, ?)
- 将这两个参数配置为包上可用的适当变量.现在该包已准备好进行部署.
- 在数据库服务器的 SQL 代理节点上,创建了一个名为
Test_ExportData
的新 SQL 作业.Owner
字段信息被删除以隐藏敏感信息.
- On the database server's SQL Agent node, created a new SQL job named
Test_ExportData
.Owner
field information is removed to hide sensitive information.
- 在 SQL 作业的 Steps 页面上,创建了两个名为
Step_01
和Step_02
的步骤.
- On the SQL job's Steps page, created two steps named
Step_01
andStep_02
.
- 步骤 1 配置如下,包存储在路径
c:\temp\SO_10177578.dtsx
中.
- 复制使用包创建的包配置文件,并将变量
ExportType
的值更改为 Schedule.将配置文件命名为ScheduleConfig.dtsConfig
.屏幕截图仅显示部分配置文件以隐藏敏感的连接字符串信息.
- Copied the package configuration file created using the package and changed the value for the variable
ExportType
to Schedule. Named the configuration file asScheduleConfig.dtsConfig
. Screenshot shows only part of the configuration file to hide sensitive connection string information.
- 在作业的第 1 步中,从路径
c:\temp\Test\ScheduleConfig.dtsConfig.
引用新创建的包配置文件
- In step 1 of the job, referred the newly created package configuration file from the path
c:\temp\Test\ScheduleConfig.dtsConfig.
- 第 2 步的配置如下,它使用存储在路径
c:\temp\SO_10177578.dtsx
中的包与第 1 步使用的相同.
- Step 2 is configured as below and it uses the same package stored in the path
c:\temp\SO_10177578.dtsx
as used by step 1.
- 复制使用包创建的包配置文件,并将变量
ExportType
的值更改为 Unschedule.将配置文件命名为UnscheduleConfig.dtsConfig
.屏幕截图仅显示部分配置文件以隐藏敏感的连接字符串信息.
- Copied the package configuration file created using the package and changed the value for the variable
ExportType
to Unschedule. Named the configuration file asUnscheduleConfig.dtsConfig
. Screenshot shows only part of the configuration file to hide sensitive connection string information.
- 在作业的第 2 步中,从路径
c:\temp\Test\UnscheduleConfig.dtsConfig.
引用新创建的包配置文件
- In step 2 of the job, referred the newly created package configuration file from the path
c:\temp\Test\UnscheduleConfig.dtsConfig.
- 现在,两个步骤都已配置.执行了新的 SQL 代理作业.
- 查询表
ExportData
.您可以注意到该包执行了两次,并且 SQL 代理作业步骤中的每次执行都使用了步骤中指定的相应配置文件.
- Queried the table
ExportData
. You can notice that the package was executed twice and each execution in the SQL agent job's steps used the appropriate configuration files specified on the steps.
希望有所帮助.
这篇关于使用 2 种不同的配置运行 SSIS 包的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!