How to output GUID without curly bracket

SSIS and Import/Export
1

Hi,

I have trouble getting rid of the curly brackets on the uniqueidentifier value. The destination is a csv flat file.

I tried something like this,
select substring(CONVERT(varchar(36), my_GUID), 2, 36) ...
but it complained that
Msg 8169, Level 16, State 2, Line 9
Conversion failed when converting from a character string to uniqueidentifier.
or this
select CONVERT(varchar(36), my_GUID) ...
but the brackets are still there.

Of cause, the select in SSMS did not return curly brackets, but the SSIS out did.

What is the trick?

Thanks!

2

THe error message doesn't make sense for what you posted. FWIW This works:

declare @my_guid uniqueidentifier = newid()
select @my_guid, substring(CONVERT(varchar(36), @my_GUID), 2, 36)

but I don't think that's what you want. try a derived column transformation in SSIS to get rid of the braces

3

SQL's trying to convert the string back to a guid. If there are braces {} around the value, you need varchar(38) not varchar(36) to hold the full value.

4

Thanks!

gbritton,
You are right. That syntax is correct.

ScottPletcher,
I also tried varchar(38) as you have suggested here.

With that fixed, now the package error out as:

[OLE DB Source [1]] Error: There was an error with output column "MY_GUID" (17) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.".
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

5

The column in your dataflow is probably too narrow to hold the result. Post the expression from your derived column transformation

6

I don't have derived column transformation yet. My "fix" was on adding substring/convert to all my SELECTs. There were multiple Selects union together, and I did not have them all converted at first.

Do I use string functions or type casts as expression? what should I cast to?

7

What is the datatype and length of the column (in the SSIS pipeline) that is to receive the converted data?

8

I am not sure I understand what you are asking here. That column in SQL Server is uniqueidentifier, and the output is csv file. All the substring/convert functions are added to get rid of the curly brackets. Otherwise the GUID is just exported fine.