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'

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>