2

I have an Execute SQL Task which tries to execute a stored procedure, like this:

EXEC usp_stored_proc ?, ?, ? OUTPUT, ? OUTPUT;

I have 4 variables mapped to parameters. Ignoring the output parameters, these are both strings mapped to NVARCHAR params (as expected by the stored procedure).

When I run the package, an error tells me that execution failed with the message input string is not in the correct format. However, when I use a breakpoint to find the runtime values of the input parameters (or at least the variables mapped to them) and execute the same line of SQL in SSMS using the runtime values, it works fine.

Can anyone help? I'm at the end of my tether with this. I can't even find out the exact parameter causing the issue although it's probably both as the values follow the same format.

More details:

  • Connection type: OLE DB
  • Input Variable: String = schema.table
  • Mapped Param: NVARCHAR, ParamName = 0, ParamSize = -1

UPDATE

Solved the issue by making a new execute sql component that calls a stripped down procedure. I then slowly added lines of code to the procedure and additional parameters until arriving at the same component I started with and now it works. Comparing the original and rebuilt tasks, I see absolutely no differences (same with the procedure), so I don't know why this issue was occuring.

TurgidWizard
  • 619
  • 1
  • 7
  • 22

2 Answers2

0

Try changing the parameter size (ParamSize) to match the parameter size within the stored procedure; if nvarchar(50) then set it to 50.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • thanks for the reply but it didn't work, same message :( I can pass params into a stripped down procedure so im building it up slowly trying to find the point at which errors occur – TurgidWizard Mar 30 '20 at 21:30
  • I can't copy and paste it but it was "Failure executing sql command XYZ... with error message input string in incorrect format" or equivalent. Issue fixed now but without explanation (see edits) – TurgidWizard Mar 31 '20 at 09:30
0

Solved the issue by making a new execute sql component that calls a stripped down procedure. I then slowly added lines of code to the procedure and additional parameters until arriving at the same component I started with and now it works. Comparing the original and rebuilt tasks, I see absolutely no differences (same with the procedure), so I don't know why this issue was occurring.

TurgidWizard
  • 619
  • 1
  • 7
  • 22