none
condional data flow tasks

    Question

  • Hi All,

    I am having a requirement as follow.

    I am supposed to take data from two types (having different formats) excels present in the one single folder and by the name only i can recognize their nature.

    For one format of excel sheet i need to execute one dataflow (say Dataflow1) and for other type of excel i need to execute another dataflow (say dataflow2). I am using 2 dataflows as their functions are completely different from each other.

    I am using a 'for each loop' which will traverse all excel sheets present in a specific folder. Then i am using a 'script task' to take excel name and some other tasks as well.

    Now my main dilemma is now i have use a conditional split kind of thing to execute different dataflows on different file names. Please help me out if there is some possibility to do so, else can suggest some work around which will meet my requirement.

    Any solution/suggestion is highly appreciable.

    Thanks,


    sarat chandra sahoo

    Friday, November 23, 2012 11:20 AM

Answers

  • Sounds like you should use 2 for each loops  - one with a file mask for each type of file.  Otherwise you can use your script to set a variable which could be used in a conditional constraint.

    Look here for an example of setting up the constraints

    http://www.jasonstrate.com/2011/01/31-days-of-ssis-controlling-your-flow-in-the-control-flow-2131/


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Friday, November 23, 2012 1:35 PM
  • Hi Sarat,

    you can use a script task inside For each loop and compute for which DFT this file belongs to and with the help of SSIS variable, you can proceed to the specific DFT.

    hope it helps.

    Thanks,

    Sumit

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Saturday, November 24, 2012 1:42 PM
  • You could set the fileSpec in the property window of the for each container to a variable. You can place that for each container into another for each container. In the outer container you can set the variables to the different names on each pass. In the inner for each loop you can replace your data flow with an execute package task with a variable name. Another script package in the outer loop will set the package name variable. You can hard code these values in the scripts as you like though I would prefer to populate a SQL table with the values for package name and file name filters and use the output of a SQL task to perform the assignments. This would make the task of adding a new type of process a matter of creating the package containing the data flow and adding a row to the assignment table.

    In other words put each of your data flows into their own package.

    Use a for each loop to call those packages for each file matching a fileSpec.

    That fileSpec is a variable set in an outer loop.

    That outer loop performs an assignment operation for each of the fileSpec and package pairs.

    Voila. Your controller is decoupled from the tasks it calls. Add a package add a row and you add a new file format.


    If you're happy and you know it vote and mark.

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Saturday, November 24, 2012 3:07 PM

All replies

  • Sounds like you should use 2 for each loops  - one with a file mask for each type of file.  Otherwise you can use your script to set a variable which could be used in a conditional constraint.

    Look here for an example of setting up the constraints

    http://www.jasonstrate.com/2011/01/31-days-of-ssis-controlling-your-flow-in-the-control-flow-2131/


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Friday, November 23, 2012 1:35 PM
  • Hi Sarat,

    you can use a script task inside For each loop and compute for which DFT this file belongs to and with the help of SSIS variable, you can proceed to the specific DFT.

    hope it helps.

    Thanks,

    Sumit

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Saturday, November 24, 2012 1:42 PM
  • You could set the fileSpec in the property window of the for each container to a variable. You can place that for each container into another for each container. In the outer container you can set the variables to the different names on each pass. In the inner for each loop you can replace your data flow with an execute package task with a variable name. Another script package in the outer loop will set the package name variable. You can hard code these values in the scripts as you like though I would prefer to populate a SQL table with the values for package name and file name filters and use the output of a SQL task to perform the assignments. This would make the task of adding a new type of process a matter of creating the package containing the data flow and adding a row to the assignment table.

    In other words put each of your data flows into their own package.

    Use a for each loop to call those packages for each file matching a fileSpec.

    That fileSpec is a variable set in an outer loop.

    That outer loop performs an assignment operation for each of the fileSpec and package pairs.

    Voila. Your controller is decoupled from the tasks it calls. Add a package add a row and you add a new file format.


    If you're happy and you know it vote and mark.

    • Marked as answer by sarat sahoo Monday, November 26, 2012 5:10 AM
    Saturday, November 24, 2012 3:07 PM
  • Thanks for your useful tips...

    I had solved this by using a variable by script task then by using an expression i am calling different DFTs...


    sarat chandra sahoo

    Monday, November 26, 2012 5:12 AM