May 4, 2012 at 10:38 pm
I need to develop a SSIS package to pull delta from SAP source. My scenario is that i have a source SAP table that has modified date column to track changes.The challenge is that I have to pull anything modified after the last run.the package will be scheduled as a job so I wonder how can I track the last run date to compare with the modified date from source. Also if something fails, everything has to be rolled back. Please advise.
May 6, 2012 at 10:34 pm
I doubt that basing your extraction criteria on the date/time of the last time you extracted data is going to give you the correct result. You really need to used the most recent value in the modified date column from the data that was extracted the last time you ran - and you will need to apply this logic to all of the table in that you are extracting data from.
If you used the last run date (of your SSIS package), you will, potentially, be basing your extract on a clock that is completely independent of the clock used by the SAP application
As far as ensuring that everything is rolled back if there is a failing, there are a number of options - they all rely on database transactions. If your updates/inserts/deletes are inside a transaction, all you need to do is rollback the transaction if you detect and error.
May 8, 2012 at 10:54 am
happycat59 (5/6/2012)
I doubt that basing your extraction criteria on the date/time of the last time you extracted data is going to give you the correct result. You really need to used the most recent value in the modified date column from the data that was extracted the last time you ran - and you will need to apply this logic to all of the table in that you are extracting data from.If you used the last run date (of your SSIS package), you will, potentially, be basing your extract on a clock that is completely independent of the clock used by the SAP application
As far as ensuring that everything is rolled back if there is a failing, there are a number of options - they all rely on database transactions. If your updates/inserts/deletes are inside a transaction, all you need to do is rollback the transaction if you detect and error.
How do I compare two columns coming from different servers. For example, if i pull a date column from SAP source and I want to compare to a system date in a completely different server, how do I accomplish that?
May 8, 2012 at 11:00 am
if you have a column other than the date time which can be used to identify the deltas that would be your best bet, Mainly becuase datetimes have problems with accuracy in the milisecond range when working with datetime datatype and also timezone and dst etc need to be accounted for. If this is not possible it would be better for you to store the last timestamp ( not of the job run but the row fecthed) into a internal config table and manage rollbacks etc by simply reusing the timestamp from this table. make sure that you dont use checkpoints etc if your looking for a complete rollback
May 8, 2012 at 1:31 pm
Jayanth_Kurup (5/8/2012)
if you have a column other than the date time which can be used to identify the deltas that would be your best bet, Mainly becuase datetimes have problems with accuracy in the milisecond range when working with datetime datatype and also timezone and dst etc need to be accounted for. If this is not possible it would be better for you to store the last timestamp ( not of the job run but the row fecthed) into a internal config table and manage rollbacks etc by simply reusing the timestamp from this table. make sure that you dont use checkpoints etc if your looking for a complete rollback
I am more concerned about how to get to the point where i can actually compare two columns. The ado source and sql destination is completely different and i cannot use lookup or merge join or even execute sql task.I need to be able bring these two columns from different source and compare them how wud i do that?
May 8, 2012 at 2:26 pm
Why can't you use a derived column, or a data conversion transform to change the data to like formats and types and then use your lookup or merge join?
May 8, 2012 at 3:42 pm
Daniel Bowlin (5/8/2012)
Why can't you use a derived column, or a data conversion transform to change the data to like formats and types and then use your lookup or merge join?
Daniel, I am grabbing a column from an independent source and trying to compare to a column coming from another server. How will this work given your advise?
May 8, 2012 at 7:23 pm
Daniel, I am grabbing a column from an independent source and trying to compare to a column coming from another server. How will this work given your advise?
This is exactly what SSIS is good at.
Example (Assuming you keep track of the latest modified date and time you have retrieved):
Create two connection managers. -- One for each independant source
Create an Exec SQL Task. -- Query your latest recorded modified datetime2 and return it to a variable.
Create a data flow.
Create a source for your SAP data. -- Parameterise your source query to only return records with a modified datetime2 greater than the one you recorded last.
As has been mentioned be particularly careful about the "datetime" data type, it does (seemingly, but not really) peculiar rounding at the microsecond level.
You would have to incorporate any 'reload' (I would avoid transactions if possible) logic and last change tracking logic into your solution.
May 9, 2012 at 12:05 pm
in the ssis package the way to go and the data flow tasks have everything u need. ideally you might still want to consider bringing the data from the othe source into some kind of staging area for your data quality checks else a failure in the data pull or load will require you to fetch the entire data set again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply