Connect to multiple databases programmatically

[MDA]

Hi all,

I am trying to figure out a way to loop through multiple databases within DTS.

Basically I currently have 25 databases that have the same structure on the same SQL server. I need to extract data from each database (same table name). However, the number of databases will grow so I am trying to find a way to avoid “hard coding” the connection information for each DB, within the DTS package. I was thinking of using an Excel file or even a table that contains the DB name and somehow reference that to build the connection info during execution of the package. The UID/PWD is the same for each SQL DB.

Any ideas are greatly appreciated..

Regards,
MDA

 

[JayKusch]

An easy work around would be to use the undocumented stored procedure (SP) called sp_MSforeachtable

Example of the code would be :

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')

That way, it does not matter how many DB you have or will add.



Thanks

J. Kusch

 

[MDA]

Thanks for the tip Jay. I think maybe the sp_MSforeachdb command would be closer to what I am looking for, BUT I actually need a way to specify the DB's to use. sp_MSforeachdb will pull all DB's on the server. I am just trying to find a way to avoid setting up the connection information every time. The goal is to avoid having to make changes within the DTS package.

Thanks again, any other ideas?
M

 

[tb]

Hi MDA

See thread961-853251 as reference of how you can change the properties of the tasks/connections ect through code - this example is in an active x script in the DTS, but this can more easily be done through code as you can loop through the properties to find exactly what you need.

Hint: You'll change the DataSource property.

Hope this helps,


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!