April 2, 2009 at 3:15 pm
Hello,
I have created a simple package Import/Export wizard, to move data from one db to the other.
I have selected the options to 1)Drop and Recreate Destination Table; 2)Delete existing Rows. While I realize "2)" should not be necessary b/c of "1)", I'm having issues with duplicate data.
Once the package succeeds, I do a row count, and then analyze individual records, and see exact duplicate records in the destiantion DB? Why?? The source, does NOT have duplicate rows. And, yes, I ran it a second and third time, adding duplicate records. If its dropping and recreating the table(s), shouldn't only one record be inserted (please answer)? It should be a 1-1 row migration, correct?
My second problem is, which may help eliminate the first, is that there are some Primary Keys that don't get migrated.
Is there a way to do this? What's the best method to copy the data, ensure no dupes, and insert primary keys?
Please advise.
April 2, 2009 at 7:15 pm
Your question seems to boil down to "why is SSIS creating duplicates in my data transfer?"
How can you expect anyone to answer this when you provide so little detail about how your package works?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2009 at 8:17 am
I can't tell you how much your reply helps! It must have taken you an awful long time to put together such an insightful answer. I'm not asking "why my SSIS package is creating duplicates", I wanted to know how to have it handle and discard duplicates, and thus I listed the options which I built my package using the Import/Export wizard. ANd my secondary question is do I need to modify my package in anyway and how. What other detail do you need to know, and I will provide it.
April 3, 2009 at 10:05 pm
If your source data does not contain dups, but your target data does, you should be asking why, in my opinion - and that was my assumption on reading your original post.
Surely your destination table has a PK which disallows the creation of pure dups - or do these dups have different PKs?
"... have it handle and discard duplicates" - you have stated that your source data does not contain dups, so adding any such measures would surely have no effect?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 6, 2009 at 8:56 am
Ok, thx Phil. I understand that.
That's what I was asking: How to enforce integrity and migrate the keys....Import/Export doesn't have this, but need to go into the SSIS BIDS. I set some options for this, modified my package and did 1 iteration - so far,so good.
April 6, 2009 at 10:50 am
Have you tried editing the package in VS to see whether you can make it include the options that you want?
I haven't checked, but I'm guessing that the wizard has used a 'Transfer SQL Server Objects' task to do the work. If you edit that task in VS, you can set options to copy keys, indexes etc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2009 at 1:37 am
If you have joins in your data selection query you may be producing duplicates that way, so check by running the query in management studio and note the number of rows.
Check in BIDS (aka Visual Studio) and ensure you have an execute sql task to truncate the destination table before the data flow task to clear the old data first.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply