locked
Last Run Date RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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.
    Sunday, May 6, 2012 5:05 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    • Proposed as answer by Sanil Mhatre Tuesday, May 15, 2012 9:50 PM
    • Marked as answer by Eileen Zhao Friday, June 1, 2012 6:13 AM
    Monday, May 14, 2012 8:11 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Sunday, May 6, 2012 6:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    Sunday, May 6, 2012 7:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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?

    Monday, May 7, 2012 6:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Monday, May 7, 2012 8:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.
    Monday, May 7, 2012 9:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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?
    Tuesday, May 8, 2012 3:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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."
    Tuesday, May 8, 2012 4:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    • Proposed as answer by Sanil Mhatre Tuesday, May 15, 2012 9:50 PM
    • Marked as answer by Eileen Zhao Friday, June 1, 2012 6:13 AM
    Monday, May 14, 2012 8:11 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    TruncatedHype,

    Did you ever get this figured out?  I'm also trying to use ado.net as a source, but I only want to bring in records that have been updated or added since the last time this package was run.  I did create a SQL table with one column and field storing a date, which I hope to figure out how to update from the package as well.  However, my first challenge is how do i write the code behind the source control to only select records (from my SQLite file) with an updated date >= the value in my SQL table with the last updated date in it?

    I created a variable, but am stumped on how to auto-populate it with the date stored in the SQL table with the last update date in it.  I'm assuming I could then use it in the where clause of the select query in the source control....?  I'm still learning SSIS myself and haven't touched on variables too often yet.

    Thanks!

    Dana

    Wednesday, September 19, 2012 6:13 PM