User-Defined Sql in Target Stage
Posted: Wed Apr 25, 2007 5:37 am
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
This gives me error like
Then I changed my sql like
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?
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;
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.
Code: Select all
UPDATE tempdb.dbo.tbltst SET empname=? WHERE empid= ?;
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?