December 23, 2009 at 8:48 am
I pass a Variable Expression to an Execute SQL Task.
The variable name is SQLInsert and its datatype is String
The expression of this variable is:
"Insert TABLE (FileName, Created, Modified, Hash, Processed)
SELECT " + "'" + @[User::FileName] + "'" + ", GETDATE() , GETDATE() , (upper(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', " + "'" + @[User::FileName] + "'" + ")), 3, 32))) , 1"
The process works fine and insert the data into the table.
I need to modify my Expression because in my process I found that I need either Insert or Update the data in my table. So I have another variable that carry the value of 0 or 1 to know if the data to process has to be inserted or updated. I modified the Variable Expression as:
"If "+ @[User::NonProcessed] +" = 0
Begin
Insert Table (FileName, Created, Modified, Hash, Processed)
SELECT " + "'" + @[User::FileName] + "'" + ", GETDATE() , GETDATE() , (upper(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', " + "'" + @[User::FileName] + "'" + ")), 3, 32))) , 1
END
ELSE
BEGIN
Update Table set Processed = 1 where [FileName] = " + "'" + @[User::FileName] + "'" + "
END"
Note: @[User::NonProcessed] is declared as integer in my variable list.
This is the error:
The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Attempt to set the result type of binary operation ""
If " + @[User::NonProcessed]" failed with error code 0xC0047080.
I tried to modify the data type or change the way to read the value. But i just couldt find the right way.
Any advice?
December 23, 2009 at 9:09 am
I got the Solution:
"If " + (DT_WSTR, 1)@[User::NonProcessed] + " = 0
Begin
Insert ActiveSetTopBoxFile (FileName, Created, Modified, Hash, Processed)
SELECT " + "'" + @[User::stbinfoFileName] + "'" + ", GETDATE() , GETDATE() , (upper(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', " + "'" + @[User::stbinfoFileName] + "'" + ")), 3, 32))) , 1
END
ELSE
BEGIN
Update ActiveSetTopBoxFile set Processed = 1 where [FileName] = " + "'" + @[User::stbinfoFileName] + "'" + "
END"
Regards,
Merry Xmas
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply