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

  • 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 ?

  • 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 week, 6 days ago by  Phil Parkin.

    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

  • This was removed by the editor as SPAM

  • 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.

     

  •  

    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.

  • 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

  • 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.

  • 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

  • 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.

     

  • 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