Enabling Explicit Pass-Through Processing for Other SQL Transformations :: SAS(R) Data Integration Studio 4.5: User's Guide

Problem

You want to enable explicit pass-through processing for a Create Table transformation, Delete transformation, Insert Rows transformation, or an Update transformation.

Solution

You should enable explicit pass-through processing when you connect a database management system table to a Create Table transformation, Delete transformation, Insert Rows transformation, or Update transformation. Keep in mind that the functions that are unique to a database management system are resolved only in the context of explicit pass-through processing. If you rely on implicit pass-through processing, you will receive an error when the job is executed. Perform the following tasks:

Tasks

Determine Whether Explicit Pass-Through Processing Is Possible

The Delete, Execute, Insert Rows, Merge, and Update transformations are particularly useful for tables that originate from database management systems including DB2 9.7, Oracle 11g, SQL Server 2005, and Teradata 13. The systems must support the following commands:

SQL Delete DML
SQL Merge DML
SQL Update DML
SQL Create DML
SQL Insert DML

In addition, Sybase (ASE/IQ) 12.5 supports non-SQL Merge transformations. Sybase 15.7 adds support for SQL Merge. The SQL Merge transformation does not support SAS tables. The Create Table, Delete, Execute, Insert Rows, and Update transformations do support SAS tables, but they might not support some functions such as sort.

Enable Explicit Pass-Through Processing

To enable explicit pass-through processing by default for new instances of most SQL transformations, select Tools

Options

Job Editor Tab, and then select the pass-through check box in the Automatic Settings area. This setting affects Join transformations and also any SQL transformation whose properties window includes a Database pass-through option on its Options tab. This includes SQL transformations such as Create Table, Insert Rows, Set Operators, Delete, and Update.

To enable explicit pass-through processing for individual transformations (Create Table, Insert Rows, Set Operators, Delete, and Update), open the properties window for the transformation and click the Options tab. Specify Yes for the Database pass-through option.