How to use use if..else in Data Flow based on user

Question

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

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.

Answer 1

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.

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.

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.

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.

Hope that helps.

Reference:

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.

How do I programmatically get the list of MS Access tables within an SSIS package?

Answer 2

I guess there are enough pointers here for Agent 007 to resolve the issue. I would like to add a few general comments.

1. Enabling/disabling the tasks dynamically is not a good practice. A better way to disable a task is to use an expression within a precedence constraint. One such reference: http://www.sqlis.com/sqlis/post/Disabling-tasks-Through-Expressions.aspx

2. As suggested convert each STD (Source-Transform-Destination) into its own DFT. Even better use parent-child pattern. This would help in testing future additions of more DFTs.