1

Is there any native support for GUID / Uniqueidentifier in SSIS 2008 R2?

I am tasked to load the data from excel spreadsheets into SQL table using SSIS 2008 R2. Each load (excel sheet) has its batch number (GUID / Uniqueidentifier) generated within the package in order to identify the whole batch. Now when declaring the variables in SSIS I cant see any data type representing GUID / Uniqueidentifier. I can use string and cast that string into GUID but its failing too.

The only solution that I can think of is to use GUID as string all the way through SSIS and save into SQL table as varchar.

I dont know if this is the best option I have. Could someone please advise?

Thanks!

Yawar Murtaza
  • 3,655
  • 5
  • 34
  • 40
  • Excel doesn't know about GUIDs, it's just a text value. You are asking how to *convert/parse* that column to GUID. Either change the column type to DT_GUID in the source's Advanced properties, or use a Data Conversion transformation – Panagiotis Kanavos Oct 25 '16 at 08:05
  • Thanks for your reply Panagiotis. The batch number (GUID) is generated within the SSIS package using the script task (C#) and saved into a global variable named "BatchNumber". When declaring this variable SSIS i cant see GUID or Uniqueidentifier data type and converting string to GUID in Data Conversion transformation results in error. – Yawar Murtaza Oct 25 '16 at 09:33
  • 1
    What's the error? You should probnably post the expression and the error. That would tell us whether it's worth suggesting that you wrap it in curly braces like this suggestion: http://stackoverflow.com/questions/12356198/data-conversion-issue-in-ssis-package-text-to-guid/18876226#18876226 – Nick.Mc Oct 25 '16 at 09:39
  • Yes adding curly brackets worked. Full expression: (DT_GUID) ( "{" + @[User::BatchNum] + "}" ) – Yawar Murtaza Oct 25 '16 at 10:45

1 Answers1

2

SSIS has native support for GUID with DT_GUID data type of dataflow column.
If you have GUID as a string in its regular format like 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', you can convert it to DT_GUID with Derived Column and the following expression.

DerivedColumnName   Expression                         DataType
GU_ID               (DT_GUID)("{" +StringGUID+ "}")    Unique Identifier

Braces are mandatory.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33