How to retrieve filename from path and load it using two different Dataflow task in SSIS ?

+2 votes
asked Dec 10, 2015 in SSIS by user234 (980 points)

I have a folder with 125 txt files in them.
Half of the txt files start with Policy_(somenumber)
The other half starts with CA(Somenumber)

The files that start with Policy are to be loaded into a table in my dbtable called Policytb
While the files that start with CA are to be loaded into another table in my dbtable call CAtransactions

This is to be done in one loop with two DTFs called PolicyProcess and CATransaction.

1 Answer

+3 votes
answered Dec 10, 2015 by darsh (3,020 points)
selected Dec 11, 2015 by Admin
 
Best answer

For this you need to use foreach loop container to loop through all the file in folder for example test folder i have consider for this example and declare variable called loop to store path of file

enter image description here

enter image description here

Then inside foreach loop container add three DFT one empty DFT and 2 DFT for processing
respective files i.e. policy and CA
connect empty DFT to other two DFT in precendence constraint editor select expression
and use following expression to process respective files

    UPPER(SUBSTRING(RIGHT( @[User::loop],FINDSTRING(REVERSE(
   @[User::loop] ),"\\",1) ),2,6))=="POLICY"

  UPPER(SUBSTRING(RIGHT( @[User::loop],FINDSTRING(REVERSE(
   @[User::loop] ),"\\",1) ),2,2))=="CA"


enter image description here
enter image description here
enter image description here
enter image description here
enter image description here
enter image description here
enter image description here

Welcome to I Can Crack, where you can ask questions and receive answers from other members of the community.

68 questions

47 answers

3 comments

5,318 users

...