SQL task- Sync Updates have a silent failure RRS feed

  • Question

  • Hello,

    I have an SSIS master package that spawns out and runs 6 differenent dimension update packages.

    When i begin processing each dimension table i insert an audit row into my DimAudit table. And after I am finished i update that row with the changes that have been made. I am using a simply SQL task that calls a stored procedure to do this.

    Inserting the initial audit row always works. And the underlying Dimension always seem to be updated correctly.

    Anyway it seems that updating the audit rows does not work when many of the packages have no updates. In some of the audit tables the audit table columns for the number of rows updated, inserted, and errored are left NULL. As if the update was never made. Yet the package does not fail, and the data is correct.  I suspect that they are all hitting the DimAudit table at once and one or two of them does not make it.  But it is so strange to me that they are A) not waiting for a lock to come open and B) not failing the package if an update does not finish. All of the packages have an synchronized isolation level. Which makes me think that the SQL-task is just throwing out a sql command and not checking to see what the result was. 

    Anyone have any idea how i can force SSIS to

    1) Make each package wait for its chance to update the DimAudit table and then perform the update.

    2) Fail if does not perform the update successfully.

    Wednesday, February 7, 2007 9:32 PM