How to use if..else use in a data stream based on user variable values ​​in SSIS

Question

I have a fairly simple SSIS package with several Data Flow tasks, each with data streams for multiple tables, as shown below:

I want to be able to execute each of these data streams based on user-defined variable values ​​that I control with using the Script Task in the control stream. Something like if the value of the variable (say, BESTELLDRUCK) is true , then I want to execute the data flow for this table (source-destination assignment tasks), otherwise I want to skip this table and go to another table (for example, AKT_FEHLER) in that same data flow task.

How can i do this? Thanks in advance.

Answer 1

You cannot disable or enable conversions in the Data Flow Task . However, you can enable or disable data flow tasks in the Control Flow tab.

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

If possible, move source → destination transformations to separate data flow tasks. Something like as shown below.

Suppose you create variables for each stream to enable or disable the data stream task based on some condition. In this example, I have hard coded values.

To dynamically enable or disable data flow tasks based on a variable. Click "Data Flow Task" and press F4 to view Properties . In the properties, click the Ellipsis button next to the Expressions property. You will see the Expression Property editor.

Select the Disable Property and use the Ellipsis button to enter the expression !@ [User::Enable_BESTELLDRUCK] . Pay attention to the exclamation mark, because the variable is declared Enable, but only Disable is available to you to do the opposite.

Repeat this process for other data flow tasks with the appropriate variables. Run the package and you will notice that the second task of the data flow did not complete, because the variable Enable_AKT_FEHLER was set to False .

Hope this helps.

Reference:

To load multiple tables that have the same schema in a ForEach Loop container, see the SO answer below. It transfers data from MS Access to SQL Server. Hope this should give you an idea.

How to programmatically retrieve a list of MS Access tables in an SSIS package?

Answer 2

I assume that agent 007 has enough pointers to solve the problem. I would like to add some general comments.

• Turning tasks on / off dynamically is not good practice. The best way to disable a task is to use an expression within the priority constraint. One such link: http://www.sqlis.com/sqlis/post/Disabling-tasks-Through-Expressions.aspx

• As suggested, convert each STD (Source-Transform-Destination) into its own DFT. Even better is the parent-child pattern. This will help in testing future additions of more DFT.