如何根据 SSIS 中的用户变量值在数据流中使用 if..else [英] How to use use if..else in Data Flow based on user variable values in SSIS

问题描述

我有一个相当简单的 SSIS 包，其中包含许多 Data Flow 任务，每个任务都有多个表的数据流，如下所示:

I have a fairly straightforward SSIS package with a number of Data Flow tasks each with data-flows for multiple tables like shown below:

我希望能够根据我在控制流中使用 Script Task 操作的一些用户定义的变量值来执行这些数据流中的每一个.比如，如果一个变量(比如 BESTELLDRUCK)值为 true，那么我想执行这个表的数据流(源转换目标任务)，否则我想跳过这个表和继续处理同一数据流任务中的另一个表(例如 AKT_FEHLER).

I want to be able to execute each of these data-flows based on some user-defined variable values that I manipulate using a Script Task in control-flow. Something like, if a variable (say BESTELLDRUCK) value is true, then I want to execute the data-flow for this table (source-conversion-destination tasks), else I want to skip this table and proceed to another table (e.g. AKT_FEHLER) in same data-flow task.

我该怎么做?提前致谢.

How can I do this? Thanks in advance.

推荐答案

您不能禁用或启用数据流任务中的转换.但是，您可以在 Control Flow 选项卡上启用或禁用数据流任务.

You cannot disable or enable transformations within the Data Flow Task. However, you can enable or disable Data Flow Tasks on the Control Flow tab.

这是在控制流"选项卡上执行此操作的一种可能方法:

Here is one possible way to do this on Control Flow tab:

如果可能，请移动源 -->到单个数据流任务的目标转换.如下图所示.

If it is possible, move the source --> destination transformations to individual data flow tasks. Something like as shown below.

假设您已经为每个流创建了变量，以根据某些条件启用或禁用数据流任务.对于这个例子，我硬编码了一些值.

Let's assume you have created variables for each flow to enable or disable the Data Flow Task based on some condition. For this example, I have hard coded some values.

根据变量动态启用或禁用数据流任务.单击数据流任务并按 F4 查看 Properties.在属性上，单击 Expressions 属性旁边的 省略号 按钮.您将看到属性表达式编辑器.

To dynamically enable or disable Data Flow Tasks based on variable. Click on a Data Flow Task and press F4 to view Properties. On the Properties, click the Ellipsis button next to the Expressions property. You will see the Property Expression Editor.

选择Property禁用并使用省略号按钮输入表达式!@[User::Enable_BESTELLDRUCK] 注意感叹号，因为变量被声明为 Enable 但只有 Disable 属性可用，你需要做相反的事情.

Select the Property Disable and use the Ellipsis button to enter the expression !@[User::Enable_BESTELLDRUCK] Notice the exclamation sign because the variable is declared to Enable but only Disable property is available to you need to do the opposite.

使用适当的变量对其他数据流任务重复该过程.运行包，您会注意到第二个数据流任务没有执行，因为变量 Enable_AKT_FEHLER 被设置为值 False.

Repeat the process for other Data Flow Tasks with appropriate variables. Run the package and you will notice that the second Data Flow Task did not execute because the variable Enable_AKT_FEHLER was set to the value False.

希望有所帮助.

要在 ForEach Loop 容器中加载多个具有相同架构的表，请查看以下 SO 答案.它将数据从 MS Access 传输到 SQL Server.希望这能给你一个想法.

To load multiple tables having same schema within ForEach Loop container, take a look at the below SO answer. It transfers data from MS Access to SQL Server. Hopefully, that should give you an idea.

如何以编程方式获取 SSIS 包中的 MS Access 表列表?

这篇关于如何根据 SSIS 中的用户变量值在数据流中使用 if..else的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！