passing Execute Sql Task Result set object to For Each Loop container

Question

i have written a oracle query in Script Task and assigned it to string variable. This is my query

string oracle_query = "SELECT bank_ID, Branch_ID, Trans_NUM, Trans_DT, convert(Varchar(25),quantity) AS quantity  FROM OPENQUERY(orcale_server_name,  'SELECT * "
                + "FROM oracletable" +
                 " where bank_ID =''" + Dts.Variables["User::bank_ID"].Value.ToString() + "'' AND Trans_DT = ''" + Dts.Variables["User::Trans_DT"].Value.ToString() + "'' AND quantity = " + Dts.Variables["User::quantity"].Value.ToString() + " AND Branch_ID = ''" + Dts.Variables["User::Branch_ID"].Value.ToString() + "'' ')";

the result of this query has been assigned to string variabl which i have declared in my package

Dts.Variables["oraclequery"].Value = oracle_query; .

output of this query is

bank_ID  Branch_ID  Trans_Num  Trans_DT   Quantity  
12         13       12AS566    2012-01-01  4000

Based on Trans_Num column i need to update one more table.

Instead of script task i can use oledb TranFormation, but while passing Running parameters in Oledb Source, no data is coming.

So the result of script task i have assigned to OracleQuery varibale and passed as source variable in Execute Sql Task and resultset as Full Result Set .

This Execute SQl Task resultset i have passed to for each loop container and inside varibale index i have assigned OracleQuery Varibale.

but still no data is populating

praveen · Accepted Answer · 2012-07-30 08:19:29Z

2

1.First you need to create variables for the column being retrieved from your oracle query

2.Then the variable oraclequery should be of type System.Object

3.In the resultSet tab of Execute SQL task assign the result to the variable oraclequery

ResultName    VariableName 
 0            oraclequery

4.Then in the ForEach Loop use ADO Enumerator and select ADO Object SourceVariable to oraclequery and click the radio button Rows in the First Table

In the variable mapping of for each loop, map the columns in the same order as it is being retrieved from the query .

 Variable     Index 
  BankId       0
  Branch_ID    1 
 ....          ..

Now you can access these variable inside inside a DFT placed in the Foreach loop and then use OLEDB component to update another table based on the Trans_Num

For more details refer this article and this

edited Jul 30, 2012 at 8:19

answered Jul 30, 2012 at 8:09

praveen

12.3k1 gold badge44 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1348424 Over a year ago

what should i use in Execute SQl task as source . i canot write direct oracle query there. i think it wont supports

praveen Over a year ago

Having connectionType as OLEDB it does support oracle query

praveen Over a year ago

When you write that query in execute sql task ,you need to specify the parameters for the variable in your where clause blogs.msdn.com/b/msdnts/archive/2006/11/17/…

user1348424 Over a year ago

i mapped, variables to for each loop container, to check whether am getting the values or not, i have placed script task inside foreach loop and in readonly varibales i have one of the 4 variables user::TransNum and inside script task i have written string trans = dts.variables["Trans_Num"].value.tostring(). But after for each loop it is not going inside Script task

praveen Over a year ago

r u getting values from the oracle query ? try placing a script task after execute sql task and see whether the query returns value or not

|

Collectives™ on Stack Overflow

passing Execute Sql Task Result set object to For Each Loop container

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related