SSIS Execute SQL Task with String Output Parameter problem, using ADO.NET

• steve lord

  SSC Veteran

  Points: 258

  More actions

  May 2, 2013 at 10:04 am

  #293419

  Greetings ... SSIS newb here.

  I have an Execute SQL task using ado.net that successfully uses an output parameter that is an integer. What I really want, however, is for the output parameter to be a string. I've been banging my head against the wall for a couple of days on this now. I'm using VS 2008, sp1 against SQL Server 2005.

  However, when I change the (1) parameter in the sproc to a varchar(250) and (2) in the SSIS variable to a string, defaulting the value to MyOutput, and (3) changing the datatype from Int32 to String in the Execute SQL Task Editor Parameter Mapping I get an error that's not too terribly informative:

  "A severe error occurred on the current command. The results, if any, should be discarded.

  A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  For testing, I've set the string output to a constant making sure there's no issues with Null values. Any ideas about where I've gone wrong? Again, works fine when the output is an INT -- the STRING is giving me fits. I'd really prefer not to use OLE_DB. I know that I could always use a resultset but it's now a personal mission to make this work! 🙂

  Thanks so much for any thoughts or ideas.

  -Steve