Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2016 8:31 AM
Points: 7, Visits: 290
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

Post #1502311
Posted Monday, October 7, 2013 1:30 PM



Group: General Forum Members
Last Login: Friday, June 3, 2016 7:09 AM
Points: 15,149, Visits: 13,037
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 SQLKover.

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

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 17, 2015 7:48 AM
Points: 1,002, Visits: 3,420
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.

Post #1502383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse