Issues while Importing data from Excel to SQL using SSIS - 1 - Uniqueidentifier column

There are multiple issues which one can run into while importing data from Excel to SQL server. Although SSIS has pave quite a easy way to accomplish this task but failing to paying attention to details can run us into multiple issues.

In this part we will be discuss the issue related to column from excel with Uniqueidentifier data in it. 

I have an excel file (GUIDShow) with following column structure and data in GUIDDesti sheet

Excel Data

ID - Int
UniqueId - String







I also have a table with following structure in one of the SQL database

CREATE TABLE tbl_ExcelGUIDImport
(
ID SMALLINT,
UniqueId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
)

INSERT INTO tbl_ExcelGUIDImport(ID)
SELECT 10

SELECT * FROM tbl_ExcelGUIDImport

Now I'll try to import data from above into tbl_ExcelGUIDImport using SSIS package. 

• Create one OLEDB connection manager pointing to the database where tbl_ExcelGUIDImport is created.
• Create excel connection manager (GUIDShow) pointing to the excel file GUIDShow (file with data from screenshot)
• Take one DataFlow task 
• Inside DataFlow task add Excel Source 
• Edit its properties such that it uses the connection manager created in step 2 and the sheet which has data from the screenshot. In my case it looks like below

Once the connection is made and appropriate excel sheet is selected click on preview, and the data from excel is shown in the new window. 

Click OK.

Click on columns tab and you would see columns from excel sheet listed there.

Click OK of excel source editor.

• Similarly add OLEDB destination. Join Excel source to this Edit its properties such that it points to the OLEDB connection manager created in first step.  

Once the Connection manager, Data access mode and table is selected make sure that you click on Mappings tab.

This is required since here column from excel source are mapped with the columns from OLEDB destination.

In my case column names are same hence they'll be mapped automatically.

If you have chosen different column names then you'll have to map them explicitly.

Once done, click OK.

The DataFlow task with look like this

Now run the package. It will fail.

In my case it has failed with Invalid character value for cast specification Error

I'll try and see if this error can be avoided using data conversion transform. This transform needs to added between Excel source and Destination. 

In that I'll try to convert String values to Uniqueidentifier.

Select UniqueId as a InputColumn in Data Conversion. And then select its type as Uniqueidentifier. This editor shows that it will create output alias (New column) for selected input column as Copy of UniqueId. Click OK.

Now that we have a new column with unique Identifier data is created. We need to map this new column to the destination column.

In order to do this open OLEDB destination properties go to Mappings tab there change input column from UniqueId to Copy Of UniqueId.

As it can be seen in the screenshot, I have mapped new column to UniqueId destination column.

Click OK.

Now try to run the package. 

In my case it has again failed with following error 

The value could not be converted because of a potential loss of data.

The error is Self-explanatory.

It appears that there is still something that we are doing wrong here. 

To identify the problem lets try other way around. Lets try to Export data from table to Excel sheet.

Now add new data flow task which would have OLEDB as source and Excel as Destination.

Try to run this DataFlow task alone and you would see that it executes just fine. Let's see what do we have in excel after this successful execution.

As we can notice while exporting data to excel SSIS has enclosed uniqueidentifier data into curly braces {}.

This is something we didn't do while importing into SQL server .

So does this mean we not only have to convert but also have to enclose values from UniqueId column into curly braces ?

let us try and see if that helps.. In order to do this we will require Derived Column Transformation. Now that we are adding derived column transformation we can do Type Casting there it self. So we can get rid of Data conversion task here.

As you can see in the Derived Column Transformation editor we have added an expression wherein we are appending {} to uniqueId column and while doing so we replacing existing column instead of creating new one.

The expression used is 

(DT_GUID)("{" + UniqueId + "}")

Click OK.

Go to OLEDB destination editor. In that Mapping tab now change back mapping from Copy of UniqueId to UniqueId column.

Before executing package make sure that you delete the row that we added while exporting data from table to Excel. If you forget to do so your package with fail because 3rd row already has a curly braces and if we add one more then data conversion error would occur.

After deleting the row from excel. Run the package and see it executing successfully.

So take away from this exercise is- 

Make sure you enclose string containing uniqueidentifier from excel into curly braces before importing into SQL table column with UNIQUEIDENTIFIER as its datatype.

In case you have few rows in excel enclosed in {} while others are NULL then expression given above won't work. In that case we will have to rewrite the expression as

ISNULL(UniqueId) ? NULL(DT_GUID) : (DT_GUID)(FINDSTRING(UniqueId,"{",1) > 0 ? UniqueId : "{" + UniqueId + "}")