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

Question

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.

If the filenames are consistent you could use a suitable filename filter, i.e. ???_???_?????????_*.PDF would include your example filenames and exclude your example. I suggest you just run a DOS ROBOCOPY copy command beforehand that identifies and moves suitable files, as long as they can be identified by a suitable pattern: msdn.microsoft.com/en-us/library/ms690414(v=vs.85).aspx — Nick.Mc
– Nick.Mc, Commented Jun 5, 2016 at 0:33

MnM · Accepted Answer · 2016-06-04 20:05:27Z

This can be done using TOKENCOUNT function in an Expression.

Create 2 variables

FileName of String type
TokenCount of Int32 type

Foreach Loop Container

Use Foreach Loop Container and set the Collection - Foreach File Enumerator
Specify the folder location where your .pdf files exists
set ".pdf* under Files: Select the radio button Retrieve File Name - Name only

Map the File Name retrieved

Next, put an Expression task inside the Foreach Loop Container and using the following expression
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]
In the Precedence constraint Editor, provide the following Constraint Options

Configure the Execute SQL Task

Finally, it should like this

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

Running the package - let's say you want to load these file names from this folder
Since, we gave the condition in the expression (Token count > 3), after running the package, these file names will be loading in the database

Hope this helps.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related