June 23, 2021 at 12:24 pm
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.
Regards,
Steve
June 23, 2021 at 2:36 pm
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?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2021 at 3:17 pm
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,
Steve
June 23, 2021 at 6:03 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2021 at 6:17 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply