SQL SSIS 2012 - 将变量从父包传递到子包
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25123491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS 2012 - passing variables from parent to child package
提问by Tamila
I know this topic has been covered many times, but I have one caveat there that I can't seem to find an answer to .
我知道这个话题已经被多次提及，但我有一个警告，我似乎无法找到答案。
I have several packages which all have ActivityDate variable. By default, the packages need to run for yesterday's date.
我有几个包，它们都有 ActivityDate 变量。默认情况下，包需要在昨天的日期运行。
There are two possible scenarios.
有两种可能的情况。
Scenario 1 - packages are called from a master package. In this case, ActivityDate is set once, to yesterday's date, in the master package, and then passed down to child packages.
场景 1 - 从主包调用包。在这种情况下，ActivityDate 在主包中设置为昨天的日期，然后传递给子包。
Scenario 2 - packages run stand-alone. In this case, ActivityDate is set in each child package, also to yesterday's date, via expression.
场景 2 - 包独立运行。在这种情况下，ActivityDate 设置在每个子包中，也通过表达式设置为昨天的日期。
In SQL 2008 it was very easy - the variable in each child package vas set via Package Configuration for Scenario 1, but then used expression when run in Scenario 2.
在 SQL 2008 中，这很容易——每个子包中的变量通过场景 1 的包配置设置，但在场景 2 中运行时使用表达式。
I am unclear how to accomplish this in 2012. There is no more package configuration, so I need to create package parameter in the child package and then link to it from the parent. But parameters don't use expressions. So if I create ActivityDate parameter in the child package, then i can link to it from the parent (for Scenario 1), but I can't create an expression for it (for Scenario 2). And if I have only ActivityDate variable, then I can create an expression, but can't link to it from the parent.
So how do I make both scenarios work in 2012??
我不清楚如何在2012年完成这个。没有更多的包配置，所以我需要在子包中创建包参数，然后从父包链接到它。但是参数不使用表达式。因此，如果我在子包中创建 ActivityDate 参数，那么我可以从父级链接到它（对于场景 1），但我无法为其创建表达式（对于场景 2）。如果我只有 ActivityDate 变量，那么我可以创建一个表达式，但不能从父级链接到它。
那么我如何让这两个场景在 2012 年都有效呢？？
Help, please!
请帮忙！
回答by Kyle Hale
First, parameter bindings can use expressions:
首先，参数绑定可以使用表达式：
- Create a variable @User::Variable to hold the value you want to pass to the child package.
- Bind @User::Variable to your child parameter.
- 创建一个变量 @User::Variable 来保存要传递给子包的值。
- 将 @User::Variable 绑定到您的子参数。
Secondly, instead of package configurations you have environment variables which perform the same basic function, setting values of parameters dynamically at runtime. The basic setup is:
其次，您拥有执行相同基本功能、在运行时动态设置参数值的环境变量，而不是包配置。基本设置是：
- Define a project parameter and use it in your package.
- On the IS server, define an environment, set up an environment variable.
- Deploy your project and bind the environment variable to your project parameter.
- Execute package using particular environment reference.
- 定义项目参数并在您的包中使用它。
- 在IS服务器上，定义一个环境，设置一个环境变量。
- 部署您的项目并将环境变量绑定到您的项目参数。
- 使用特定环境参考执行包。
Anyway, it's not entirely clear what you're trying to accomplish - by default use the parent activity date, but allow it to be overriden using a parameter? Or if you call the child package independently of the parent, still provide it with yesterday's value?
无论如何，您要完成的任务并不完全清楚-默认情况下使用父活动日期，但允许使用参数覆盖它？或者如果你独立于父包调用子包，仍然提供昨天的值？
So, I thinkthis accomplishes what you want:
所以，我认为这可以完成你想要的：
- Create variable in Master Package User::ActivityDate. Set its value directly or using an expression.
- Create second variable in Master Package User::RunningFromMaster. Set as type Boolean and set default to True.
- Create parameter in Child Package $Parameter::MasterActivityDate bound to User::ActivityDate in Execute Package task.
- Create parameter in Child Package $Parameter::RunningFromMaster bound to User::RunningFromMaster in Execute Package task. Set as type Boolean and default to False.
- Create variable in Child Package User::ChildActivityDate.
- Set Expression property of User::ChildActivityDate to
(@[$Parameter::RunningFromMaster] ? @$Parameter::MasterActivityDate : DATEADD("d",-1, GETDATE() ) ) - Use User::ChildActivityDate in your package.
- 在主包 User::ActivityDate 中创建变量。直接或使用表达式设置其值。
- 在主包 User::RunningFromMaster 中创建第二个变量。设置为布尔类型并将默认设置为 True。
- 在执行包任务中创建绑定到 User::ActivityDate 的子包 $Parameter::MasterActivityDate 中的参数。
- 在执行包任务中创建绑定到 User::RunningFromMaster 的子包 $Parameter::RunningFromMaster 中的参数。设置为 Boolean 类型，默认为 False。
- 在子包 User::ChildActivityDate 中创建变量。
- 将 User::ChildActivityDate 的 Expression 属性设置为
(@[$Parameter::RunningFromMaster] ? @$Parameter::MasterActivityDate : DATEADD("d",-1, GETDATE() ) ) - 在您的包中使用 User::ChildActivityDate。
When you run from master, User::ChildActivityDate will just take whatever value was passed in to the MasterActivityDate parameter. When your child package is run independently, it will just use the expression to pull yesterday's date (feel free to modify the expression to suit your specific needs.)
当您从 master 运行时，User::ChildActivityDate 将只采用传递给 MasterActivityDate 参数的任何值。当您的子包独立运行时，它只会使用表达式来拉取昨天的日期（您可以随意修改表达式以满足您的特定需求。）
Also, if anyone asks, I used a Boolean because DateTime parameters always default to GETDATE() so you have to build convoluted conditionals to determine if they've been set or not.
此外，如果有人问，我使用了布尔值，因为 DateTime 参数始终默认为 GETDATE()，因此您必须构建复杂的条件以确定它们是否已设置。
