September 29, 2009 at 1:54 pm
How to maintain the same identity column during the sql 2005 to sql 2008 migration? For example, the sql 2005 table has a identity column and suppose the value is 2252 and when you bulk load to sql 2008 or copy the database to sql 2008 the identity column values will be changed to supposed 2015. So please advise if I use the SSIS package to upload the table to sql 2008 how would I maintain the identity column value?
Thanks!
September 29, 2009 at 2:01 pm
In each of the 2005 --> 2008 migrations I have done, they involve BACKUP/OFFLINE/RESTORE methodology. However, you indicated you're doing bulk load.
I personally don't have experience in SSIS with this type of operation, but look for an option to "Keep Identity Values" or similar (as in DTS). I would use bcp, which would look like the following
bcp.exe targetdbname.dbo.tablename in c:\datafile.txt -S servername -T -r$$$ -t"|" -b 10000 -w -E
-E switch keeps original identity values (IDENTITY_INSERT_ON)
MJM
September 29, 2009 at 2:07 pm
Thank you so much!
I really want some one to help me with SSIS package.
September 29, 2009 at 4:36 pm
SSIS Ole DB Destination also has checkbox to keep identity values from the source. In general, this component has all features available in bcp.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply