Copy one table to multiple databases 1

[mlowe9]

Fairly new to SSIS, haven't done much with it.

I have a "template" database where I have a table I want to "roll out" to multiple databases.

I am currently doing this using SSIS by hard-coding all of the connections and doing the same Data Flow tasks over and over.

However, when I need to add a new connection, I have to go through adding all of the new tasks and so forth.

I am thinking there must be a way to simplify this. For example, by using the Foreach Loop Container - and maybe somehow using variables to specify the databases and/or tables I need. I have been unsuccessful at finding information on doing this, and was hoping one of you experts can help me get started.

 

[MDXer]

is it the actual table you want to rollout or the data within the table? you could create a packag that does both. However if it is the data you want pushed out to multiple databases having a package loop through and connect to multiple databases would probably be the least efficient way.

If you are always pushing out to the same databases a faster way would be to utilize the multicast within a dataflow. This would be faster in that you only extract the data once, but have the ability to output a single buffer to multiple destinations.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mlowe9]

The data within the table is sufficient for what I'm doing. Multicast - can't say that I'm familiar with that either, but I'll certain take a look.

 

[MDXer]

Understanding how SSIS processes data will help you better understand why in this situation a multicast is going to be higher performing than looping through and dynamically setting your destination.

SSIS processes data in buffers by default these buffers are 10k rows or 10MB in size by default.If 10K or more rows can fit into a buffer it will place 10K rows if 10K rows will exceed 10MB then it will place the nuber of rows it can fit into 10mb without flowing into the next buffer.

Some tasks create new buffers while others won't a multicast does not create new buffers so the tasks after a multicast really share the same buffer.

So if you have a Data Source followed immediately by a multicast which then outputs to 5 destinations. This process utilizes a single buffer so if you had 10K rows to process and they fit within the 10MB all 5 destinations would load in parallel.

This requires a single data extract a single dataflow validation, where if you looped it you would extract and validate in each loop all of which takes time.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mlowe9]

That does indeed make sense. I'm playing with it now.

 

[mlowe9]

May I ask another thing? I am unsure how variables work in SSIS, but could I assign the table name to a variable and somehow use that variable to copy the table?

That way I can set it up one time to copy a table from my template database, then I can simply change the variable to copy a different table - instead of going through all of the connections changing that table name?

 

[MDXer]

Yes BUT!

How many source tables and how many destination tables?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mlowe9]

Sorry again - I keep thinking of questions.

Is there a flag to purge the table before copying the new data? I've been using a SQL Task before the copy, but I'm thinking it would be logical to have a flag to do it automatically - I just don't see one.

If not, I can continue using the SQL task.

 

[MDXer]

no there isn't and it really does suck at times. You have to utilize a SQL task prior to the Data flow to execute either a delete or truncate.

do you need to fully replace all data or just add new data but you delete so as not to duplicate existing data?



Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mlowe9]

I would like to set it up to have 1 source table (from the source database) - and copy to 1 destination table (but on multiple databases - 15 or more).

I'd like to have the table names stored in a variable (or 2, one for source, one for destination in case the table names arent' the same), so I could just change the 1 or 2 variables rather than the 16 sources/destinations in the Data Flow. I realize I could set up a different package for each table, I was just thinking a variable may be easier.

Replace all data. I can do a simple delete SQL Task, which is what I do now.

Thanks again for your help.

 

[MDXer]

I think you will find that the administration and trouble shooting of a package designed this way to be more effort than creating a package to move data from 1 table to your multiple destination tables.

Personally I would not accept a package designed in this manner and would have the developer redo it.

Just because you know how it works doesn't mean the guy tasked with supporting it will of the guy charged with trouble shooting what went wrong during a process. When it comes to SSIS Control and Data Flows KISS is the way you want to design.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mlowe9]

Point well taken - here's a star for your time. I think this multicast thing will be much better than what I'm doing now. Thank you very much.

Hopefully I won't come up with any more questions.
Matt