User-Defined Sql in Target Stage

[DS_SUPPORT]

I just created a test job, which will read from a CSV file and load it to a SQL Server target. I have used DRS as Target stage. First I have inserted all the records to the target by selecting "Insert rows without clearing".

Then I made the target update action as "User-Defined Sql' and i wrote my target sql as

Code: Select all

UPDATE tempdb.dbo.tbltst SET empname=:2 WHERE empid= :1;

This gives me error like

Code: Select all

MultJob..Transformer_14: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ':'.
MultJob..Transformer_14: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Then I changed my sql like

Code: Select all

UPDATE tempdb.dbo.tbltst SET empname=? WHERE empid= ?;

This executes fine without giving any error.
What is wrong in my first statement?

And in some other posts, it is mentioned we can dynamically give the column names to the user defined sql? how to acheive this functionality?

[WoMaWil]

I had such phenomens also years ago. I suceeded in switching the "where"-Part to then end.

[chulett]

Answer seems pretty obvious. The DRS stage does not support numbered bind variables. You need to stick with the positional (yuck) question mark variety. Besides, I thought only Oracle used the numbered ones?

As to your second question, carefully read the post you linked to. The question about 'dynamic column names' wasn't really answered. Sure, they said it 'works fine' but no explanation on how to do it and what explanation is there seems to be for something else to me. [shrug]

I'm not aware of any way to do that.

[DS_SUPPORT]

Any updates on Creating the Column name dynamically?

[ray.wurlod]

None. What you find with Search is all there is.