Importing Identity field values to SqlServer 2005
-
When I try to load the data from Sql Server 2000 to Sql Server 2005 using SSIS but i dont see any error , when i check the data for the Identity column fields .. its loaded with the new seeds instead of the existing seeds , is there any way to migrate the same seeds from source to destination ........
Thanks in advance for the suggestions.........
-
You need Identity Insert set to ON.
The best way to do this is to insert two Execute SQL Tasks, one before the import and one after the import. The first one should be the "Set Identity_Insert MyTable ON", the second should be the "Set Identity_Insert MyTable OFF". Once this is done, verify that you've included the Identity values from your source table in the import / data flow process. After that, you should be done and everything should be working fine.
Note: I_I can only be "on" for one table at a time in the database & the session, so you want to make sure you turn it off as soon as you're done with it in case another process needs it.
-
Thanks for the suggestion but I have tried with that also but still the migrated values seeds are different to the source seeds....
-
Is this field truly a plain vanilla Identity column? Does the destination table have two different columns, one pulling the Ident from the source and the other its own Ident column which is similarly named? Or are you trying to pull in GUIDs (unique row identifier records)?
-
Its truly a plain Identity Column... and even the destination table has the columns which has the Source..... only difference i am finding is the Identity Column values.... i.e. not able to import the source Identity Column values to the destination using ssis... here i am using the "Transfer Sql Server Objects Tasks" (TSSOT ) the properties used for this Task are,
Objects -> Destination ->
DropObjectsFirst --> True
CopyData --> True
ExistingData -->Replace
Objects -> DestinationCopyOptions ->
ObjectToCopy --> selected only one table which has the Identity Column
Objects -> Connections ->
SourceConnection -> Sqlserver 2000 server name
DestinationConnection -> Sqlserver 2005 server name
SourceDatabase & DestinationDatabase -- > given the appropriate db names.
Note : before to this task i have added one more "Execute T-Sql Statement Task" (ETSST ) which will do the "Identity insert on " and after the TSSOT i have added one more ETSST to "Identity insert off" .... but still i am not able to migrate the same data...
Here while doing this process i am not getting any error but the data is wrong.... i.e. the data is coming with the new seeds not the source seeds.
Subra
-
Are you just doing a data transfer? No triggers, etc. If so, maybe what you need to do is resort to an Execute SQL Task and stick a SELECT statement in there rather than use the Transfer SQL Objects Task.
-
Ya i am doing only Data Transfer ... but as you mentioned using the "Execute SQL Task" i dont see any where source connection and destination connection... so how can i use this Task to make it work...... ( bcause i see only one connection type not the two connection types )..
-
You are correct. I mis-spoke.
Try this: Pull over a Data Flow Task. Double-click it or Click the Data Flow Tab and arrow down to the Data Flow Task if it's not already listed at the top to get into the proper namespace. In the Data Flow Tab, add two OLE DB Connectors. Open the first and your Connection Manager is the very first thing on the list. Click NEW and create the connection to the Source DB (SQL 2000). Then choose "SQL Command" as the Data Access Mode and put the query in the box that will show up below.
Open the second OLE DB and create a new Connection Manager pointing to the destination db (SQL 2k5). The data access for this will be Table or View Fast Load. Tell it the source table in the line below that.
Add a precendence flow from the first OLE DB to the second, then reopen the first one to make sure your columns are properly mapped (if you haven't already). It's under Columns. Double-check Mappings on the second OLE DB to make sure everything is kosher on both sides of the conversion.
I don't know if the setting of Identity Insert will work within the first OLE DB connector or not. You can try it and if it doesn't, just add the Execute SQL Tasks before and after the Data Flow Tasks with precendence constraints (Precedence is VERY important for this to work correctly for you).
Let me know if that doesn't work.
-
Thanks a lot it works COOL...........
finally i am able to migrate the data ..... -
Glad I could help. @=) And my apologies for the initial mis-direction.
-
Thanks again.... is there any other way to do this little bit simple bcause in Sql Server 2000 there we have a "Transform Data Task" where we do this task very simple like that is there anything in Sql Server 2005?
-
Simpler way to do this? No. SSIS is capable of doing so much more than DTS that the this task has become a bit more complicated rather than being streamlined.
However, if you click on the Data Flow tab and look at the Toolbox, you'll notice there are any number of Data Transformation tasks you can add into the Data Flow (and it has to be here, not in the Control Flow Tab) to manage your data transformations.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply