Convert string variable to GUID

Question

I want to use an Execute SQL Task query to return varbinary data from a database table. The query requires a UniqueIdentifier as a parameter which is stored as string in a Package variable. The result of this query (the varbinary data) will be stored in a second variable of type Byte.

The table below shows my list of local variables, note that DocumentGuid is pre-populated by another part of control flow

| Variable Name | Qualified Name     | Scope   | Data Type | Comments                                 |
|---------------|--------------------|---------|-----------|------------------------------------------|
| DocumentGuid  | User::DocumentGuid | Package | String    | Used to store a GUID value               |
| DocumentData  | User::DocumentData | Package | Byte      | Used to hold varbinary data from a query |

When I try to use this in a Execute SQL Task query like this:

SELECT DocData 
FROM  docsRepo
WHERE DocumentGuid = ?

Passing in the parameters as

| Variable name      | Direction | Data Type | Parameter Name | Parameter Size |
|--------------------|-----------|-----------|----------------|----------------|
| User::DocumentGuid | Input     | GUID      | 0              | 100            |

And Result Set

| Result Name | Variable Name      |
|-------------|--------------------|
| DocData    | User::DocumentData |

I get the following error:

[Execute SQL Task] Error: Executing the query "SELECT DocData FROM
dbo.docsRepo..." failed with the following error: "Conversion failed when converting from a character string to uniqueidentifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Am I missing some kind of fundamental logic here? The following script works just fine in SQL Server:

SELECT DocData 
FROM  docsRepo
WHERE DocumentGuid = '53A394A7-5D2B-40C0-A04D-90553E4535C3'

Baerl

Change the parameter type to be string. I know, it's a guid but since it's a string variable, keep the data type alignment and let the backend rdbms handle the conversion - does that work? – Tropine

@billinkc's got it. The error message isn't coming from the query execution, it's coming from the Execute SQL Task trying to pick up a string parameter and convert it to a GUID. SSIS can't do that, so it's choking before you ever get to the query inside the box. – Decimalize

Answer 1

I made two changes to your sample and I was able to get it to work.

The first was change the parameter type to be string in the Execute SQL Task. I know, it's a guid but since it's a string variable in SSIS, keep the data type alignment and let the backend rdbms/driver handle the conversion.

The other thing I changed was the data type for DocData. You defined as Byte but having never used that, I worry it is one whole byte, not bytes. At any rate, for the table I created, using the Object data type for SSIS made it work.

Source query

Here's my table and a single value in it

IF NOT EXISTS
(
    SELECT
        *
    FROM
        sys.tables
    WHERE
        name = 'docsRepo'
)
BEGIN
    CREATE TABLE dbo.docsRepo
    (
        DocumentGuid uniqueidentifier
    ,   DocumentData varbinary(MAX)
    );

    INSERT INTO
        dbo.docsRepo
    SELECT
        '53A394A7-5D2B-40C0-A04D-90553E4535C3'
    ,   CAST('Hello world' AS varbinary(MAX));
END;

Retrieval query

SELECT D.DocumentData FROM dbo.docsRepo AS D WHERE D.DocumentGuid = ?;

Configured with Full Resultset. Used an OLE DB Driver. Parameter name 0, data type varchar, variable User::DocumentGuid. Results tab, I have a result set name of 0, variable User::DocumentData

This Biml will create an SSIS package that demonstrates all of this

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="localhost" ConnectionString="Provider=SQLNCLI11;Integrated Security=SSPI;Data Source=.\dev2014;Initial Catalog=tempdb" />
    </Connections>
    <Packages>
        <Package Name="SO_50028154" ConstraintMode="Linear">
            <Variables>
                <Variable Name="DocumentGuid" DataType="String">53A394A7-5D2B-40C0-A04D-90553E4535C3</Variable>
                <Variable Name="DocumentData" DataType="Object" />
            </Variables>
            <Tasks>
                <ExecuteSQL Name="SQL GenerateTable" ConnectionName="localhost">
                    <DirectInput>IF NOT EXISTS(SELECT * FROM sys.tables WHERE name='docsRepo')BEGIN CREATE TABLE dbo.docsRepo(DocumentGuid uniqueidentifier, DocumentData varbinary(max)); INSERT INTO dbo.docsRepo SELECT '53A394A7-5D2B-40C0-A04D-90553E4535C3', CAST('Hello world' AS varbinary(max)); END</DirectInput>
                </ExecuteSQL>
                <ExecuteSQL Name="SQL Retrieve data" ConnectionName="localhost" ResultSet="Full">
                    <DirectInput>SELECT D.DocumentData FROM dbo.docsRepo AS D WHERE D.DocumentGuid = ?;</DirectInput>
                    <Parameters>
                        <Parameter DataType="AnsiString" Name="0" VariableName="User.DocumentGuid" />
                    </Parameters>
                    <Results>
                        <Result Name="0" VariableName="User.DocumentData" />
                    </Results>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Tropine

Thanks for the thorough explanation @billinkc. It turns out that there were a few mistakes I made around the variable data types/parameters and results mappings. Much appreciated, what you provided was absolutely correct! – Baerl

Answer 2

You could try explicit CAST:

SELECT DocData 
FROM  docsRepo
WHERE DocumentGuid = CAST(? AS UNIQUEIDENTIFIER);

Janeanjaneczka

Can you post your entire query? – Lauralauraceous

Thanks @lad2025 I'll give this a try – Baerl

Thanks for the explanation. It turns out using Billnkc's explanation that SQL can actually handle the conversion itself rather than trying to explicitly cast it to a specific type. However this is a very useful technique for future use. Many thanks for the help – Baerl

Answer 3

Look like you may have values stored in DocumentGuid which are not valid GUIDs, or invalid GUIDs you're passing to the query

To find if your table contains invalid GUIDs, you can run:

SELECT DocumentGuid
FROM  docsRepo
WHERE TRY_CAST(DocumentGuid AS UNIQUEIDENTIFIER) IS NULL

Charlenacharlene

Answer 4

when you want to send your uniqueIdentifer as varchar to your Execute SQL Task, you can use this query in your Execute SQL Task:

DECLARE @docGuid AS UniqueIdentifier 
SET @docGuid  = CAST ( '{' + ? + '}' AS UniqueIdentifier )

SELECT DocData 
FROM  docsRepo
WHERE DocumentGuid = @docGuid

the ? send by parameter as varchar with size 50

Graehl