October 22, 2009 at 8:14 am
Hi,
I am setting up my ETL for my datawarehouse. I would love to hear some ideas on how I can configure my package so that it loops through a list of databases and performs the same action on each.
I figure this will start with a config file, containing connection strings to all the (sql server 2000) data sources I want to extract from.
Then the process will be something like:
1. Read in config file
2. Foreach connection string
>> 2a. Connect to data source
>> 2b. Extract and Load "Contract" Table, Extract and Load "Product" Table
I would like to parameterize this as I have many different data sources and I would like to have one central config file which says which data sources to process, as appose to fiddling with the innards of the package.
So my question is, Is this possible?
Thanks
Dan
October 22, 2009 at 8:24 am
Are you using DTS or SSIS? (The forum is for SSIS, but you mentioned SQL 2000 data sources, so I need to ask.)
SSIS has a very nice For Each Next loop. You could load the file of connection strings, and have it change the base connection for a data flow task by using the data from the file to build that in a variable. I've done that kind of thing many times and it works quite well.
I'm less familiar with DTS, but it might have something similar.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 22, 2009 at 8:50 am
Thanks, always good to hear to your not the only one doing something 🙂
I am using SSIS, going through an upgrade at the moment so all our production databases are SQL 2000 but I have access to SQL 2005. Hence the sql 2000 data sources for my package.
The foreach loop looks grand, did you use the "Foreach NodeList Enumerator" pointing to an xml. Will try that angle.
October 22, 2009 at 9:23 am
I now have the xml file reading and populating a variable User::ConString
Are you able to offer any more advice on setting the base connection string in the data flow. Done in code behind i presume??
Thanks again
October 23, 2009 at 6:44 am
Nah. The connection has properties. Just force the connections string to populate from your variable. It's as easy as right-clicking the connection and going to the right screen on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply