SSIS import multiple flat files to multiple tables

Question

I have about 20 TXT files daily with different names and structure together.

Files Names  -->  Table Names
-----------------------------------------------------
Tbl-Sales-yymmdd-hhmmss.TXT  -->  Table-Sales   
Tbl-Customers-yymmdd-hhmmss.TXT  -->  Table-Customers

I need to create a SSIS package to import these TXT files to SQL tables. I have tried multiple Foreach Loop Containers but once the first container complete, the rest return with empty enumerator and skip all the tasks inside. Any help would be appreciated. Thanks.

Ann L. Ann L. · Accepted Answer · 2013-04-28T14:48:46

I assume that your Table-Sales and Table-Customers tables have different collections of columns. That means that you are going to have to have one Data Flow task per destination table, at a minimum: each destination table will need its own set of column mappings.

The difficulty with this is that, as the date and timestamps on your source files change, you presumably don't want to have to change each Flat File Connection to point to the right source text file.

The simplest way to handle the situation would be to standardize your file names, in this sense: copy Tbl-Sales-yymmdd-hhmmss.TXT to a file called Table-Sales.txt in a different, dedicated directory, Tbl-Customers-yymmdd-hhmmss.TXT to Table-Customers.txt in the same dedicated directory, and set up all your Flat File Connection Managers (because you will probably need one per source file type) to point to the files in the dedicated directory. Then you will have a SSIS package that doesn't need custom programming or messing around with variables: as long as the files are there, it will execute without further intervention.

You can probably use SSIS to copy the timestamped files to the right destination file names, or do it with a bit of code, or (possibly) even with an old-fashioned .bat file. There are lots of ways.

SSIS import multiple flat files to multiple tables

1 Answers