sql server programming: I would like some suggestions as to which would be the best way of doing the
following.

We have 19 companies each with their own accounts databases, all Sage Line
50 which is an ODBC database, and would like to extract some summary
information out of each database over night and put this into a summary SQL
2005 database.

I would like the main proceedure to be the same bit of code which is called
multiple times for each with a different connection string so that this can
be expanded as extra information is required. For instance initially we are
after things like last Sales Invoice date.

Thanks

--

On Sep 4, 2:50 am, Christopher Hilling
[quoted text, click to view]

You can use SSIS . Create connection and pass connection string value
by variables.

[quoted text, click to view]

I can create the SSIS to extract the data and update the SQL for one company
but I not sure how to put in the loop which would read from some setting file
to use the connection strings for the other databases.

I would like to end up with an SSIS which I put in the SQL Job Agent to run
each evening.

Where would you suggest the connection string settings are held and could
you explain how to automate picking them up within each loop and the passing

On Sep 4, 8:32 pm, Christopher Hilling
[quoted text, click to view]

If you are familiar with SSIS then I can give you one rough idea

Create a transecute SQL Task
set its Result set to full result set
Create a query which will provide all database name
Create a Variable lets give it name <dbnamelist> with scope of
package
and type to object
Go to Resultset tab and add this variable


Create For each loop container task
In collection
Set enumerator to Foreach ADO Enumerator


Assign this <dbnamelist> variable in Enumerator Configuration
Create one more variable <dbname> type string
Go to variable mapping
Assign this <dbnamelist> variable with index 0


Add dataflow task
in connection manager go datasource connection properties, go to
expression and assign Initial Catalog value of this <dbname>
variable.