October 30, 2024 at 4:31 am
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:14 am
Yes there is. You can assign the result of running a query in an ExecuteSQL task to an SSIS variable
This variable can then be used to build another query (where the text of the query is an 'expression').
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 30, 2024 at 10:50 am
This was removed by the editor as SPAM
October 30, 2024 at 2:21 pm
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.
October 30, 2024 at 2:23 pm
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.
October 30, 2024 at 2:28 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 30, 2024 at 2:45 pm
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.
October 30, 2024 at 3:09 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 30, 2024 at 3:28 pm
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.
October 30, 2024 at 3:34 pm
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"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply