Data Import - Dynamic File Name

Question

Data Import - Dynamic File Name

tinausa

SSCrazy

Points: 2697
More actions
May 27, 2020 at 6:24 pm

#3757822

I am a newbie to SSIS. I created th3e workflow to import csv files. But the file names change from one customer and month to another and are not consistent. How do I use dynamic file names? Or use a dialog to give file name when importing.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

palandri Old Hand Points: 387 More actions · Answer 1

I think the answer might depend on how many files you are processing in each run, if there are files in the same folder that you do not want to process, and if there are any similarities in the file names, e.g. a common prefix.

I have some imports of single files sent to me by users. Since these are on demand imports of single files, when I receive one, it’s not a problem to rename it to the name the package expects.

If you process multiple files at once, if you put them all in a single folder and there are no files in that folder that should not be processed, you could use a fully wild carded name in the FOR EACH loop. If there are files in that folder that must be skipped and the file names to import have any similarities, you may be able use a partial wild card name to select only those files.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

tinausa wrote:

I am a newbie to SSIS. I created th3e workflow to import csv files. But the file names change from one customer and month to another and are not consistent. How do I use dynamic file names? Or use a dialog to give file name when importing.
Thanks.

First of all, SSIS is a server-based technology and dialogs are therefore not the way to go. The package executes on the server and there's no one there to respond to dialog boxes.

The FOREACH container is almost certainly the way to go. This will loop round all files in your selected folder which match the filespec you provide and allow you to map the 'current' file name to an SSIS variable, which itself can be used to construct a connection string, to be used by your data source.

This method works best if all of the CSV files have exactly the same format (column names, column order and number of columns).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Are all of the CSV files structured the same? The same number of columns - the same types of values for each column?

If they are all the same you can use a for each loop to provide the file names...if they are not then you need a different package for each one or you need to build a dynamic SSIS solution to process each file as a new file (every time) with a new destination table that is built dynamically. That is a very complex solution and requires a lot more work in SQL...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

tinausa SSCrazy Points: 2697 More actions · Answer 4

There may be 3-4 csv files in each run. The names may not be constant, but the issue is that the process will be run such that, another system will place the files in a specific folder and based on some specific characters in the file name the file will be loaded into different tables.

Thaks.

tinausa SSCrazy Points: 2697 More actions · Answer 5

ok thanks, I already went thru Google search and did not find anything specific.

There are multiple csv files and depending on naming convention of a part of the file name, the file would be loaded into different tables.

tinausa SSCrazy Points: 2697 More actions · Answer 6

Well, all csv files belongign to a specific table are structured the same. BUt based on some characters in the file names, they are differently structured nad go into different tables.

I think the best way may be to just some write some code that can then insert into respective tables.

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

tinausa wrote:

Well, all csv files belongign to a specific table are structured the same. BUt based on some characters in the file names, they are differently structured nad go into different tables.
I think the best way may be to just some write some code that can then insert into respective tables.
Thanks.

This is all possible in SSIS, but as you are new to the technology, you may find the learning curve a little steep. Here is one possible solution architecture.

Design a separate import package for each of your different file types. Use a parameter to control the file path (this will be passed to the package from a 'parent' package at run time).
Create a master package, which does the following:

Foreach loop (FEL) container to iterate round all *.csv files in your inbound folder
Within the FEL, add logic which sets a variable, depending on the 'specific characters' in the file name
Also within the FEL, add execute package tasks, for all of the packages you created in (1). Pass the current full file path to each of the packages as a parameter.
Also within the FEL, add 'Expression and Constraint' precedence constraints which direct flow to the execute package tasks, based on the contents of the variable mentioned above.
Also within the FEL, add logic to archive the file which has just been processed to a separate folder.

Once this is set up, you can schedule your master package to run as frequently as you wish. If there are no files in the inbound folder, the package will complete successfully without doing anything.

This reply was modified 4 years, 5 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin