none
how to convert nvarchar to uniqueidentifier

    Question

  • I have a package in which the  source is excel and target is database table.

    based on certain criteria the report is genetrated and the output format of excel  unique identifier column also.

    i.e

     column1                                                     column2          column3

    2C65A997-4CE7-4AEC-8D56-7F857E9B6244    1                         2

     

    here the column 1 is coming from a table to the excel.

    When I upload the data from excel,the data type of the unique identifier column is displayed as nvarchar but in the target it is uniqueidentifier.

    I converted the column1 to unoque identifier and when I run the package it shows error cannot conver because of potential loss of data


    fuzailrashid
    Tuesday, December 28, 2010 12:00 PM

Answers

  • Hello Fuzail,

    What i understand from your post that you have Excel Source with giving data, The first column has GUID data.

    1--I created Excel Source

    2--Use Derived column and add new column and wrote above expression

    3--Inserted data into SQL Table that has column with uniqueidentifier datatype. 

    I used the data u have provided and it worked, Can you please let me know if i missed anything? At which point you are getting above Error?

     

    Thanks

    • Marked as answer by fuzailrashid Thursday, December 30, 2010 5:03 AM
    Wednesday, December 29, 2010 2:33 PM

All replies

  • Hello Fuzail,

    Use these expression in your Derived Column for column you are getting from Excel (uniqueidentifier)

     

    (DT_GUID)("{" + YOURCOLUMNNAME + "}")

     

    You need to wrap your source data into {} and then convert to GUID as expression show above.

    Thanks

    Tuesday, December 28, 2010 3:55 PM
  • You can also use the Script Component.

     

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
     Row.Col1Guid = new Guid(Row.Col1);
    }
    Input Column = Col1
    Outut Column = Col1_Guid 
    DataType for Col1_Guid = unique identifier [DT_GUID]
    
    Tuesday, December 28, 2010 4:35 PM
  • Hi Aamir

    I have used the above expression but it shows error

     i.e unable to perform a type cast

    please help me on this


    fuzailrashid
    Wednesday, December 29, 2010 4:21 AM
  • Hello Fuzail,

    What i understand from your post that you have Excel Source with giving data, The first column has GUID data.

    1--I created Excel Source

    2--Use Derived column and add new column and wrote above expression

    3--Inserted data into SQL Table that has column with uniqueidentifier datatype. 

    I used the data u have provided and it worked, Can you please let me know if i missed anything? At which point you are getting above Error?

     

    Thanks

    • Marked as answer by fuzailrashid Thursday, December 30, 2010 5:03 AM
    Wednesday, December 29, 2010 2:33 PM