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

SCD type-2 integrity constraint warning

Reply
Super Contributor
Posts: 625

SCD type-2 integrity constraint warning

I've DI job which loads data to the physical table via SCD type-2 transformation. When I ran the job I received an error like

 

"WARNING: Add/Update failed for data set INS.EMPLOYEE because data value(s) do not comply with integrity constraint PRIM_KEY. (Occurred 38 times.) NOTE: There were 38 observations read from the data set WORK.ETLS_MATCH.
NOTE: 0 observations added." 

 

Business key for my job is "Employee_ID" and there is no duplicates on Employee_ID and generated key is "Employee_RK". Could someone you help me understand the likely cause for this issue?

Super User
Posts: 23,354

Re: SCD type-2 integrity constraint warning

Doesn’t this mean the records you’re trying to insert, the 38 employee IDs, are already in the table?

 


@Babloo wrote:

I've DI job which loads data to the physical table via SCD type-2 transformation. When I ran the job I received an error like

 

"WARNING: Add/Update failed for data set INS.EMPLOYEE because data value(s) do not comply with integrity constraint PRIM_KEY. (Occurred 38 times.) NOTE: There were 38 observations read from the data set WORK.ETLS_MATCH.
NOTE: 0 observations added." 

 

Business key for my job is "Employee_ID" and there is no duplicates on Employee_ID and generated key is "Employee_RK". Could someone you help me understand the likely cause for this issue?


 

Super User
Posts: 5,853

Re: SCD type-2 integrity constraint warning

First: what is the definition of PRIM_KEY?
Next execute the job with SCD 2 Loader in debug mode, do that you can access the ETLS_MATCH table after execution.
Data never sleeps
Super Contributor
Posts: 625

Re: SCD type-2 integrity constraint warning

Could you please help me understand to know how to execute the SCD
transformation with debug mode?
Respected Advisor
Posts: 4,698

Re: SCD type-2 integrity constraint warning

@Babloo

The primary key on your target table is {Employee_RK, <Valid_From_Dttm>}? Right?

And the Valid_From_Dttm column (or however you call it) contains a SAS Datetime value?

Super User
Posts: 5,853

Re: SCD type-2 integrity constraint warning

You need to set the macro variable ETLS_DEBUG to something else than 0. Try that in the pre processing for the job, or add it manually in the generated code.
One option is to set this as default in your server definition. See here for more info:
http://documentation.sas.com/?docsetId=etlug&docsetTarget=p0ygtz7di8z8kpn1ufncd1ajt1pj.htm&docsetVer...
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 213 views
  • 3 likes
  • 4 in conversation