Loading files with varying number of columns into the same table with SSIS

0
votes

I need to load 14 different files with a different number of columns into the same table. Many of the columns are the same, but each file has some columns that are specific to them. The name of each file indicates the columns the file will have. For example, the file "Temporary_Employees.txt" will always these have columns:

Contract_ID | Person_ID | HireDate | ContractDurationHours | Manager_ID

And the file "PartTime_Employees.txt" will have these columns:

Contract_ID | Person_ID | HireDate | HoursPerWeek | Manager_ID

The destination table has all the columns.

I'm new to SSIS and the only solution I can think of is having 14 data flows with 14 flat file connections... Is there a better way to do this?

1
sql-serverssisoledbflat-file
You can use a Script Block to create and invoke a programmatic package: the destination table contains all possible columns; the script block reads the header row of a file to determine the source columns; the script block creates a programmatic package with flat file reader, a destination table and maps the columns between them. This works great for hundreds of different files but if you're only dealing with fourteen it could be more effort than it's worth. – AlwaysLearning
Is it possible to convert your .csv/.txt files into XML format? If the answer is YES, it makes it very easy by using SSIS XML Source Adapter. XML elements are optional, and whatever tag is missing - it will be loaded as NULL value. – Yitzhak Khabinsky

1 Answers

0
votes

You will want to create a flat file connection manager per unique file format.

If there is a shared format, FullTime_Employees.txt and Management_Employees.txt then you can use a Foreach File Enumerator to loop over files (changing the connection manager's connection string property) and have the data flow use the updated connection manager.

Design a data flow to do a single task - this one loads part-time employees, this one loads temporary and this one loads full-time employees. It might feel like more work up front but trying anything clever can be a nightmare to debug.

Nightmare approach

SSIS Task for inconsistent column count import?

If you don't need the "extra" columns populated in the destination, you can use the query approach I outlined on the above old answer. Basically, you'll write a query that enumerates all the column names that are common across files. That will result in a consistent set of metadata available and then you can use a foreach file enumerator to process all the files.

I classify this as a nightmare approach because while it works, if this is your first foray into SSIS, this solution incorporates a lot of advanced techniques.