October 7, 2013 at 12:42 pm
We have a database named NorthStar (NS) that pushes data into a CRM Application (Salesforce) through an SSIS via a sql agent job. Lately, NS has been pushing duplicate data and Salesforce users are complaining of receiving duplicate data/records. Average amount received daily should be 156. Today 270 records were received. How do I solve this problem?
Here is the path to the package. --- J:\Projects\NS to SF\NS Contacts Update with Org\Weekly NS Push\Package.dtsx
Thanks!
October 7, 2013 at 1:30 pm
How is the data retrieved from the database? I guess with a query? Modify the query so that no duplicate data is retrieved.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 7, 2013 at 2:59 pm
Sounds like there is absolutely no error checking in the SSIS Package. You should have multiple cleansing and staging tables to work thru before sending the data to the Destination. Once the data is pumped into the a "FirstTable" some Verification and Cleansing should take place. Then if some duplicates are found, and pushed to an error table for later viewing by humans, then the good data can be inserted into the other database.
I always cleanse and verify all data. Never use the original table(s) where the data is being stored. If there is an error, like attempting to pass an incorrect data type, that data is moved off to an error table, and a notification is sent out. If there are no duplicates allowed to move, I move the rows that are duplicate into an error table, and again, notification is sent out.
But a SSIS package needs to have some steps to take care of things like this. I also use all stored procedures, just in case I need to modify one and add or remove some code. I can easily do that, without having to deploy the package and configuration all over again.
Andrew SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply