How to have a specific Dataflow execute based on file name.

sam nick 286 Reputation points
2022-08-26T20:53:20.463+00:00

Hello,
I have a requirement that needs to be executed in the below steps.

In a folder there are multiple txt files with names as
ABC_0101222.txt
ABC_02022022.txt
ABC_04022022.txt
DEF_03032022.txt
DEF_05012022.txt

1) Take 1 file to a temp location
2) Change filename to csv
3) If File name starts with ABC then execute Dataflow1 . If it starts with DEF then execute Dataflow2
4) Continue the process till all files are processed.

I am able to accomplish task 1 and 2 via batch script, but not sure on how to accomplish 3 and 4. Please can you advise. Currently I am doing this manually by clicking the DF manually.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,464 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2022-08-26T21:10:26.747+00:00

    Hi @sam nick ,

    Here is how you need to compose steps of the process in SSIS:

    1. 'Foreach Loop' Task, to traverse directory with files.
      Assign a fully qualified file path to a variable.
    2. 'File System' Task, Rename file operation.
    3. Use two precedence constraints to check for 'ABC' or 'DEF' pointing to a particular DFT.

    Check it out here: execute-data-flow-task-based-on-condition-in-ssis

    0 comments No comments

  2. ZoeHui-MSFT 33,386 Reputation points
    2022-08-29T05:58:49.743+00:00

    Hi @sam nick ,

    This kind of orchestration is easily achieved by using the precedence constraints: https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

    Reference: precedence-constraints

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.