how to handle unique identifier values during SSIS

问题:

I'm trying to to import an excel file with a unique identifier column into SQL server but SSIS is giving me a lot of trouble.

I can get the unique identifier values from excel into the OLE DB Destination component but i can't get them into SQL Server table.

I get the error message below on trying to insert the unique identifier.

NOTE: column CreatedBy is of type uniqueidentifier in the destination SQL server table.

There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[CreatedBy] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

回答1:

I recommend you try this:

(DT_GUID)("{"+(DT_WSTR,36)[CreatedBy]+"}")

because your initial string doesn't contain any brackets you need to add them before converting to GUID.

Have fun!

回答2:

You need to add a Derived Column with the following transformation

REPLACE(REPLACE((DT_WSTR,50)[CreatedBy], "{", ""), "}", "")

and map the new derived column to the output destination.

have fun!