6

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

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.

Agent007
  • 2,720
  • 3
  • 20
  • 24
  • 3
    There are many ways to do this, but I think that the better choice would be to do that on the control flow, and choose what data flow box to execute depending on your variable value – Lamak Feb 05 '13 at 14:28

2 Answers2

16

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.

Control Flow

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.

Variables

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.

Property Expression Editor

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.

Execution

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?

Community
  • 1
  • 1
  • This is really nice, can I wrap this up in `ForEach` loop anyhow keeping table names as dynamic parameters coming from variables? – Agent007 Feb 06 '13 at 11:40
  • Thanks, That's what I meant, can you please give me some references on this? That would be really helpful. – Agent007 Feb 06 '13 at 12:33
  • I've just tested the above, and it does not work if you're using a loop in a container, the property is set at runtime. – seebach May 13 '14 at 13:48
1

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.

Anoop Verma
  • 1,495
  • 14
  • 19
  • Once you've split each into its own DFT, is it possible to execute a specific DFT via SQL Command? on the IDE it is easy, just right click the DFT and execute – Peter PitLock Mar 04 '16 at 09:30
  • If you want to run the DFT via a SQL command, I would suggest that you put each DFT in a child package of its own. – Anoop Verma Mar 06 '16 at 05:26