SSIS: import multiple files to separate destination tables

Question

I'm new to SSIS. I'm running BIDS under SQL Server 2008 R2. I have several text files that I need to import into separate SQL Server destination tables. The tables already exist in the DB. Each file will map to only 1 table. (For example, file_A maps to table_A, and file_B maps to table_B.) My general data flow is as follows:

Flat File Source
Data Conversion (to handle the issue of unicode vs non-unicode strings)
OLE DB Destination (to handle the issue of local server to remote server)

Do I need to create a separate data flow task for each of my text files? If so, my package may be very large.

Yes, do create a separate data flow for each file. Otherwise, you're going to spend a lot of time waiting for validation to complete. Plus, things can get ... odd when you have many unrelated things in a dataflow. You might be interested in creating a package per source file. Without knowing your business requirements, it's hard to say if that's a good pattern — billinkc

Tab Alleman Tab Alleman · Accepted Answer · 2014-12-10T21:37:23

You can create a single data flow task that has more than one "flow" in it. Hard to describe with words, but you can put a source1 that flows to DataConversion1 that flows to Destination1 and then alongside it (no connections) Source2 flows to DataConversion2 flows to Destination2, and so on.

However, I do agree with @billinkc that using a separate dataflow for each is the better way to go. It will make debugging easier, in addition to the other benefits he mentioned.

SSIS: import multiple files to separate destination tables

1 Answers