Post reply

Problem maintaining identity columns on SSIS import wizard

  • 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?

  • July 22, 2006 at 10:34 am

    #650925

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

  • July 26, 2006 at 7:34 am

    #651616

    Thank you, sir! That did the trick.

    I must say that behavior is VERY counter intuitive.

  • July 26, 2006 at 8:32 am

    #651643

    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.

  • July 26, 2006 at 4:03 pm

    #651765

    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

  • July 27, 2006 at 7:27 am

    #651873

    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

  • July 27, 2006 at 11:41 am

    #651964

    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

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

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