CODEWITHSUNDEEP
ssis
Gurpreet Singh
Gurpreet Singh

Reputation: 197

Mutiple Tables import using single dataflow in ssis

I have 10 tables I am importing to another sql server database using SSIS.

Do I have to create 10 different Dataflow tasks or can I proceed with one Dataflow task and add the 10 tables to it?

I have tried to use a single dataflow task but it is only allowing for a single table.

Upvotes: 1

Views: 4711

Answers (2)

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

Do all the source tables share one common schema? Do all the destination tables share one common schema (which doesn't have to be the same as the common schema for the source tables)?

If the answer to both questions is "yes", then you can in fact write a single Data Flow Task (whose connection managers are parameterized) and put it in a Foreach Loop container.

If the answer to either (or both) of those questions is "no", then you'll have to have separate sources and destinations. You might want to investigate Business Intelligence Markup Language as a way to generate those data flows automatically, although it's probably overkill for "only" ten tables.

Upvotes: 2

William Salzman
William Salzman

Reputation: 6446

The answer depends upon you and your best practices and how many developers you will have working on projects at the same time.

It is entirely possible to put more than one set of tables in a single dataflow. You can simply add additional sources and destinations to your dataflow. However, this is almost never a good idea as it adds to the maintenance effort later in the lifecycle of your project. It makes it more difficult to find and debug errors. It makes the entire project more complex.

If you are working alone and you will be building and maintaining this project's full lifecycle by yourself, then by all means do whatever you feel most comfortable with.

If you are in a group that may all maintain this project, I would suggest that you, at a minimum, break out the dataflow to different tables into different dataflow tasks.

If you are in a larger group and for more flexibility in maintenance, I would suggest that each dataflow be broken out into a different package (assuming 2008 or below. I have not played with the 2012 project models yet, so won't comment on them here), so that each can be worked on by different developers simultaneously. (I would actually recommend coding this way even if you are the only one on the project, but that is just the style I have developed over my career.)

Upvotes: 1

Related Questions