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.

Hi, This is fairly simple if you query the sys table - dbo.sysjobhistory. You can get the latest run date/time of the job in question and its success/failure status. Then compare this latest successful job run date/time with the modified date column in your source SAP table and only get rows where modified date greater than last successful job run date/time. dbo.sysjobhistory - http://msdn.microsoft.com/en-us/library/ms174997.aspx Hope this helps! Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

If you will use any kind of the system date/time there is always risk of missing or redoing records created within few seconds of system date I would advice to keep dedicated table with the time stamp used in last successful load. The load process will do like: 1. save current date/time and last run time time in variables 2. use variables to load data. 3. update your time stamp table from variable.

Hi, This is fairly simple if you query the sys table - dbo.sysjobhistory. You can get the latest run date/time of the job in question and its success/failure status. Then compare this latest successful job run date/time with the modified date column in your source SAP table and only get rows where modified date greater than last successful job run date/time. dbo.sysjobhistory - http://msdn.microsoft.com/en-us/library/ms174997.aspx Hope this helps! Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com Ok I get the idea but I am actually unable to implement this. Since both of these column (modified data from SAP and rundate is coming from different sources..how can i bring them together and compare? I used ado.net source for SAP and execute sql task to pull the rundate but i can't bring them together and compare?

what version of SSIS your are using? if this is SSIS 2012; there is a SSIS Catalog which has detailed information about running each package, task and component which you can query on tables to get data, you can also use CDC task and components to implement an incremental load if you are using 2008; you can query job tables and others said, or create your own logging and log package times. there are some useful articles about how to implement an incremental load with SSIS 2008: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspxhttp://www.rad.pasfu.com

what version of SSIS your are using? if this is SSIS 2012; there is a SSIS Catalog which has detailed information about running each package, task and component which you can query on tables to get data, you can also use CDC task and components to implement an incremental load if you are using 2008; you can query job tables and others said, or create your own logging and log package times. there are some useful articles about how to implement an incremental load with SSIS 2008: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx http://www.rad.pasfu.com I hear what you are saying but there are limitations to what you can do in SSIS when your source is SAP which uses ADO.net connection to talk to SAP source. I am using SSIS 2008 R2. My concern is if somehow I can pull a date column from OLEDB source and compare that date to a Column from SAP- i would be all set. I need some advise on this given scenerio. Thanks.

I don't know anything about SAP I'm afraid but can you not write back to a custom table in the SAP database with the timestamp of last record loaded once your package is complete? Then use this in your select in the SAP data source in the next load?

Hi, This is fairly simple if you query the sys table - dbo.sysjobhistory. You can get the latest run date/time of the job in question and its success/failure status. Then compare this latest successful job run date/time with the modified date column in your source SAP table and only get rows where modified date greater than last successful job run date/time. dbo.sysjobhistory - http://msdn.microsoft.com/en-us/library/ms174997.aspx Hope this helps! Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com What tools in data flow/control flow can i use to "compare this latest successful job run date/time with the modified date column in your source SAP table and only get rows where modified date greater than last successful job run date/time."

Hi TruncatedHype, You can use Execute SQL Task to execute the query. The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially.For more details about it, please see: http://technet.microsoft.com/en-us/library/ms141003.aspx Thanks, Eileen