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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy