Fun with SSIS and {GUID’s}

Task

Create a SQL Server Integration Services (SSIS) package that accepts a Globally Unique Identifier (GUID) as a variable and uses this variable as:

Problem

Although SSIS supports the GUID data type (DT_GUID) and although you can use a Data Conversion Transformation to transform a string representation of GUID into the DT_GUID data type, there is no GUID data type that can be assigned to SSIS variables.

“Not a problem” I hear you say, “Just use a STRING data type and SQL Server will implicitly convert it to a GUID”“You wish!” says SSIS

Solution

This should have been an easy solution to implement so I can’t believe I wasted an hour trying to get things working. So what caused all my pain? SSIS’s {perceived}lack of support for GUID’s variables.

Steps to implement solution:

1. Create a SSIS variable of type STRING

2. Add an Execute SQL Task to execute a SQL Server stored procedure using the SSIS variable as a parameter

3. Add a Data Flow Task

4. Add an OLE DB Source with a Data Access Mode of SQL Command and a SQL Command Text which uses the SSIS variable as a parameter

5. Add an OLE DB Destination to pipe the result from the OLE DB Source into a SQL Server table.

6. Run the package

7. Debugging

Step 1 – Create SSIS Variable

MSDN Link: How to: Add or Delete a Variable in a Package by Using the Variables Window

Here I’ve created a variable called ProjectUID of type STRING and assigned it a default value to assist with debugging.

image

Step 2 – Create Execute SQL Task

MSDN Link: Working with Parameters and Return Codes in the Execute SQL Task

You can see from the first image below that stored procedure usp_DeleteEMPImportTablesByProjectUID accepts a parameter called @ProjectUID (of data type UNIQUEIDENTIFIER) and it’s value has been set to “?”.  This tells the Execute SQL Task that a parameter mapping is being used to provide the value.

The second image shows how to map the SSIS variable created in step 1 to the @ProjectUID parameter

The connection manager created for this task is using the Native OLE DB\SQL Native Client provider.

image

image

Step 3 – Create Data Flow Task

MSND Link: Adding a Data Flow Task

Nothing add here

image

Step 4 – Add an OLE DB Source

MSDN Link: How to: Extract Data by Using the OLE DB Source

MSDN Link: How to: Map Query Parameters to Variables in a Data Flow Component

Here I add an OLE DB Source and set its Data Access Mode to SQL Command.  I have also added my SQL Statement which filters results by ProjectUID.  As with the Execute SQL Task, the “?” means that a parameter mapping is being used to provide the value.

Clicking on the Parameters button launches the Set Query Parameter dialog (image 2) where the SSIS variable can be mapped.

The connection manager used for this data source is the same as that used for the Execute SQL Task i.e. Native OLE DB\SQL Native Client provider.

image

image

Step 5 – Add an OLE DB Destination

MSDN Link: How to: Load Data by Using the OLE DB Destination

Nothing add here either

image

Step 6 – Run the Package

So I ran the package and as you can see from the images below, the OLE DD Source failed with the following error:

[OLE DB Source [5018]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0x80040E21  Description: “Invalid character value for cast specification”.

Looking into this further shows that it failed on casting the GUID string value (66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3) to a GUID data type.  But how is this so as the Execute SQL Task succeeded and it’s using the same value?

imageimage

Step 7 – Debugging

The first step in the debugging process was to remove the parameter mapping for the OLE DB Source and use the GUID value directly:

WHERE     (ea.ProjectUID = ’66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3′)

As you can see, the package runs successfully.  This is what had me stumped for a while.

Anyway, to cut a long storey short, a STRING representation of a GUID needs to be formatted as {66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3} i.e. enclose the GUID in curly brackets (which are optional by the way)

This format is accepted by both the Execute SQL Task and the OLE DB Source.

imageimage

Conclusion

I’m not sure why the curly brackets are required for an OLE DB Source and not an Execute SQL Task (especially when they’re using the exact same provider) as I haven’t researched it yet.

Advertisements

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff is also a MCT specialising in SQL Server technologies and holds MCPD, MCITP, MCDBA and MCSD certifications.

4 Responses to Fun with SSIS and {GUID’s}

  1. Elbert says:

    Great post. I used to be checking continuously this weblog and I’m impressed! Very helpful information particularly the closing section 🙂 I deal with such info a lot. I was looking for this particular info for a very lengthy time. Thanks and good luck.

  2. Is there anyway to do this without having to write data back to a temp table? I’m trying to do this with a recordset destination, flat file, excel file, something. Great write up man!

  3. Just CAST the column instead of the variable in your SQL Command. (… WHERE CAST( Table1.GuidIdentifier AS NVARCHAR(50)) = ?)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: