An issue occurs when your source and target tables for the Insert Rows transformation are in different database management systems (DBMS). In that scenario, SAS^® Data Integration Studio generates incorrect code to insert the rows in the target table. The query is generated using explicit SQL procedure pass-through code. For example, if you are inserting rows into an Oracle table and a Microsoft SQL Server table is your source table, the PROC SQL INSERT section of the code would be generated as follows:

The Microsoft SQL Server table reference is included in the code that is processed by the Oracle database. You might receive an error similar to the following:

The workaround is to use one of these methods to change the setting for the PROC SQL pass-through option:

• Set the SQL procedure pass-through option to No. You will find the option in the properties of the Insert Row transformation. After you open the properties of the transformation, select Options ► Database pass-through. 
• Deselect the Set Pass Through to yes for new SQL transformation check box. You will find this check box in Tools ► Options ► Job Editor.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Data Integration Studio 4.904 is available at: