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

问题描述

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

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

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

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

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

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.

是否可以设置时间表并动态设置Year值?

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

还是使用2个不同的配置文件是解决该问题的唯一方法?

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

谢谢.

推荐答案

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

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.

我将使用命令行变量来处理这种情况(dtexec中的/set选项).如果您是从命令行运行该程序包，则其外观类似于dtexec.exe /file MyPackage.dtsx，即使您使用的是SQL Agent，它也会在后台构建这些命令行参数.

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.

此方法假设您创建两个不同的作业(与1个作业每天计划2次相比). AgentMyPackage2011具有SSIS的工作步骤，可导致

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\"

和AgentMyPackage2012具有SSIS的工作步骤，结果是

and AgentMyPackage2012 has a job step of SSIS that results in

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

通过GUI，它看起来像

Through the GUI, it'd look something like

您要配置的属性没有GUI或选择器.但是，由于您已经为包创建了.dtsConfig文件，因此请打开该文件并查找类似

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.

最后，是指向 SSIS配置优先级的链接是2005年与2008年模型之间的差异.我看到您在票证中注明了2008，但是对于以后的读者来说，如果您同时使用/SET和配置源(xml，sql server，registry，环境变量)，则操作顺序在版本之间会有所不同.

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屋！