Loading source flat file (without identity column) data in a SQL Server table with identity column: SSIS extract

0
votes

I am asked to load data from a flat file source without an identity column into a SQL Server table with an identity column (column also does not allow null).

Is there a way to do this using a SSIS package?

I have data flow task created, the only option while mapping destination column (identity ) is to ignore, and this fails the task while executing the package.

1
sqlsql-serverssisetl
You could use auto increment on the column or use a scripting task to generate a primary key. - Menno
I am trying to use auto increment but for some reason Default value in the properties is gray and I can't type NEWID(). Please let me know how you do this "auto increment" - apariyar
Auto increment would be set in the table structure, not as part of the package. - Menno
ok that's what I mean, but how do we do that? - apariyar
@MarekGrzenkowicz I thought so too. I have this, [VehicleRecordKey] [bigint] IDENTITY(1,1) NOT NULL, the while mapping I have ignored for VehicleRecordKey. the error is like this "Cannot insert the value NULL into column 'VehicleRecordKey', table 'FUP_ETL_DB.dbo.import_Vehicle'; column does not allow nulls. INSERT fails.". - apariyar

1 Answers

0
votes

I found the solution to my problem. This is my dentity column , [VehicleRecordKey] [bigint] IDENTITY(1,1) NOT NULL.

All I did to execute the package and load into the table without task failure was unchecked the Keep identity at OLE DB Destination Editor.

Thanks for commenting on the post everyone.