Steps to get files as per the file name

Subhomoy Chakraborty 106 Reputation points

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.196940-image.png

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
0 comments No comments
{count} votes

12 answers

Sort by: Most helpful
  1. Michael Taylor 51,346 Reputation points

    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.

    0 comments No comments

  2. Subhomoy Chakraborty 106 Reputation points


    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 197949-image.png

    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.

    0 comments No comments

  3. Michael Taylor 51,346 Reputation points

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

    0 comments No comments

  4. Subhomoy Chakraborty 106 Reputation points


    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 198169-image.png

    and in source connection manager expressions 198216-image.png

  5. Subhomoy Chakraborty 106 Reputation points


    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 198544-image.png

    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.