Hi

I'm having problems on create a loop package with dynamic data sources.

I have 36 databases do connect and I want to create a loop to connect
one by one and get the data (btw, all the 36 databases have the same
structure).

In the DTS I did this using "Dynamic Properties" component to change
the Datasource information. However, I have no idea how to do that in
SSIS.

Tks

CarlosR wrote:
In SSIS you have the For Each Loop container. That drives the loop. You
will need to enumerate over your source and assign the database to the
connection manager. Use an expression to do the assignment. Not sure
what your enumeration source is so bit difficult to be more precise.
Expressions replace Dynamic Properties in effect, and they can be set on
a Variable, the value can be evaluated as an expression, or on a
property, task or connection, and a few other places, but that should
get you started.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

Ok So what I think you want is to grab 36 database from the same server
and loop through them. You want this then to populate a connection
manager and then your pipeline will move the data.

Correct?

The way I would be looking to approach it would be this

ForEach Loop Enumerator
Loop over an SMO Collection.
SMO Connection Manager required

And the Enumerate property will look like this

SMOEnumObj[@Name='Databases']/SMOEnumType[@Name='Names']

Assign that to a variable.

Map the variable using expresssions and an OLEDB Connection Manager to
the ConnectionString property along with the other properties you will
need.



Allan




1. To grab a list of server

"CarlosR" <carlos.rodrigues (AT) recall (DOT) com> wrote