Posted by jhofmeyr on 11/26/07 17:31

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
>
> Jagannathan Santhanam
>
> 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

[Back to original message]