SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Question related to Identity column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockstar283
Yak Posting Veteran

90 Posts

Posted - 06/06/2014 :  15:45:10  Show Profile  Reply with Quote
I have a SOURCE table as below in Prod:

SELECT '-1' as PKey, NULL as ID, 'Unknown' as Name
UNION
SELECT '1' as PKey, 01 as ID, 'ABC' as Name
UNION
SELECT '2' as PKey, 02 as ID, 'XYZ' as Name

I want to replicate (move) it to DEV environment. For this I have a Foreach loop container with following three tasks:



1) Identity_Insert ON: Checks if the table has any identity column or not, if Yes, then set it to ON (I have tested this and it works)

2) Script_task_1: This uses the following code to move the data from PROD to DEV

try{
string connectionString =
@"Data Source=Prod_Server;Initial Catalog=Source_DB;Integrated Security=SSPI;";
// get the source data
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
SqlCommand myCommand =
new SqlCommand("SELECT * FROM " + TableName, sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();

// open the destination data
string connectionString1 = @"Data Source=Dev_Server;Initial Catalog=Dest_DB;Integrated Security=SSPI;";

using (SqlConnection destinationConnection =
new SqlConnection(connectionString1))
{
// open the connection
destinationConnection.Open();

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 1000;
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(reader);
}
}
reader.Close();
//MessageBox.Show("Data copied successfully!!");
}
}
catch(Exception E){
Console.WriteLine(E.Message);
}

3) Identity_Insert OFF: Checks if the table has any identity column or not, if Yes, then set it to OFF

After this process..My destination table is looking like this:

SELECT '1' as PKey, NULL as ID, 'Unknown' as Name
UNION
SELECT '2' as PKey, 01 as ID, 'ABC' as Name
UNION
SELECT '3' as PKey, 02 as ID, 'XYZ' as Name

So, it is copying the data properly, but the Identity field for the root record is not replicating. instead of being -1, it is starting at 1.

Does anyone have an idea what am I doing wrong here?

rockstar283
Yak Posting Veteran

90 Posts

Posted - 06/06/2014 :  19:21:07  Show Profile  Reply with Quote
Solved it..
No need to set identity_insert ON and OFF before bulk copying.
SQLBulkCopyOptions.KeepIdentity takes care of this.
So, following code worked for me:

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString,SqlBulkCopyOptions.KeepIdentity))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000