February 5, 2010 at 9:33 am
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.
Ryan
February 5, 2010 at 3:18 pm
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.
April 11, 2013 at 9:52 pm
Hello Ryan..I'm facing the same issue now. Can you please let me know how you figured out this issue ?
March 6, 2014 at 2:35 pm
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
March 19, 2014 at 1:27 am
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