Problem Description: Incremental Pipeline Target export job to Sql server database will fail with the below exception in the job log.


ERROR] 2019-05-14 01:22:40,660 [pool-3-thread-1] infoworks.discovery.rdbms.helper.SQLServerHelper:660 :: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHEN'.


Root cause: 


This issue occurs if you select the same set of columns to export and also as natural keys as shown below.



When the export columns are same as the Natural Key columns, the below Merge query will be generated by Infoworks with an incorrect update clause.


MERGE "INFOWORKS_TEMP"."dbo"."PLACEMENT_XREF" orig_table  USING "INFOWORKS_TEMP"."staging"."TEMP_IW_PLACEMENT_XREF" new_table  ON "orig_table"."PSFT_ID" ="new_table"."PSFT_ID"  AND "orig_table"."SFDC_ID" ="new_table"."SFDC_ID"  WHEN MATCHED THEN UPDATE SET  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES ("new_table"."SFDC_ID"  , "new_table"."SFDC_ID" );


This is a limitation in Infoworks ADE (2.6.x) and there is already a jira (IPD-7505) raised to address this issue in future releases.



Solution:


a) Set ZIW_ROW_ID as the natural key. ZIW_ROW_ID will be unique for each record and is generated with natural key values for each record.

b) Set the Export type to Full export and run the job (This will delete the old SQL server target table and will create a new one with ZIW_ROW_ID as Natural key)

c) From the next incremental runs, set the Export Type to Incremental Export and run the jobs.

Please go through the below article for more information on export to sql server database.

https://docs.infoworks.io/product-documentation-2.7.0/sql-server-export


Applied to versions:


IWX ADE 2.6.x,2.7.x