SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

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

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:

table loader mapping.PNG

 

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.


Accepted Solutions
Solution
‎08-21-2017 06:18 AM
Super User
Posts: 5,849

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

Posted in reply to imdickson

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).

Data never sleeps

View solution in original post


All Replies
Solution
‎08-21-2017 06:18 AM
Super User
Posts: 5,849

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

Posted in reply to imdickson

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).

Data never sleeps
Frequent Contributor
Posts: 92

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

Added that statement in the library configuration. Thanks alot
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 191 views
  • 0 likes
  • 2 in conversation