May 10, 2010 at 8:43 am
I need some help and direction. I have two situation. Currently I am migrating 20+ Access databases to SQL Server 2005. Each database has the exact same structure, just different data. The data is pulled in nightly from the order entry system. That order entry system is a foxpro application which, like the Access DB's is 1 for each location. The update routines in Access to bring the data in each night are the same across all of the databases. The end goal will be to create a single database in SQL to replace the Access databases. I am currently bringing them over into SQL individually as I will have to rewrite the front end app as well, and can only do one at a time. I cannot do anything about the individual order entry applications at this point so, just like I am doing currently, the update process will hit each one.
I can and have created SSIS packages to handle the updating routines for the tables, that's not an issue. The issue is that the package will need to be the same for all location updates, but the data source (connection managers) will be different.
I have been searching the web and it looks like creating a Package Configuration is what I will want to use. Is that so? If so, how can anyone point me in the right direction as to how to use the connection once created? I have created one as a test using steps I found when researching, but it stops there at the creation.
Any help and/or direction would be greatly appreciated. Thanks
May 10, 2010 at 11:41 am
If I understand your requirement you want to run a process against 20 seperate database files. It is the same process for each and the only thing different is the connection string to the file.
I don't think config files are the right way. I would use a ForEach Loop and read the filenames from a table and then build a connection string. The using expressions I would assign the connection string to the connection.
CEWII
May 10, 2010 at 3:24 pm
Elliott W (5/10/2010)
If I understand your requirement you want to run a process against 20 seperate database files. It is the same process for each and the only thing different is the connection string to the file.I don't think config files are the right way. I would use a ForEach Loop and read the filenames from a table and then build a connection string. The using expressions I would assign the connection string to the connection.
CEWII
I'll second that. If you're keeping all of the Access databases together in a tight folder structure, you wouldn't even need to keep the file names in a table - the foreach loop will find them all for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 10, 2010 at 4:31 pm
Thanks. I took that approach and it seems to be working well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply