1

I have this stored procedure:

Dbo.SprocName (@Id UNIQUEIDENTIFIER, 
               @ResponseCode INT OUTPUT,
               @ResponseDescription VARCHAR(500) OUTPUT)

And it returns a dataset called say Result as a nvarchar(MAX) (always a single row).

I've tried OLE and ADO connections and as well as result sets. I've tried creating a table variable and storing the value there.

Nothing works.

I can see in the database that it's running successfully then it fails when returning the result data set.

I’ve done some debugging and I can assure the result string is returned as should be. The problem is that I don’t know how to handle this on SSIS.

The error that I get is:

Input string was not in a correct format

I appreciate any ideas.

Thanks.

EDIT: I have tried using a table variable again and it works. I guess I didn't do it well first time. sorry about that. Thanks!

Maria
  • 363
  • 4
  • 13

1 Answers1

1

One potential cause for your problem could be a mismatch in data types between SSIS and SQL Server.

An SSIS GUID data type does not match a SQL Server uniqueidentifier - the SSIS GUID has curly braces (e.g., {00000000-0000-0000-0000-000000000000}), while the SQL value does not. SQL cannot recognize the value as a unique identifier, and fails to convert.

To pass down a GUID, you will need to remove those curly braces, either in SSIS or in SQL. One approach I've used it to send it across as a VARCHAR and then strip out the curly braces, e.g., 

DECLARE @GUID VARCHAR(40) = '{00000000-0000-0000-0000-000000000000}'
DECLARE @CnvtGUID UNIQUEIDENTIFIER = REPLACE(REPLACE(@GUID, '}', ''), '{', '')

SELECT @GUID, @CnvtGUID
AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • thanks for this but the procedure is returning nvarchar and the variable is string. I don't think the data types are the problem but the way to catch the data set. I've tried modifying the data types as well. – Maria Jun 06 '17 at 15:04
  • Are you passing in a value to the `@ID` parameter? That's the `uniqueidentifier` data type that prompted my suggestion – AHiggins Jun 06 '17 at 15:10
  • I see. The stored procedure was executing so the input parameteres were not the problem. I have used a table variable where I store my data set so it's fine now. Many thanks for your help. – Maria Jun 06 '17 at 15:25