June 9, 2006 at 7:10 am
I've got a small production database in SQL Server 2005, and a couple of development machines, one running SQL Sever 2005 and the other SQL Sever 2000. Occassionally to do some debugging I want to download the whole production database into my dev machine. I created an SSIS package using the import wizard to copy all tables, and marked each only to delete first and checked Enable Identity insert.
My customer file has a CUSTID identity field which is the primary key. In the production database, it starts at custid 64.
When it imported the data, it renumbered the custid's starting at 1 - it did not insert the existing custid's, which screwed things up horribly.
In contrast, I used the other machine and created a DTS package in SQL Server 2000 to import the data with the same settings - delete rows from destination table, and Enable Identity Insert. And it kept the existing custid's fine.
So what gives with SSIS? If there was some other setting I missed, it sure isn't an obvious one.
June 12, 2006 at 8:00 am
This was removed by the editor as SPAM
June 29, 2006 at 8:59 am
I would be interested to see the answer to this question. Is this a bug or by design? Thanks.
September 27, 2006 at 8:24 am
We've seen this as well and we are running sp1. I'd be interested in seeing people's comments on this.
September 27, 2006 at 10:00 am
Somebody replied to me in another post. You can't use the "Optimize for many tables" option if you are going to keep identity inserts. Nowhere are you warned that using this option will cause Keep Identity Inserts to not work. Inituitive, isn't it?
I got my SSIS package to run fine once I turned of the Optimize for many tables option.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply