April 30, 2006 at 6:28 pm
Hi,
I am building a data extract process to pull data from a company database. There are four different locations of this database, based in four countries. I have 20 packages that run fine from the local database in this country.
What i would like to do is run each of these four packages four times, once for each country, to pull the database into my warehouse.
How can i do this with SSIS? in SQL 2K i would have set the database based on a variable that i passed to the package, this does not seem to work on 2k5.
Any help would be muchly appreciated!
Truby Gravatt.
May 3, 2006 at 8:00 am
This was removed by the editor as SPAM
May 3, 2006 at 11:02 am
You'd need to set the connection string as an expression, passed in to a variable or similar to the package. Run the SSIS package inside of a For Each loop or similar that gets the database names from some set location - table/file/XML/etc. When you pass in that variable to the package, it should run the package for that database.
Only gotcha I can think of with this approach would be to ensure that the DelayValidation option is True for the Connect String. If it's False, you could easily run against the same DB multiple times.
I would probably test first with a simple insert into a new table specifically for testing - one copy in each db. Once you've got that insert into each table down from your main loop, you can take that same practice and apply to your package.
-Pete
May 3, 2006 at 11:19 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply