SQL error when casting guid to string

0

I am working with SSIS packages, and I use OLE DB Source component with following SQL query:

DECLARE @ConnectedGUIDS nvarchar(200);

SELECT @ConnectedGUIDS = COALESCE(@ConnectedGUIDS + ',', '') + convert(nvarchar(36), Record2Id)
FROM ConnectionBase 
WHERE Record1Id = CAST(? AS CHAR(36));

SELECT DISTINCT(Record1Id) AS ContractGUID,  
       @ConnectedGUIDS AS ConnectedGUIDs 
FROM ConnectionBase 
WHERE Record1Id = CAST(? AS CHAR(36));

When I try running package, it fails in pre-execute phase, with following error:

Error: 0xC0202009 at Update connected contracts in Stage DB, Get connected Contracts from CRM DB [5]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Conversion failed when converting from a character string to uniqueidentifier.". Error: 0xC004701A at Update connected contracts in Stage DB, SSIS.Pipeline: Get connected Contracts from CRM DB failed the pre-execute phase and returned error code 0x80040E14.

These are the types I have:

  • ? is parameter mapped in loop container and it is String representation of GUID
  • Record1Id is uniqueidentifier
  • Record2Id is uniqueidentifier

If I replace

CAST(? AS CHAR(36))

with

CAST('9C5D6FA0-4EBD-41F6-9D31-FB62A885CEC6' AS CHAR(36))

I can run same query in SQL Server Management Studio without any problems. I have same exact query in another SSIS package and its not causing any problems.

What am I doing wrong?

sql-server
ssis
sql-server-2012
asked on Stack Overflow Oct 19, 2016 by azec-pdx • edited Oct 20, 2016 by marc_s

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0