Today I am trying to figure out how to get rows with identity columns inserted into a Microsoft SQL 2016 database via an SSIS package that I am constructing using MVS 2015 (with SSDT 14.0.61709.290). I am pulling data from another data source (which works without issue) and I am inserting rows into a destination table that has been previously defined on the destination SQL server like so:
create table [DB_NAME].[dbo].[TableName]
(
key_value IDENTITY(1,1) primary key,
...other values...
)
GO
When I move values from the old data source to the new data source I get the error:
[MSQL Deal [70]] Error: Open Database Connectivity (ODBC) error occurred. state: '23000'. Native Error Code: 544. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF.
There are a tremendous number of forums and results that come up when searching that indicate that there should be a checkbox that permits identity inserts when modifying the column mappings page of the destination. This option does not exist and the "Advanced Editor" interface in MVS/SSDT 2015/2017 has column mappings only and no options for handling inserts for identity columns.
Also I have tried to add a step to my control flow that turns identity insert on, but for some reason enabling IDENTITY_INSERT at this level does not work and my package still fails on all insert attempts.
Now I am going to be completely honest, I am fully aware that I have alternative options to get this to work - but keep in mind I am building dev test and production databases that I am trying to keep scripted and automated and idiot proof for when it gets further down the line toward deployment. I don't want to have to introduce an intermediate step that forces one of our DBAs to wait for the first SSIS package to finish, run a SQL query that will enable identity inserts for a specific table, run the next package, then run a query to disable identity inserts. I would have to do this many times....
Did SSIS 2015 (and I tried this using MVS/SSDT 2017) completely drop support for identity inserts? Do I have to use a different interface with my DSN to get this to work (ODBC?)?
Is this still an option but it is hidden somewhere really really really well?
ODBC Destination has no option for identity insert, you can use an OLEDB Destination
instead if it, and it is contains a Keep Identity
check box, which can be used