Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio

imdickson — Mon, 14 Aug 2017 10:14:33 GMT

Dear everyone.

I am building ETL jobs for a project along with my seniors with SAS Data Integration Studio. Recently, i have updated the entire MS SQL database with new primary key. Before i edit the database, all tables have a column "ID" which is identity column which also remains until now.

Here is the problem, all my Loading jobs are using table loader for Update/Insert. It has always been working fine until the recent MS SQL database change. Now, I am getting this error :

 ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'ACTIVITY_HIGHLIGHT' when IDENTITY_INSERT is set to OFF.

Here is the screenshot of the table loader mapping:

ID is not mapped as it is set to identity column in MS SQL.

I have tried to switch the option of SQL Pass thru to on and off and im still getting this same error.

Do take note that I do not map to the ID column in any way.

I do understand that sometimes my information posted is not sufficient enough for your understanding. Hence, I appreciate if you could ask me for more particular info that you need to further analyze.

I would like to thanks everyone here for reading my post and hopefully this problem will be resolved soon.

Re: Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio

LinusH — Mon, 14 Aug 2017 11:07:25 GMT

I think that there have been a couple of threads with similar issues.

As I recall it I have solved this by setting the option IGNORE_READ_ONLY_COLUMNS=YES (works both as a libname and as a data set option).

Re: Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio

imdickson — Mon, 21 Aug 2017 10:19:16 GMT

Added that statement in the library configuration. Thanks alot

topic Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio in SAS Data Management

Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio

Re: Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio

Re: Hitting error of Identity Column with Table Loader when using SAS Data Integration Studio