SQL Lion

In big enterprise or organization, it is very common that they use GUIDs to uniquely identify each of Business Keys for any subject matter like EventIDs, RegistrationIds, or even the master data like Categories, hierarchies, etc. The reason behind using GUIDs is that a GUID is unique in nature across any platform, any server and at any point of time. The earlier versions of GUIDs contains encrypted MAC address with date component when it is got created. But in order to resolve individuals’ privacy issue, the algorithm got changed to more sophisticated form. Practically it’s a very huge number i.e. a 128 bit integer. We can store up to 2¹²⁸ unique keys. So generating a same unique randomly twice is negligible.

The global representation of GUID is {00000000-0000-0000-0000-000000000000} and the digits on each place are dependent on the type algorithm used to generate the unique key. (format » {8-4-4-4-12}, total 36 characters including ‘-‘)

But SQL Server UniqueIdentifier data type stores GUID without including curly braces like 00000000-0000-0000-0000-000000000000. In this post, I will show how to import / export GUID columns from excel to SQL Server database by using SSIS.

Let’s create one standard table with GUID column in our TestDB database.

CREATE TABLE dbo.tranIncident
(
    UniqueID     UNIQUEIDENTIFIER NOT NULL,
    IncidentType NVARCHAR (100)   ,
    OpenTime     DATETIME2       ,
    CloseTime    DATETIME2       
);

The above is a very simple table which contains some information regarding the Incidents recorded at customer support database. Let’s populate some dummy data into it. In SQL Server, we can generate a unique GUID by using NEWID () or NEWSEQUENTIALID ().

INSERT INTO [dbo].[tranIncident]
           ([UniqueID]
           ,[IncidentType]
           ,[OpenTime]
           ,[CloseTime])
VALUES (NEWID(), 'Type1', GETDATE(), GETDATE()),
(NEWID(), 'Type2', GETDATE(), GETDATE()),
(NEWID(), 'Type3', GETDATE(), GETDATE()),
(NEWID(), 'Type4', GETDATE(), GETDATE()),
(NEWID(), 'Type5', GETDATE(), GETDATE()),
(NEWID(), 'Type6', GETDATE(), GETDATE()),
(NEWID(), 'Type7', GETDATE(), GETDATE()),
(NEWID(), 'Type8', GETDATE(), GETDATE())
GO
SELECT * FROM [dbo].[tranIncident]

OUTPUT:

Now let’s create an SSIS package to export the data from above table to an Excel file.

Open BIDS (Business Intelligence Development Studio) and select “Integration Services Project” on the right pane under Template section.  Name the package to something meaningful like “ExportGUIDsToExcelPackage.dtsx”.

Now in the Control Flow, add one Data Flow Task from Toolbox and name it as “Export GUIDs”.

 Add the connection managers to the package as below; one for OLEDB source to extract data from the table [dbo].[tranIncident] and the other is Excel Destination for exporting data to an Excel file. (Right click on Connection Managers pane and add respective connections.)

Double click “Export GUIDs” data flow task and add “Data Flow Sources” and “Data Flow Destinations” as below:

Double click “Source tranIncident” component and configure the fields as below:

Double click “Excel Destination” component and click the button named “New…” beside “Name of the Excel Sheet” as shown below:

You might get an error showing “no sufficient information about mapping SSIS types…”, ignore this error and press OK.

It will automatically create the script for the destination table (i.e. destination excel sheet).  In excel DATETIME2 data type is not supported. Hence, change all occurrence of DATETIME2(7) to DATETIME and press OK.

CREATE TABLE `Excel Destination (tranIncident)` (
    `UniqueID` UNIQUEIDENTIFIER,
    `IncidentType` NVARCHAR(100),
    `OpenTime` DATETIME,
    `CloseTime` DATETIME
)

Select the excel sheet name from the drop down containing $ sign as below.

Click on Mapping tab on the left pane. It should automatically map all the columns, and if it didn’t then map it accordingly and press OK.

Execute the package; you will notice that 8 records got transferred from SQL SERVER table to EXCEL file.

Now navigate to the destination folder and open the excel file. You will notice the GUID format in Excel is little different than of SQL Server i.e. it encapsulates the GUID with curly braces as below:

For ‘OpenTime’ and ‘CloseTime’, it is showing only date part because of default date format in Excel. You can change it to any format available in Excel (Select the column, right click and click on ‘Format Cells…’ and in the ‘Format Cells’ dialog box, select the format you want to show.)

But to get the exact format that is showing in SQL Server, apply ‘Custom Format’ with the value as “yyyy/m/d h:mm:ss.ms”.

Press OK, you will get the values as below:

Now if you want to store the Unique IDs in Excel without curly braces, the following changes need to be done.

In the data flow task, change the ‘Data access mode’ to ‘SQL Command’ for “Source tranIncident” component and enter the below query in the command text box.

SELECT CAST (UniqueID AS NVARCHAR (50)) AS UniqueID,
       IncidentType,
       OpenTime,
       CloseTime
FROM   [dbo].[tranIncident];

So, the only change we are doing here is converting the UniqueIdentifier data type to nvarchar(50), so that we can get the GUIDs as text in Excel. Update the metadata in ‘Excel Destination’ and run the package.

Now SSIS, don’t do automatic metadata refresh as long as the data types are compatible to each other. So, to refresh the metadata, either delete the components and create the new ones, or follow the below steps:

• After changing the query, right click on ‘Source tranIncident’ component and click on ‘Show Advanced Editor…’
• Navigate to ‘Input and Output Properties’ tab
• You will notice that data type of ‘UniqueID’ under ‘Output Columns’ is still “unique identifier [DT_GUID]”. Change the data type to “Unicode string [DT_WSTR]”.
• Press OK

• Create the destination excel sheet once again in “Excel Destination (tranIncident)” component.
• Execute the package.

Output in Excel File:

Note: This time the GUIDs are populated in Excel file as nvarchar data type, hence there are no curly braces surrounding the GUIDs.

But the reverse is little critical and tricky; if we want to populate the GUIDs from excel which is in the above format i.e. in string format without any curly braces.

Converting a string GUID without curly braces or with curly braces to a GUID of UniqueIdentifier is easy and straight forward in SSMS as below:

SELECT CAST ('FC8932FC-A7F6-45D6-89E1-CDB0F5F34F0A' AS UNIQUEIDENTIFIER) AS [GUID_Without_Curly_Braces];
GO
SELECT CAST ('{FC8932FC-A7F6-45D6-89E1-CDB0F5F34F0A}' AS UNIQUEIDENTIFIER) AS [GUID_With_Curly_Braces];
GO

You will get the same result set.

But the same is not true for SSIS transformations. In SSIS, if a GUID is coming as a source of data type string, then it has to be encapsulated with curly braces otherwise it will throw error:

[tranIncident [26]] Error: There was an error with input column “UniqueID” (48) on input “OLE DB Destination Input” (39). The column status returned was: “The value could not be converted because of a potential loss of data.”.

The problem is SSIS engine does not consider a string as GUID if it does not encapsulated with curly braces and hence throws error while converting it to UniqueIdentifier type.

The only optimum solution for this is to add curly braces i.e.{} while importing from excel file as below:

• Change the ‘Data access mode:’ for “Excel Source(tranIncident)” component to ‘SQL Command’
• Type the below code to extract data from excel file.

SELECT      "{"+CStr(UniqueID)+"}" as UniqueID
,  IncidentType
,  OpenTime
,  CloseTime
FROM            [Excel_Destination__tranIncident$]

• Almost all the functions that used in Excel VBA can be used here to extract data; here CStr() means ‘Convert to String’ (Note: SQL Server functions and operators will not work for Excel source connection managers)
• Reset the metadata in destination component and run the package.

Hope you enjoyed reading this article. Waiting for your valuable comments. ☺
Download Sample SSIS Package:Handling Or Importing GUIDs In SSIS (82)