SSDT Data Corruption

  • Hi,

    We have a problem with a SSDT Data Flow task corrupting data. For example we have a source table with a field that contains a value of 'N'. When I test the query the data preview shows the correct value. However, when the package is called using SQL Server Agent the 'N' value is posted to the destination table as a 'Y'.

    This problem does not affect all rows in the data flow, but roughly 10%. We have similar problems with 2/3 other columns in the same data flow. The remaining 80 or so columns in the data flow are delivered correctly.

    The source and destination tables are in different databases on the same SQL server. I have tried configuring the OLE Destination to use both Table and Table Fast Load options. I have also changed the Destination Connection Manager from using the OLE-DB driver to one using a SQL Native Client driver from the drop down list in the Connection Manager editor.

    I have built and re-deployed the project a number of time so I don't believe it is an out of date version in teh Integration Services Catalogue being called by the Agent.

    Any ideas would be much appreciated.



  • As a thought, are you using NOLOCK?

    As a second thought, are you doing any transforms on the data in the Data Flow task that MAY be changing the data?

    Another thought, any chance the data is changing on the source AFTER the SSIS package is run?

    Yet another thought, any processes being run AFTER the data is copied over that MAY be changing the data?

    Final thought - are you removing the data prior to the data load (truncate and refresh)?  With this thought, I am wondering if MAYBE the N's and Y's that you are seeing incorrectly are actually correct, but that you have the old row (where it was a Y) AND the new row (where it is an N) in your table?

  • Brian,


    Thank you for taking the time to reply. To answer your thoughts in order:

    1: NOLOCK is disabled in the OLE Destination connection

    2: There are no transformations that could change the errant columns

    3: There are no subsequent process that may be changing the data - this is the last process that looks at this particular data on a daily basis

    4: The process in question takes data from an Invoice table and places it in a work table. The work table is cleared (using a delete statement but  we had the same problems when this table was cleared by a Truncate statement) before being populate with the invoice data. A SQL update process then updates some current month and YTD sales values in this work table. At this point both the source table and the work table have the correct value. The final data flow then transfers the data from the work table to the reporting table with no data transformations in this final stage. This is where the value gets changed. After the event I can still see that the work table has the correct value, but the final summary table is wrong.

    Thanks again,


  • Depending on how you are doing the move from work table to reporting table, that could be the problem.  Is it a wipe and reload?  Is it a merge?  Is it an INSERT, UPDATE, DELETE process?  You know the data is good in the work table but breaks when going to the reporting table.  Can you modify the SSIS package to ONLY do the work table to reporting table step and also use a different, new "final" table?  My thoughts here are that way you can verify that the final destination table will be empty (as it is new), and you can run the process faster as you are only testing the bit that is failing.

  • Is there any way you could show the data flow task - with the source/destination and the column mappings?  Assuming that is the way you are moving the data from the work table to the final table.

    Without being able to see any code or the actual tasks - everything here is just a guess.  If you are using code to move the data - are all objects in that code schema qualified?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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