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 GUIDRecord1Id
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?
User contributions licensed under CC BY-SA 3.0