CODEWITHSUNDEEP
ssis
Devora
Devora

Reputation: 357

SSIS Foreach loop container dynamic file name and path, exclude file with more than a certain underscore

I have a Foreach File Enumerator that will read pdf files name from a folder and place the filename into database. However, i wan it to exclude reading filename that has more less than 3 underscore.

AAA_BBB_000004554_060420161906_S1234567H_M.pdf
AAA_BBB_000003345_060420161906_S9876543H_S.pdf
AAA_BBB_000008546_060420161906_S1234123H_V.pdf
AAA_BBB_201604.pdf  
etc

AAA_BBB_201604.pdf should be excluded in the loop as the filename only has 2 underscore.

How can i archive that? i did some search and it seems like using expression is the key, but i had no idea how to do it. Kindly help thank you.

Upvotes: 1

Views: 6211

Answers (1)

MnM
MnM

Reputation: 306

This can be done using TOKENCOUNT function in an Expression.

Create 2 variables

  1. FileName of String type
  2. TokenCount of Int32 type

Foreach Loop Container

  1. Use Foreach Loop Container and set the Collection - Foreach File Enumerator

  2. Specify the folder location where your .pdf files exists

  3. set ".pdf* under Files: Select the radio button Retrieve File Name - Name only

enter image description here

  1. Map the File Name retrieved

enter image description here

  1. Next, put an Expression task inside the Foreach Loop Container and using the following expression

  2. Next, drop an Execute SQL Task and connect it from Expression task

    @[User::TokenCount] = TOKENCOUNT( @[User::FileName] ,"_")

    This uses the TOKENCOUNT function - Returns the number of tokens in a string (FileName in your case) that contains tokens separated by the specified delimiters ('_' in your case)

    Assign the token count to an int variable - @[User::TokenCount]

  3. In the Precedence constraint Editor, provide the following Constraint Options

enter image description here

  1. Configure the Execute SQL Task

enter image description here

  1. Finally, it should like this

enter image description here

  1. I put script task between Expression task and Execute SQL task for debugging purpose, if you want you my use this

enter image description here

  1. Running the package - let's say you want to load these file names from this folder

    enter image description here

  2. Since, we gave the condition in the expression (Token count > 3), after running the package, these file names will be loading in the database

enter image description here

Hope this helps.

Upvotes: 5

Related Questions