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

Question

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

Ryan-257272

Right there with Babe

Points: 759
More actions
February 5, 2010 at 9:33 am

#386257

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

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

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

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 1

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.

Kavitha Chidipothu SSC Rookie Points: 37 More actions · Answer 2

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

JSB_89 Ten Centuries Points: 1075 More actions · Answer 3

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

tzvikl SSC Enthusiast Points: 113 More actions · Answer 4

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