2

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Turner
  • 93
  • 1
  • 9
  • I think you need an OLE DB destination as the ODBC destination doesn't currently have an identity insert setting. – Dan Guzman Nov 12 '17 at 17:39

1 Answers1

0

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

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks Hadi - the solution was indeed to change the destination from ODBC to OLE DB. This worked and my data is flowing to where it should go now! Thanks. – Turner Nov 12 '17 at 20:11