Passing values from a table to Oracle query in dataflowtask?

Question

Employee:

EmployeeID     EmployeeName      EmployeeNumber     StartDate        StopDate
   1            Tom                 7887             3/1/2014         3/1/2015
   2            Harry               7888             3/1/2013         3/1/2015
   3            Frank               7889             3/1/2012         3/1/2015
   4            John                7890             3/1/2011         3/1/2015

I have an Employee table in Sql Server .I am using employee information to pass values in to an Oracle query to get the data from Oracle using data flow task. But I never tried with pl/sql as it looking different from sql. My oracle query looks like

SELECT *
    FROM Components A INNER JOIN Values b
    on A.Rowid=B.Rowid
    and A.Contact IN(:EmployeeName)
    and A.Sub IN(:EmployeeNumber )
    WHERE
A.time BETWEEN TO_DATE(:StartDate) AND TO_DATE(:EndDate) + 0.99999


How can I pass my Employee table values in to above sql query using for loop Container.

NOT THE DOWN VOTER :) -- What would your loop container loop over? In other words would the Oracle query get fired for every EmployeeName, EmployeeNumber combination? — TMNT2014
– TMNT2014, Commented May 16, 2014 at 18:44
@godzilla2014 Yes oracle query should be fired for every EmployeeName, EmployeeNumber . — user3203331
– user3203331, Commented May 16, 2014 at 19:13
"I am looking how to pass the values to from the table to variables in SP using for loop container in SSIS. " Are you using an Oracle SP now instead of the query? Or did you have any issues passing in the variables to the query? — TMNT2014
– TMNT2014, Commented May 19, 2014 at 15:16
@godzilla2014: i am using execute sql task to get all the values from the table.My next step is i am using for each loop conatiner where i a getting single row for varaible .How can i assign first row values to User defined variables : EmployeeID, EmployeeName, EmployeeNumber, StartDate,StopDate so i can use them in DFT for Oracle source?? — user3203331
– user3203331, Commented May 19, 2014 at 15:20

TMNT2014 · Accepted Answer · 2014-05-19 15:43:50Z

1

+50

You can query Oracle just as you do your SQL Server. In your case you would have to build the Oracle query from your look up variables. For example your variable ExtractSQL would be an expression like below-

"SELECT * FROM Components A INNER JOIN Values b on A.Rowid=B.Rowid and A.Contact = '" + @[User::EmployeeName] +"' and A.Sub ='"+ @[User::EmployeeNumber] + "' WHERE A.time BETWEEN TO_DATE('"+ @[User::StartDate] +"') AND TO_DATE('"+ @@[User::EndDate] +"') + 0.99999"

Depending on how you have stored the date on your end you might have to pass a second parameter to the To_Date function for the format Eg. TO_DATE ('2014-05-14 15:01:27' , 'YYYY-MM-DD HH24:MI:SS')

-To answer your question about setting the variables.

Create a variable called EmployeeRecordset of type "Object".
In your execute SQL task in the Result Set tab, have Result Name 0 and Variable Name as User::EmployeeRecordset.
In your for each loop, set the Enumerator as ForEach ADO Enumerator, Set the ADO Object Source Variable as User::EmployeeRecordset. Enumeration mode you can leave it as Rows in First table.
In Variable mappings set your row level variables - i.e. Employee Number, Employee Name etc. in the order it is returned from the query starting at an index of 0.

Now you should be able to use the variables inside the loop to form your Oracle query.

edited May 19, 2014 at 15:43

answered May 16, 2014 at 19:37

TMNT2014

2,1401 gold badge12 silver badges13 bronze badges

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

12 Comments

user3203331 Over a year ago

Thanks a lot brother.inputs to oracle must look like 14-MAY-2014 .

TMNT2014 Over a year ago

That's the default but you can control the format by using the second parameter to the To_DATE function. Anyways glad I could help...

user3203331 Over a year ago

he single quotation mark at line number "15", character number "32", was not expected.

TMNT2014 Over a year ago

You had the quotation marks in the wrong order and you would also need () around theemployee name for "IN" to work - "SELECT * FROM Components A WHERE A.Contact IN ('"+ @[EmployeeName]+"')"

TMNT2014 Over a year ago

Can you check the format of the value that is set for @[User::StartDate] and @[User::EndDate]? Obviously the (DT_WSTR, 10) is truncating the string. Instead of having the variables @[User::StartDate] and @[User::EndDate] as datetime type, have them as String Type.

|

sabyasm · Accepted Answer · 2014-05-19 12:27:29Z

0

It should be something like this:

Dataflow task (DFT1) to build a rowset - that we would loop though in Oracle.
User defined variables : @EmployeeID, @EmployeeName, @EmployeeNumber, @StartDate,@StopDate
DFT1 - A sql query to load the data into a recordset destination. You can write the query as your wish to format the data, that is, for example define @StartDate as string and convert '3/1/2014' into the desired date format in Oracle
Now use foreachLoop enumerator for the Ado .NET recordsets and loop through.
Inside the foreachLoop enumerator, you should have the data flow task that uses the variables above to form a SQL statement (Meant to be executed in Oracle) using expression. You will need to use SQL Command from expression option there
Destination!

answered May 19, 2014 at 12:27

sabyasm

563 bronze badges

2 Comments

user3203331 Over a year ago

I am totally confused how can we assign my first row values to the User defined variables : EmployeeID, EmployeeName, EmployeeNumber, StartDate,StopDate.

sabyasm Over a year ago

You can see it here and here. This is the simplest way to loop through all your records.

Collectives™ on Stack Overflow

Passing values from a table to Oracle query in dataflowtask?

2 Answers 2

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related