You are here: Re: FOREACH LOOP container and looping through record set ... « MsSQL Server « IT news, forums, messages
Re: FOREACH LOOP container and looping through record set ...

Posted by jhofmeyr on 11/27/07 09:33

On Nov 26, 8:22 pm, jags...@yahoo.com wrote:
> 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 -- Hide quoted text -
>
> - Show quoted text -

Hi Jagannathan,

Have you configured the ForEach loop to put the ODBC database details
into the variable? It should just be a matter of having the tasks
using the connection sitting inside the loop. Each time the loop
executes, it will fetch the details of the next ODBC db into the
variable and the Expression in the connection manager will point the
connection to the new server.

Good luck!
J

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация