August 5, 2009 at 3:33 am
Hi
i have created a variable and checked that it is working using a breakpoint.
In my next execute sql task, i want to update a field in a table with this value.
Tried to run this statement in the sqlstatement section:
UPDATE Peugeot_TPH_Phase1
SET RunNumber = ?
but it fails.
I have read that ssis doesn't read the parameter name into sql statements.
So i created another another variable (string this time), set evaluate expression to true.
in the expression section i put the following in:
"Update Peugeot_TPH_Phase1 set runnumber = " +@[User::RunNumber]
try to evaluate but i get the following error
The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".
any ideas on this?
thanks
p.s new to SSIS!
August 5, 2009 at 3:39 am
Try using & instead of + ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 5, 2009 at 3:43 am
just tried that and its saying:
The data type "DT_WSTR" cannot be used with binary operator "&".
August 5, 2009 at 3:52 am
"Update Peugeot_TPH_Phase1 set runnumber = " +@[User::RunNumber]
If @[User::RunNumber] is a numeric variable then you need to cast it into a string so that it can be concatenated on to the end of your query.
Try "Update Peugeot_TPH_Phase1 set runnumber = " + (DT_WSTR,10)@[User::RunNumber]
Even though you've had to use this method as a workaround, it can actually be a better way to dynamically control the SQL. By doing this you're able to view the entire statement at run-time & design time which is easier to debug, and also there are various SQL statements that can't use parameters such as subqueries and joins.
HTH
Kindest Regards,
Frank Bazan
August 5, 2009 at 4:01 am
that works- thank-you.
i am trying to get into a habit early on of using variables,parameters..hopefully better in the long run!
cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply