How to pass a variable in look up transformation in SSIS ? With Oracle database

Question

Post reply

How to pass a variable in look up transformation in SSIS ? With Oracle database

SKAK7102

Valued Member

Points: 67
More actions
October 30, 2024 at 4:31 am

#4475230

We have a need to pass a variable in the look up query against Oracle database using SSIS, is there a way to pass a Sql query output (variable) in SSIS ?
October 30, 2024 at 10:50 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Yes there is. You can assign the result of running a query in an ExecuteSQL task to an SSIS variable

Results

This variable can then be used to build another query (where the text of the query is an 'expression').

This reply was modified 1 month, 3 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SKAK7102 Valued Member Points: 67 More actions · Answer 2

Thank you for the response, but that is using Execute Sql task not the Look up transformation ? May be I was not clear, here is the situation - The situation is such that there is Azure Sql DB, with 2 columns - ID, Login Date and Oracle DB with 5 columns - ID , Dep ID, Start date , End date, Fund Code. Azure Sql DB needs to be updated with DeptID and Fund Code from Oracle based on the Azure Sql Dbs login date column , the way I designed it is OLD DB source for Azure Sql selects two columns and in the Look up transformation, I am selecting Oracle data , however since the login date differs I need to pass ID and Login Date to Oracle to pick up the exact Dept ID and Fund Code for that specific date period.

Source Azure Sql - ID, Login Date

Source Oracle - ID , Dep ID, Start date , End date, Fund Code.

Based on the login date , the value from Oracle needs to be picked up and updated for DeptID and fund code in Azure DB. Basically adding two new columns to Azure SQL DB with updated info.

Any help is appreciated.

SKAK7102 Valued Member Points: 67 More actions · Answer 3

Thank you for the response, but that is using Execute Sql task not the Look up transformation ? May be I was not clear, here is the situation - The situation is such that there is Azure Sql DB, with 2 columns - ID, Login Date and Oracle DB with 5 columns - ID , Dep ID, Start date , End date, Fund Code. Azure Sql DB needs to be updated with DeptID and Fund Code from Oracle based on the Azure Sql Dbs login date column , the way I designed it is OLD DB source for Azure Sql selects two columns and in the Look up transformation, I am selecting Oracle data , however since the login date differs I need to pass ID and Login Date to Oracle to pick up the exact Dept ID and Fund Code for that specific date period.

Source Azure Sql - ID, Login Date

Source Oracle - ID , Dep ID, Start date , End date, Fund Code.

Based on the login date , the value from Oracle needs to be picked up and updated for DeptID and fund code in Azure DB. Basically adding two new columns to Azure SQL DB with updated info.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

If you modify the source query for the Oracle data to select where

StartDate <= LoginDate <= EndDate

, can you then do the lookup in the way that you wish?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SKAK7102 Valued Member Points: 67 More actions · Answer 5

I use the SELECT ID , Dep ID, Start date , End date, Fund Code FROM ORACLETABLE WHERE (TO_DATE(?, 'YYYY-MM-DD') BETWEEN OracleTable.StartDate AND OracleTable.EndDate in the LOOK UP transformation.

If you look closely the '?' I use is where I need to pass the 'Azure.LoginDate' and Since it's in Azure, the query fails in SSIS.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

Create a variable and add an expression which evaluates to the exact query you want to execute. Use that variable as your source query.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SKAK7102 Valued Member Points: 67 More actions · Answer 7

That's exactly where the problem begins. For the variable I had assigned the value as 'Select logindate from Azuredb' and then used that variable in Oracle query in the look up and then the error shows up.

Ed B Hall of Fame Points: 3669 More actions · Answer 8

One solution might be to use a an explicit cache transformation for the lookup. The cache has to be loaded using a data flow first, but that allows the source to the a query from a variable. The variable is set with an expression so only the resolved query is passed to Oracle. The main data flow then uses the cache transform for the look up. The expression for the cache source might look something like this.

"SELECT  ID , Dep ID, Start date , End date, Fund Code FROM ORACLETABLE WHERE to_date('"+ 
@[User::AzureLoginDate] + "', 'YYYY-MM-DD') BETWEEN OracleTable.StartDate AND OracleTable.EndDate"