Steps to get files as per the file name

Subhomoy Chakraborty 106

Hi Team,

I have a source location where I have files as per the screenshot.

Now I need to prepare SSIS package import export to fetch this file based on the file name.

The logic should be for package 1 it should check date(20220425) and file name (_Cof_Test) and proceed for import.
For package 2 it should check date(20220425) and file name (_Dop_Test) and proceed for import.

I have made 2 packages but unable to fetch the file name dynamically. As of now I hard coded it. Please suggest how to pick the files for each packages.

Please refer the screenshot of the packages.

12 answers

Subhomoy Chakraborty 106 Reputation points

2022-05-03T13:32:48.087+00:00

In addition to this I also created a script task within the for each loop as below but it still did not pick up the right file.
Please sign in to rate this answer.
Michael Taylor 60,326 Reputation points

2022-05-03T14:16:48.173+00:00

You need to ensure you're using the Multiple File Connection Manager to get the list of files, then enumerate through the results in foreach but note that Foreach can already do file enumeration so you don't need the MFCM at all. In either case ensure that you added FilePath to the list of ReadonlyVariables for the script and then they would show up. I tested this locally and it works fine.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Subhomoy Chakraborty 106 Reputation points

2022-05-03T18:37:05.86+00:00

Hi

When the file is different and stored in differnent tables having different column name than how to use MFCM? Please suggest.
Please sign in to rate this answer.
Michael Taylor 60,326 Reputation points

2022-05-03T19:18:00.943+00:00

I think you're making this more complicated than it needs to be so let's reset your package (you only have 1 here by the way).

I'm assuming here that your 2 containers are for the _cof-test and _dop_test files. I'm also assuming that you will have only one of each file. If you want to set this name at runtime then add a package-level variable to store the name.

Add a sequence container as the root (optional)

Add 2 foreach containers inside the root.

The first foreach is for COF file(s), the second for DOP files.

Set the collection of the first to use files and match your _cof_test file pattern. For the second do the same just with a different file pattern.

In the variable mapping assign each filename to a variable scoped to the foreach container as I showed a screenshot of in the earlier post.

Within each foreach loop add the tasks needed to process that file type. If you need a file connection then you would do it inside here.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Subhomoy Chakraborty 106 Reputation points

2022-05-03T19:48:16.41+00:00

Hi,

For point 4 inside for each loop collection I am doing this

But inside variable mappings add variable please let me know what will be the value. Package Name : Cof_Package_SSIS.dtsx
Please sign in to rate this answer.
Michael Taylor 60,326 Reputation points

2022-05-03T20:40:28.953+00:00

The variable mapping is where you specify the variable to store the filename that the foreach loop is currently processing. In your screenshot set the name to something like MyFileName. Then later in that same container you can reference the current file of the foreach loop using the User::MyFileName variable. The filename is only needed within the foreach loop so set the scope to the foreach container. If you don't do that then your other foreach loop (which you'll do the same thing for) will collide with it.

Refer to my screenshot where I showed what mine looked like. Each time through the loop then the variable will be set to the next filename. Since it is scoped to the foreach loop and each loop is a separate container the 2 foreach loops won't be writing to the same variable.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Subhomoy Chakraborty 106 Reputation points

2022-05-03T19:48:57.34+00:00

It would be great if we can have any screenshare session.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Subhomoy Chakraborty 106 Reputation points

2022-05-03T21:23:02.19+00:00

Hi,

Thanks for your reply.

Once I am setting the variable within for each loop it is giving error as "The variable might not exist in the correct scope."
Please sign in to rate this answer.
Michael Taylor 60,326 Reputation points

2022-05-03T21:51:26.447+00:00

Please post a screenshot of the error along with your variable declaration.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Steps to get files as per the file name

12 answers

Your answer