GUID gives Invalid character value for cast specification in Data Flow

Question

Good day,

I hope you are doing well in these strange times, jumping right into the issue:

I have created a Data Flow Task with two tasks in it. The first collects data based on a GUID, an OLE DB Source Task, and the other inserts this data in some other table.

When trying to collect the data the following error is fired:

Error: 0xC0202009 at Insert Doc Log Data, Get Doc Data [2]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E21  Description: "Invalid character value for cast specification".

The Data Source Task runs the following SQL Command:

SELECT        DocID, XML, FileHash, CompCode, CompName, VesselName, VesselID, ComponentID, CreatedOn
FROM            SLEP_XML_MP_PROD
WHERE        (DocID = CAST(? as uniqueidentifier))

Where the input on the question mark, ?, is given by the following: 

	Name	Value	Type
▶	User::CurDocID	{ADE3B511-0A3E-4DCD-AAF0-306FA38DB0FB}	String

In SQL Server itself it has no problem running the following, with or without the curly braces { and }:

SELECT        DocID, XML, FileHash, CompCode, CompName, VesselName, VesselID, ComponentID, CreatedOn
FROM            SLEP_XML_MP_PROD
WHERE        (DocID = CAST('ADE3B511-0A3E-4DCD-AAF0-306FA38DB0FB' as uniqueidentifier))

Normally outside a Data Flow I can specify the input data type, but inside I can't.

Is there someway without to much of a work around to fix this, e.g. I would like to refrain from using a variable to run the command as I find this quite cumbersome.

Thank you for your time and comments!

Answer 1

Hi Mick,

We can use Execute SQL Task in SSIS package.

SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.

Please refer to Execute SQL Task and Execute SQL Task in SSIS: Output Parameters vs Result Sets.

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 2

Hello Mona,

Thank you for your reply!

I am not using an Execute SQL Task, this due to the fact that i cannot reliable use two after each other to collect and insert Data. That's why I specifically chose to use a Data Flow Task.

Kind Regards,

Mick

Answer 3

Try changing your code to:

DECLARE @guid VARCHAR(100);
SET @guid = ?;

SELECT        DocID, XML, FileHash, CompCode, CompName, VesselName, VesselID, ComponentID, CreatedOn
FROM            SLEP_XML_MP_PROD
WHERE        (DocID = CAST(@guid as uniqueidentifier))

Answer 4

Hi

Set the data type of the field as DT_GUID .  Look the url may solve your problem

https://www.sqlshack.com/using-the-ssis-script-component-as-a-data-source/

Thanks and regards

Answer 5

Hello Tom,

Thank you for your reply. This small fix solved the issue I had!

Thank you and kind regards,

Mick