SSIS: Using a Full Result Set variable in a Data Flow Task

  • I attempting to find a solution for data extraction from a remote Oracle server using SSIS. I have a list of several hundred thousand IDs (Primary Key) in my local SQL Server database. What I would like to do is pull back the matching records in a Oracle database where the ID is shared. I do not have write rights on the remote Oracle database, so I am not able to load the list of records to a table on that server and use it in a join to extract the records I am interested in. What I was was wondering is if there is a way to pass a Full Result Set Variable to a Data Flow Task (essentially passing the list of ID's to be used in a WHERE clause of the statement. I am able to successfully build the Execute SQL Task to write the list to a object variable, but I am struggling with the next step, which is passing the list to the Data Flow Task.

    Any help would be greatly appreciated or if there is a different method that one has had success with in the past to solve a similiar solution I would definitely be interested in trying that option.

    Thanks in advance.


    Kindest Regards,

    Ryan

  • You wouldn't necessarily need to pass that recordset down to the Data Flow Task - I wouldn't think of doing that as my first choice. What I would do is attempt to construct your massive "where" clause either in the Data Flow itself (from a query that pulls the IDs you're looking for) or in a Script Task using the recordset you got from the Execute SQL Task.

    Once you have the IDs arranged into a string (or strings - depending on the limit of how many items you can have in a WHERE clause, or the limit on the query string length that Oracle would process - don't know either), then you would dynamically construct your query for use in your Data Flow Task.

    One way is to use a Script Task (as mentioned). Check out Andy Leonard's walkthrough on reading your recordset inside a Script Task. Use standard .Net code to iterate and build your entire query, or just the WHERE clause of it, and write it to a variable.

    Now use that variable as the source of a query in an OLE DB Source inside the Data Flow.

    Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
    Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
    Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

  • Hello Ryan..I'm facing the same issue now. Can you please let me know how you figured out this issue ?

  • Hi Todd,

    I looked at the example you have provided but was not able to move forward

    In my scenario ,I have 2 Tables

    Tbl_AreaCode

    305,954,234,123

    I need to pass the area codes as parameters to customer table

    select Col1,Col2,Col3,Col4,Col5 from tbl_customer where areacode in (select distinct area codes from tbl_areacodes)

    I created a ssis variable v_area of package scope and string and the evaluated the expression query "select distinct area codes from tbl_areacodes"

    In the DataFlow sql command

    select Col1,Col2,Col3,Col4,Col5 from tbl_customer where areacode in ? but it does not let me add the parameter. i have tried to use SQL command from variable for the 'data access mode', but it is not evaluating the expression . I think im missing something . Can you throw some light on the issues .

    How can we pass multiple values like in (1,2,3) to a DFT via variables from a select query. The area code should the driving table for the package which will be update with newer areacodes.

    thanks in advance

  • Hi Jampabsatish

    You should create two seperate variables, one which holds the area codes which you wish to filter in the through the WHERE clause (e.g USER::@v_AreaCodes "(305,954,234,123)"- dont forget to include the brackets).

    the second variable will be the actual SQL command (e.g USER::@SqlCommand, "select distinct area codes from tbl_areacodes where"+USER::@vAreaCodes- click evaluate expression and you will see your desired sql command).

    Now go to the DataFolw task, in source chosse "Sql Command from variable" - and select the variable User::SqlCommand

    hope this helps

    Regards

    Zvi

Viewing 5 posts - 1 through 4 (of 4 total)

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