Posted by jags_32 on 11/26/07 20:22

J

I realized that my FOREACH loop was running and fetching data from
only the 1st ERP database. How do I dynamically set the connection to
point to the appropriate ERP database within a Foreach loop? I already
have a variable that holds the ODBC database connection name that was
fetched from a table. Our ODBC database connection names are stored in
a table on SQL Server and I need to loop through rows in this table to
run the SSIS script.

Thanks

Jagannathan Santhanam

On Nov 26, 12:31 pm, jhofm...@googlemail.com wrote:
> On Nov 26, 4:57 pm, jags...@yahoo.com wrote:
>
>
>
>
>
> > J
>
> > Thanks a bunch! I was able to get this running in no time after I read
> > through your post. I did take note of your post about schema being the
> > same across the different ERP databases. We anyhow use customized DTS
> > packages in such cases.
>
> > Thanks
>
> >JagannathanSanthanam
>
> > On Nov 26, 5:06 am, jhofm...@googlemail.com wrote:
>
> > > On Nov 26, 12:34 am, jags...@yahoo.com wrote:
>
> > > > J
>
> > > > Could you elaborate on step 5?
>
> > > > Thanks
>
> > > >JagannathanSanthanam
> > > > On Nov 21, 8:50 am, jhofm...@googlemail.com wrote:
>
> > > > > HiJagannathan,
>
> > > > > You will need to:
> > > > > 1) Create a variable in the package of type "Object"
> > > > > 2) Put an Execute SQL task before your ForEach loop which returns the
> > > > > connection data from your database and puts it into the variable you
> > > > > created (Change the "ResultSet" property to "Full result set" and on
> > > > > the Result Set tab set the Result Name to 0 and the Variable Name to
> > > > > the new variable you created.
> > > > > 3) On the Collection tab of your ForEach loop, change the Enumerator
> > > > > property to "ForEach ADO Enumerator" and set the ADO object source
> > > > > variable to the Object variable.
> > > > > 4) On the Variable Mappings tab of your ForEach loop map your
> > > > > connection details to suitable variable(s). *NOTE: These variables
> > > > > will need to be at the Package scope.
> > > > > 5) Set Expression(s) using the variables set by the ForEach loop to
> > > > > configure the ODBC connection manager that your data flow source task
> > > > > is using.
>
> > > > > It is important to note that the table metadata in each of the ODBC
> > > > > databases needs to be identical in order to re-use the same data
> > > > > flow. If the metadata is different then you will need to create a
> > > > > seperate data flow for each table.
>
> > > > > Good luck!
> > > > > J- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > HiJagannathan,
>
> > > If you r-click on your ODBC connection manager at the bottom of the
> > > design screen and select Properties from the context menu. One of the
> > > properties is "Expressions". When you edit this it opens another
> > > window which allows you to select properties that you'd like to be
> > > determined by an expression. Here you need to select the
> > > Connectionstring property and set it to equal the variable that has
> > > been set to store this information.
>
> > > Good luck!
> > > J- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> Happy to help :)
>
> J- Hide quoted text -
>
> - Show quoted text -

[Back to original message]