SSIS - Auto increment field is not inserted correctly with data flow task

Question

I am trying to copy data from one database to another using ssis. I created the dtsx package with the SQL Server Import and Export Wizard. The table I am copying from has a column name "Id", the other table has name "ModuleCategoryId", which I mapped together.

ModuleCategoryId is the identity, and has an auto increment of 1. In the source database, the Id's are not ordered, and go like this:

• 32 Name1
• 14 Name2
• 7 Name3

After executing the data flow, the destination DB looks like this:

• 1 Name1
• 2 Name2
• 3 Name3

I have enabled identity insert in the wizard during the, but this doesn't do anything.

The destination database was made with Entity Framework, code first.

If I explicitly turn off ValueGeneratedOnAdd, and remake the destination database, the data is being transferred correctly, but I was wondering if there's a way to transfer all the data without turning off the auto increment, and then turning it back on.

If I manually set Identity Insert on for that table, I can insert rows with whatever ModuleCategoryId I want, so it must be something with the dataflow.

Answer 1

Table definitions are table definitions - regardless of the syntactic sugar ORM tools might overlay.

I created a source and destination table and populated the source to match your supplied data. I do define the identity property on the destination table as well. Whether that's what a ValueGeneratedOnAdd is implemented as in the API, I don't know but it almost has to be otherwise the Enable Identity Insert should fail (if the UI even allows it).

The IDENTITY property allows you to seed it with any initial value you want. For the taget table, I seed at the minimum value allowed for a signed integer so that if the identity insert doesn't work, the resulting values will look really "wrong"

DROP TABLE IF EXISTS dbo.SO_67370325_Source;
DROP TABLE IF EXISTS dbo.SO_67370325_Destination;

CREATE TABLE dbo.SO_67370325_Source
(
    Id int IDENTITY(1,1) NOT NULL
,   Name varchar(50)
);
CREATE TABLE dbo.SO_67370325_Destination
(
    ModuleCategoryId int IDENTITY(-2147483648,1) NOT NULL
,   Name varchar(50)
);

CREATE TABLE dbo.SO_67370325_Destination_noident
(
    ModuleCategoryId int NOT NULL
,   Name varchar(50)
);

SET IDENTITY_INSERT dbo.SO_67370325_Source ON;
INSERT INTO DBO.SO_67370325_Source
(
    Id
,   Name
)
VALUES
    (32, 'Name1')
,   (14, 'Name2')
,   (7, 'Name3');
SET IDENTITY_INSERT dbo.SO_67370325_Source OFF;

INSERT INTO dbo.SO_67370325_Source
(
    Name
)
OUTPUT Inserted.*
VALUES
(
    'Inserted naturally' -- Name - varchar(50)
);

Beyond your 3 supplied values, I added a fourth and if you run the supplied query, you'll see the generated ID is likely 33. Source table is created with an identity seeded at 1 but the explicit identity inserts on the source table advance the seed value to 32. Assuming no other activity occurs, next value would be 33 since our increment is 1.

All that said, I have 3 scenarios established. In the Import Export wizard, I checked the Identity Insert and mapped Id to ModuleCategoryId and ran the package.

ModuleCategoryId|Name
32|Name1
14|Name2
7|Name3
33|Inserted naturally

The data in the target table is identical to the source - as expected. At this point, the identity seed is sitting at 33 which I could verify with some DBCC check command I don't have handy.

The next case is taking the same package and unchecking the Identity Insert property. This becomes invalid as I'd get an error reporting

Failure inserting into the read-only column "ModuleCategoryId"

The only option is to unmap the Id to ModuleCategoryId. Assuming I loaded to the same table as before, I would see data something like this

ModuleCategoryId|Name
34|Name1
35|Name2
36|Name3
37|Inserted naturally

If I had never put a record into this table, then I'd get results like

ModuleCategoryId|Name
-2147483648|Name1
-2147483647|Name2
-2147483646|Name3
-2147483645|Inserted naturally

WITHOUT AN EXPLICIT ORDER BY ON MY SOURCE, THERE IS NO GUARANTEE OF RESULTS ORDERING. I fight this battle often. The SQL Engine has no obligation to return data in the primary key order or any other such order unless you explicitly ask for it. Had the following results been stored, it would be equally valid.

ModuleCategoryId|Name
34|Inserted naturally
35|Name1
36|Name2
37|Name3

If you have a requirement for data to be inserted into the target table based on the ascending values of Id in the source table, in the Import/Export wizard, you need to go to the screen where it asks whether you want to pick tables or write a query and choose the second option of query. Then you will write SELECT * FROM dbo.SO_67370325_Source ORDER BY Id; or whatever your source table is named.

The final test, loading SO_67370325_Destination_noident, demonstrates a table with no identity property defined. If I do not map Id to ModuleCategoryId, the package will fail as the column is defined as NOT NULL. When I map the Id to ModuleCategoryId, I will see the same results as the first (7,14,32,33) BUT, every subsequent insert to the target table will have to provide their own Id which may or may not align with what your FluentAPI stuff does.

Similar question/answer Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column