Post reply

SSIS Package executes but won't commit any changes

  • December 1, 2011 at 11:26 pm

    #247876

    I have a package which in tern calls multiple packages. Each sub package is supposed to copy data from OLE DB source(SQL DB1 - Table) to OLE DB destination(SQL DB2 - Table) . An Execute SQL Task will then delete data from Source table. The job executes with out any errors at all. But the data is not copied to destination. It also won't delete data from Source table.

    The log is clear of warnings and errors. Package executes for over an hour. While executing I can see each statement being executed in Activity Monitor.

    I am kind of lost at this point. Any help is greatly appreciated.

  • December 1, 2011 at 11:45 pm

    #1415703

    Sounds like possibly a transaction rollback. Do any of your statements have a transaction declared and a rollback with them?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 1, 2011 at 11:57 pm

    #1415708

    No. There are absolutely NO Transactions OR roll backs used in the statements. The source is a simple select query (SQL Command) with a cut off date. In an earlier run when I had Table or View as the Data Access Mode (BY error) the entire table has been copied to destination. But I needed a subset of data. So I changed back to Data Access Mode to SQL Command on OLE DB Source and now it wont copy data.

  • December 2, 2011 at 12:09 am

    #1415718

    Am I missing any package properties that should be set so it would commit/move the data to OLE DB destination?

  • December 2, 2011 at 12:46 am

    #1415732

    Jason,

    I figured that cutoff date was incorrectly updated to another test database via manual script.

    So the actual cut off date used on the staging data source was less than the minimum date of criteria column.

    Now that I updated the criteria (cut off date) correctly, It is executing fine now.

    Thanks for your time.

    mVenu.

  • December 2, 2011 at 7:31 am

    #1415885

    That's good to hear.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply