Search code examples
ssispackagessis-2012

Run a specific SSIS Data Flow Task in a Job

It is possible to run only a specific Data Flow task via Visual Studio (right click on the Data Flow and execute). E.g. you have multiple data flows- but only execute one of them.

I am now trying to implement this ability via a job - e.g. I have 1 Control Flow with 2 Data Flows (DataFlow1, DataFlow2) - and in

SQLJob1 - it will fire DataFlow1,

SQLJob2 - will fire DataFlow2 of the same SSISPackage

The aforementioned link states "You can build a special control flow logic using expressions on precendance constraints to define optional execution paths."

I dont want to create special control flow logic - or have 2 separate SSIS packages installed - what would the SQL command be in the Job to fire only DataFlow1 please?

I see I can in VS right click and disable a specific data flow - and then run the package. I tried to see the command for disabling/enabling a specific data flow, but there is no SQL Query for it - or is it possible to run SQL query to disable/enable specific data flow?

Solution

  • This is the SSIS-way to do this, and I'm sorry, but it involves "special control flow logic" because there is no other way:

    1. Add a package-level variable in your SSIS package.

    2. Add a script task to the control flow of your SSIS package. The script doesn't have to do anything. Think of it as a "starting anchor". It will be the first thing that executes in your package.

    3. Add separate precedence constraints (the little arrows that link tasks) from the script task to your two dataflows.

    4. Double-click on each precedence constraint and set them to use an expression for validation. Use the variable you created, and set one of the paths to be true if the variable is set to "DataFlowA" and the other if it is set to "DataFlowB".

    5. In the jobs, set the value of the variable to the appropriate value for the dataflow you want to have execute.

    This is the answer. I'm sorry it's not what you were hoping for but the answer to these questions:

    what would the SQL command be in the Job to fire only DataFlow1 please? is it possible to run SQL query to disable/enable specific data flow?

    Are, respectively,

    There is no such command.
    No, it is not possible to programmatically enable and disable tasks.