I recently replaced my hard drive and only installed Visual Studio 2022 this time. On my previous drive I had Visual Studio 2019 and 2022.
Back then I created an SSIS package and used the SQL Server Native Client 11.0. When I created a connection manager with that provider it had the option (among others) to 'Keep Identity', which was the same as running 'SET IDENTITY_INSERT ON' in SQL Server. (See image 1 below)
Now in VS 2022 the only Native OLE DB provider for SQL Server is simply called Microsoft OLE DB Provider for SQL Server (see image 2 below). However, it does not have the 'Keep Identity' options (nor the others, see image 3 below).
I tried running an Execute SQL statement prior to running the data flow task to enable the IDENTITY_INSERT, but it just does not work.
Any ideas on how to handle this?
Well I (sort of) figured this out. I downloaded and installed SQL Server native client 11 from here: https://www.microsoft.com/en-us/download/details.aspx?id=56041
When you click on the 'Download' button you will see a list of files to select. The file you want is 'sqlncli.msi'. There are two in the list. The smaller one is for 32-bit OS and the larger file is for 64-bit OS.
One thing that is peculiar is that I did not need to select 'SQL Server Native Client 11' in the Connection Manager within my SSIS package. Just having it installed caused the newer OLE Provider to have the option to 'Keep identity' when configuring my data flow.