locked
GUID gives Invalid character value for cast specification in Data Flow RRS feed

  • 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!

    Wednesday, July 29, 2020 8:01 AM

Answers

  • 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))


    Wednesday, July 29, 2020 12:07 PM

All replies

  • 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 MSDNFSF@microsoft.com

    Wednesday, July 29, 2020 9:14 AM
  • 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

    Wednesday, July 29, 2020 11:12 AM
  • 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))


    Wednesday, July 29, 2020 12:07 PM
  • 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

    Wednesday, July 29, 2020 12:24 PM
  • Hello Tom,

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

    Thank you and kind regards,


    Mick

    Wednesday, July 29, 2020 12:52 PM