One dataflow for sevral Oracle Instances ?

Hi,

I designed a dataflow which extracts, transforms and load (the main goals of an ETL ....) data from an Oracle db to an Sql Server 2005 db. The issue is that I would like to run this dataflow on several Oracle instances in a row. This intances are not on the same Oracle Servers. Is there a way to set only one data source for all these intances and to use a loop to execute the dataflow for each?.

Regards

Ayzan

You could place the Data Flow inside a For Each Loop. Select the enumerator best suited to the list of servers you have, and obviously pass this out through a variable(s) so that you can use the specifics in an expression on the Oracle connection.|||

Ok, that's what I tried to do. But how do you pass variables to the Data Source. by editing the XML source ? Do you have any example ?

Regards

Ayzan

|||

You need to pass the current loop value through to the connection, which is used inside the loop. Store it in a variable, then use an expression on the connection string property of the connection, used by the source component in the data flow. This article demonstrates some of the concepts:

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||Right click on your Connection Manager and then Properties. Under the Expressions property click on the Ellipses [...].

In the Property Expressions Editor choose ConnectionString for the

property and then click on the Ellipses [...] next to the expression.

In the expression builder your expression will be similiar to the follwing

"Data Source=" + @.[User::InstanceName] + ";User

ID=MyUser;Provider=OraOLEDB.Oracle.1;Persist Security

Info=True;Password=MyPassword;"

You just need to replace the parts of the connection string that are

applicable to your environment (Instance Variable Name, UserID,

Provider, Password).

This assumes you are using the tnsnames.ora to declare the instances.

Larry Pope|||

Thank you all, that works really fine.

Regards

Ayzan