Load multiple target table using same source table

How to load multiple target table using same source relational table

Step 1: Create target table list flat file and keep all the target table name in which you want to load data.

Step 2: use previously created target table list file as source definition for this mapping, create one input/output port table_name in flat file source definition.

Step 3: drag this source definition in mapping and create SQL transformation after source qualifier with default options, drag and drop port from SQ to SQL T/R.

Step 4: Edit SQL T/R and goto SQL setting tab and enable "Add statistic output port", it will create "NumRowsAffected" port in SQL T/R output port and this ports returns

Total number of rows affected by SQL statements for each input row.

Step 5: Now click on SQL ports tabs and click on SQL query edit box to insert data into different target tables

Click on String Substitution under port in this window and select your port table_name here and provide your query here

SQL query will be like

INSERT INTO ~table_name~ (col1, col2, col3...Coln) select (col1, col2, col3...coln) from source_table

~table_name~ this value will change based on outputreceived from flat file

Here you can't use update query, only insert statement along with select statement.

Step 6: Now we will create expression transformation for row level operations drag all ports from SQL T/R to expression T/R, here we will create

Another output port for target load time either you can use expression "sysdate" or "SESSIONSTARTTIME" for target load time update

In another output port will write in expression to figure out any sql error occured

IIF (ISNULL (SQL_error_input),'No Error', SQL_error_input) and connect all ports to target table SQL_Target_Load_audit.

Step 7: Now we will create one Audit table which will take care all your target load logs every time

And this table will be target for this mapping, if any load fails it will write SQL errors in SQL table

By doing this you can verify your target load time, if any SQL error and no of record processed in each run.