在不同时间使用不同参数执行相同的 SSIS 包 [英] Executing the same SSIS Package with different parameters at different time

问题描述

我有一个 2011 年晚上 8 点运行的 SSIS 包.

我想在 2010 年晚上 8:30 运行相同的包.

我制作了一个 SSIS 包配置文件并接受年份"作为参数.每当我运行时，我需要打开一个文件，更改值并运行它.

是否可以动态设置时间表和设置年份值?

或者使用2个不同的配置文件是解决它的唯一方法?

谢谢大家.

解决方案

使用配置文件方法的挑战在于您需要不断修改文件.SSIS 在启动后不会重新加载配置文件，因此您可以想象晚上 8:05 和 8:35 的作业会交换配置文件，但这会在某些时候变得混乱和中断.

我会使用命令行变量来处理这种情况(是 2005 与 2008 模型的差异.我看到您在票证中指出了 2008，但对于未来的读者，如果您同时使用/SET 和配置源(xml、sql 服务器、注册表、环境变量)，则操作顺序因版本而异.

I have a SSIS package running at 8 PM in the evening for the Year 2011.

I would like to run the same package at 8:30 PM for the Year 2010.

I made a SSIS Package configuration file and accept the "Year" as a parameter. Whenever I run, I need to open a file, change the value and run it.

Is it possible to set up the schedule and set the Year value dynamically?

Or using 2 different configuration file is the only way to solve it?

Thanks all.

解决方案

The challenge with using a configuration file approach is that you would need to constantly modify the file. SSIS wouldn't reload the config file after it starts so you could conceivably have 8:05 and 8:35 PM jobs that swaps config files but that's going to get messy and break at some point.

I would handle this situation with command line variables (/set option in dtexec). If you were running the package from the command line, it'd look something like dtexec.exe /file MyPackage.dtsx Even if you're using SQL Agent, behind the scenes it's building those command line arguments.

This approach assumes you create two different jobs (vs 1 jobs scheduled 2x a day). AgentMyPackage2011 has a job step of SSIS that results in

• dtexec /file MyPackage.dtsx /Set Package.Variables[User::Year].Properties[Value];"2011"

and AgentMyPackage2012 has a job step of SSIS that results in

• dtexec /file MyPackage.dtsx /Set Package.Variables[User::Year].Properties[Value];"2012"

Through the GUI, it'd look something like

There is no GUI or selector for the property you wish to configure. However, since you've already create a .dtsConfig file for your package, open that file up and look for a section like

<Configuration ConfiguredType="Property" Path="Package.Variables[User::Year].Properties[Value]" ValueType="Int32">
<ConfiguredValue>2009</ConfiguredValue>

The file already has the path to the "thing" you are attempting to configure so punch that into your calling program and then turn off the year portion of package configuration.

Finally, a link to SSIS Configuration Precedence as there are differences in the 2005 vs 2008 model. I see you indicated 2008 in your ticket but for future readers, if you're using both /SET and a configuration source (xml, sql server, registry, environment variable) the order of operations varies between versions.

这篇关于在不同时间使用不同参数执行相同的 SSIS 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！