Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duplicate Data Reports Issue Expand / Collapse
Author
Message
Posted Monday, October 07, 2013 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 8:31 AM
Points: 7, Visits: 156
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!
Post #1502311
Posted Monday, October 07, 2013 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 12,201, Visits: 9,156
How is the data retrieved from the database? I guess with a query? Modify the query so that no duplicate data is retrieved.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1502333
Posted Monday, October 07, 2013 2:59 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 958, Visits: 3,257
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
Post #1502383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse