May 13, 2010 at 12:58 am
Hi everyone,
I have a Sequence Container in a SSIS package that checks for duplicate records in a database table, and sends an email if any are found. The first task in the container runs a Data Flow Task that runs a query to find any duplicates, and write the results to a text file. There is a Row Count Transformation in the task, that writes the row count of the query to a variable called "intDupCount," which I have assigned data type UInt32.
The second task in the Sequence Container is an Execute SQL Task, running a stored procedure that is supposed to accept the value of intDupCount as a parameter. In the stored procedure, I am accepting this parameter as data type NVARCHAR, and the Parameter Mapping in the Execute SQL Task is also defined as NVARCHAR.
I can run the stored procedure in SSMS successfully. However, when I try to run the Sequence Container task in the package, the Execute SQL Task fails with error "Error converting datatype nvarchar to numeric." If I try to pass the intDupCount parameter as a numeric, the task fails with error "Unspecified error." :crazy:
May 13, 2010 at 5:34 am
What type of connection you are using in connection manager?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 13, 2010 at 11:35 am
Please check this link below with a similar problem.. i would suggest you to use Variable Input as Input Type in the Execute SQL task and use Record set destination to store your data rather than passing parameters directly as it is more flexible
http://www.sqlservercentral.com/Forums/Topic916145-148-1.aspx#bm917666"> http://www.sqlservercentral.com/Forums/Topic916145-148-1.aspx#bm917666
May 13, 2010 at 7:44 pm
That post did help.
I'm not using the parameter mappings tab at all now - I'm building the SQL statement as an expression with the variable values sent directly to SQL rather than mapping parameters.
So I have a SQLStatementSource expresssion:
"EXEC dbo.usp_exec_Duplicates_Email " + (DT_WSTR,4)@[User::intDupCount] + ", '" + @[User::strDup_To] + "','" + @[User::strDup_CC] + "','" + @[User::strDupFile] + "'"
and it works fine. I've even had the email. I'm even thinking this might be a better way of passing parameters all round in Execute SQL Tasks, because I hate hate HATE the parameter mappings tab
(to anyone else that gets this, you need to convert the integer value to a string in the expression, as the expression is a text string. But you don't need to wrap it in '', because it's an integer and SQL needs to interpret it as an integer.)
May 14, 2010 at 8:54 am
Nice to see that it helped.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply