We have a pretty simple data flow that fetches data from our Source
ERP system and dumps it into a SQL Server table. This functionality
works, what we are trying to do now is to extend this functionality by
looping through a table in SQL Server which has a list of all ODBC
connections, changing the source connection dynamically. How do I do
this? For some reason, the SQL Server online tutorial is useless since
it just deals with file looping.

Appreciate any feedback.

Thanks

Jagannathan Santhanam

Hi Jagannathan,

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

J

Could you elaborate on step 5?

Thanks

Jagannathan Santhanam
On Nov 21, 8:50 am, jhofm... (AT) googlemail (DOT) com wrote:

On Nov 26, 12:34 am, jags... (AT) yahoo (DOT) com wrote:
Hi Jagannathan,

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

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... (AT) googlemail (DOT) com wrote:

On Nov 26, 4:57 pm, jags... (AT) yahoo (DOT) com wrote:
Happy to help

J

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... (AT) googlemail (DOT) com wrote:

On Nov 26, 8:22 pm, jags... (AT) yahoo (DOT) com wrote:
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

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... (AT) googlemail (DOT) com wrote:

On Nov 27, 2:21 pm, jags... (AT) yahoo (DOT) com wrote:
Hi Jagannathan,

I usually create jobs to run packages manually using DTSRun.exe. I
can't think of the reason why (I've been doing it this way for a long
time ) but it might have to do with wanting to ensure that the
64bit version of DTSRun is used when deploying to a 64bit server
(which is usually the case).

If you'd like to do it through the SSMS, you'll need to do the
following:
1) Expand SQL Server Agent on the SSMS Object Explorer
2) R-click on the Jobs folder and select "New Job..."
3) Give the job a name and add a new step
4) Change the step Type to "SQL Server Integration Services Package"
and set the package details below.
5) Add a new Schedule for the job. This is where you actually define
when the package should be executed.

That should be it. If the job is enabled, it should run according to
the schedule(s) you've created for the job.

Good luck!
J