|
|
Posted by jags_32 on 11/27/07 14:21
J
I messed up. I did not have an "Execute SQL " task in front of my
FOREACH LOOP, instead I had a "Data Flow" task. I fixed it and it runs
well now (except with Double Byte ERP databases). Thanks a lot for
your help and patience.
Also, would you happen to know how a SSIS package can be scheduled as
a job in SQL 2005?
Thanks
Jagannathan Santhanam
On Nov 27, 4:33 am, jhofm...@googlemail.com wrote:
> 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
>
> >JagannathanSanthanam
>
> > 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 -
>
> HiJagannathan,
>
> 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- Hide quoted text -
>
> - Show quoted text -
Navigation:
[Reply to this message]
|