Problem maintaining identity columns on SSIS import wizard

Question

Post reply

Problem maintaining identity columns on SSIS import wizard

Scott Bernardi

SSChasing Mays

Points: 630
More actions
July 22, 2006 at 9:22 am

#114809

I have a small database with about 2 dozen tables hosted on a externl webhost. Occassionally I need to get a copy of the whole database into a local sqlexpress instance so I can do development and debugging with actual data. I used the SSIS import wizard to copy all the tables from the production database to my sqlexpress instance, and for each table I checked the Delete data and Enable identity insert checkboxes.
The import runs fine, I get data, however, it goes in and renumbers all my identity columns starting from 1 instead of copying the value from the production database. This of course makes the data worthless. I never has this problem in SQL Server 2000 Enterprise Manager using DTS. Anybody have a clue what I am doing wrong?
Second question - how the heck to you change a user password in SS Management Studio?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Do you have the optimize for many tables option on, if so check this out: http://blogs.msdn.com/chrissk/default.aspx

Scott Bernardi SSChasing Mays Points: 630 More actions · Answer 2

Thank you, sir! That did the trick.

I must say that behavior is VERY counter intuitive.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 3

Just a few of the querks that I hope get ironed out in the next SP, also watch out for the querk that does not delete old backups from maintenance plans. The plan ignores the the . in .bak, .lgf etc. So when stating what suffix a file has that you wnat deleted you need to put in bak, lgf or it does not delete them. Spent a quite a bit of time figuring why my drives where filling up.

evaleah Ten Centuries Points: 1372 More actions · Answer 4

This did not work for me. I never clicked that checkbox and I still lost all my identity values. In addition, I don't really want to create the whole thing again with the wizard as I have 160 tables I am filling.

Does anyone have a good suggestion of how to fix this with the saved package as it exists?

Thanks!

Eva

asher c Grasshopper Points: 18 More actions · Answer 5

Eva,

I ran into the same issue and found 2 solutions around the problem. Firstly instead of using the Copy Database Wizard, Backup and restore the database you need to copy. This will get you your Database as is. Secondly, If possible install SP1 for SQL Server 2005. This is a known issue and Microsoft has fixed it in SP1.

Good Luck!

Ash

evaleah Ten Centuries Points: 1372 More actions · Answer 6

Ash,

Thanks. I should have known to look for a service pack but I was just so frustrated by that point I wasn't thinking. That saved the day in WAY more ways than just this one.

Thanks again,

Eva