Steps to get files as per the file name

Question

question

SubhomoyChakraborty-7706 asked Apr 27, '22 cooldadtx commented May 5, '22

Steps to get files as per the file name

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.

office-scripts-excel-dev

image.png (42.1 KiB)

image.png (84.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-04-27T14:30:54.783Z

cooldadtx answered Apr 27, '22

You normally use the File Connection Manager in your package to identify the file you want to work with. You can configure this task to use a parameter as the filename. You can then set the parameter either via a configuration file at runtime or pass it when you manually run the SSIS task. However this connection manager only works with a single file.

I find it very odd that you'd be looking for a very specific file with a datetime stamp in it. You'd have to update your code the next time you got an updated file. Should the first package pick up the *_Cof_Test file(s) and the second package pick up the *_Dop_Test files instead? If so then use the Multiple File Connection Manager instead. This allows a wildcard to get all the matching files (even if there is just one). Then you can use a Foreach container to loop through the files. This would eliminate the need for you to ask for/change parameters. However you'll want to ensure that any "processed" files are removed from the directory before you run again.

Looking at your UI screenshot it might also look like you are getting the filename from somewhere programmatically. Otherwise why would you need a foreach container in your screenshot? In this case that filename is already being stored in a variable in SSIS most likely so you would be using it instead. However without knowing more about what your screenshot is actually being fed it is hard to say.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-01T10:31:06.493Z

SubhomoyChakraborty-7706 answered May 1, '22

Hi,

Thanks for your reply.

Let me explain you the scenario. Currently I am running 2 parallel packages in a container where in first package it will dynamically pick the file _cof_test and the second one will pick _Dop_Test. But if I hard coded _cof_test and _Dop_Test into source connection manager then it will not work. So I need to know the exact syntax which should be used in flat file source connection.

Yes I am getting the filename from somewhere programmatically where the file format will be YYYYMMDD_cof_test etc.

What I need is instead of define the file name hardcoded in

I would like to pass it in a variable so tat it can pick it dynamically. Please suggest me the particular syntax for it.

In case you need any screenshare please let me know your suitable time.

image.png (24.2 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-01T19:19:34.687Z

cooldadtx answered May 1, '22

As discussed here. You can use an expression for the connection string of this manager. However you should set the DelayValidation property to true to avoid validation errors. Unfortunately I'm not sitting at a VS 2019 machine right now to show you what it would look like. Nevertheless pretty much everything in SSIS can be set via an expression.

To set up the expression define a parameter at the package level that contains the filename. Then reference that parameter in the connection string expression. It would look something like $(Package.MyFileName).

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-05-02T12:30:19.927Z

SubhomoyChakraborty-7706 answered May 2, '22 cooldadtx commented May 2, '22

Hi,

Thanks for your response. I used file connection manager and set delay validation true.

But the issue is when I am using more than one container at a time parallelly the package is failed as for each loop is picking only one file and processed it for all the packages. It doesnot distinguish the file name each time.

I have VS2019 set up in my system . So if you want I can do a screenshare and share the scenario.

What I am currently doing is within foreach loop

and in source connection manager expressions

image.png (33.3 KiB)

image.png (27.0 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 02 at 01:54 PM

Wait a second. The foreach loop's file enumerator has nothing to do with a connection manager. The foreach loop container will get the list of files given the criteria you gave and then enumerate once for each one. In the Variable Mappings section you would declare a variable (generally scoped to the container) called FileName or similar. Each time through the loop the current filename would be assigned to that variable so you could use it within the loop.

At this point no connection manager is being used. It is also unclear where the parallel processing is happening because at this point foreach is sequential. If you wanted to do something in parallel inside the foreach it would be against the current file only. If you wanted to process the files in parallel then you'll need to use the multi file connection manager to get the files and then parallel process from there.

Please provide a screenshot of your task flow showing the foreach loop and where things are running in parallel.

0 ·

image.png (9.7 KiB)

Answer 5 · 2022-05-03T13:29:39.037Z

SubhomoyChakraborty-7706 answered May 3, '22 cooldadtx commented May 3, '22

Hi,

Thanks for your reply.

Please check the below source connection flat file where after D:\All Tutorials\NBS\ I need to pass the file name dynamically.

Currently I am using expressions in flat file connection manager like

But it wont work. So here I want to know what to pass exactly.

Similarly I have another container where to pass the file name same way but the name is different.

image.png (36.0 KiB)

image.png (27.1 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 03 at 02:15 PM

Flat file connection does not support multiple files. You cannot use a wildcard there and get it to work. You need to switch to Multiple File Connection Manager. You can then use a wildcard and enumerate the results.

0 ·

Answer 6 · 2022-05-03T13:32:48.087Z

SubhomoyChakraborty-7706 answered May 3, '22 cooldadtx commented May 3, '22

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.

image.png (27.8 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 03 at 02:16 PM

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.

0 ·

Answer 7 · 2022-05-03T18:37:05.86Z

SubhomoyChakraborty-7706 answered May 3, '22 cooldadtx commented May 3, '22

Hi

When the file is different and stored in differnent tables having different column name than how to use MFCM? Please suggest.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 03 at 07:18 PM

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.

0 ·

image.png (20.0 KiB)

Answer 8 · 2022-05-03T19:48:16.41Z

SubhomoyChakraborty-7706 answered May 3, '22 cooldadtx commented May 3, '22

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

image.png (78.4 KiB)

image.png (103.0 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 03 at 08:40 PM

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.

0 ·

image.png (26.9 KiB)

Answer 9 · 2022-05-03T19:48:57.34Z

SubhomoyChakraborty-7706 answered May 3, '22

It would be great if we can have any screenshare session.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 10 · 2022-05-03T21:23:02.19Z

SubhomoyChakraborty-7706 answered May 3, '22 cooldadtx commented May 3, '22

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."

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · May 03 at 09:51 PM

Please post a screenshot of the error along with your variable declaration.

0 ·

question