how to import 1 flat file to multiple tables in SSIS

Question

I am working in SQL Server 2008 and SSIS (BIDS). I have 1 flat file that I want to import into several tables by keying off one of the columns. The number of columns varies for each key.

For example, suppose I have a flat file with 2 different record types in it -- record type A and record type B. All records of "record type A" have 5 columns. All records of record type B have 10 columns. Column 1 holds the record type flag (e.g., 'A' or 'B') for all records. So, upon import of this 1 flat file, I want all 'A' records to go into my 'A' table, and I want all 'B' records to go into my 'B' table.

How do I achieve this in SSIS? If the 1 flat file was split up into 2 separate flat files, then I could set up a data flow task for each file. I have read that it might be best to import all records into a single staging table and then write a SELECT statement to do the subsequent imports into tables A and B. I'm not certain, though, if this is best practice.

Answer 1

You could import to a staging table, but you could also handle this in SSIS by using a Conditional Split.

You'll need a single data flow, with a data source to pick up the data from the file. The next step should be a Conditional Split, with expressions checking the value of the column:

[Column 1] == "A"

[Column 2] == "B"

Here's a screenshot of how the Conditional Split task will look while you're setting it up:

Name these two outputs appropriately, and you can then add the processing needed for the two different types of record after the Conditional Split by connecting the outputs to different tasks. Here's how that might look if you just wanted to go straight to the Destination tasks: