August 1, 2013 at 8:03 am
Hello,
I am not sure how to proceed here... I will try to expline my situation clear....
I need to write a SSIS package for Incremental download of data from Oracle source to SQL Server 2008 destination. With every download.. there might be combinations of Insert/Updates/Deletes.and there is a primary key column and a last_update column both in source and destination.
What I want to do is.....
Look at the maximum value of last_update in the destination table, then go to source and get the data which is one day before max(last_update) column..... (For ex: If max(last_update) in destination is 07/31/2013..... then I need to get all rows with last_update = 07/30/2013 from the Oracle source)...
My question is: How can I get this done in SSIS package, may be it's already is there is google... but I failed to get it.....
Thank you all for your replies.
August 1, 2013 at 8:18 am
Have an Execute SQL task that gets the value you need from the destination, and then configure your data flow so that it only imports the data you require according to that value.
John
August 1, 2013 at 8:28 am
Thank you for your reply....
can you please allobrate the step you mention, how to pass the value from the "execute sql task" to ADO.NET connection source (in btw... we are using ODBC connection to connect to Oracle source).
I guess I have to use a SSIS package variable, but I'm not sure how to use in execute sql task and pass the value to ado.net connection.
Thank again
August 1, 2013 at 8:31 am
Use an output parameter or result set in your Execute SQL task to get the value into a variable, and then user that variable to configure your data flow.
John
August 1, 2013 at 8:39 am
Thank you... let me try it
Thanks Again
August 1, 2013 at 1:32 pm
Hello John.. thanks for your reply....
I'm getting this error, when tried to run it
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Variable": "Unable to find column date in the result set.".
below are the steps i did:
created a SSIS variable with name "Variable" with string datatype
Configure the "Execute SQL Task" with
Result Set: Single row
ByPassPrepare: False
Parameter Mapping: configure to ssis variable, direction: Input, datatype: nvarchar, parametername: 0, parametersize:50
Result Set: Configures to created ssis variable and name given as "date"
Please let me know, what am I missing here??
Thanks Again
August 2, 2013 at 1:24 am
You need to use Parameter Mapping or result set, not both. If you use Result Set, the results of your query are saved to a variable of type Object, which can be looped through in a task further on in your Control Flow. If you use Parameter Mapping, you write your query to output a value to a variable, and you map that variable to your SSIS variable in the Parameter Mapping tab. The rules governing placeholders and parameter naming are a little complicated, so search the web for tutorials on how to do this.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply