Import NULL GUID - SSIS Help needed

Question

Post reply

Import NULL GUID - SSIS Help needed

shafirmubarak

SSC-Addicted

Points: 493
More actions
April 15, 2014 at 5:17 am

#292445

Hi Everyone,
I am a newbie here. I am trying to import data from flat file to SQL DB using SSIS. I have GUID data which i am importing into uniqueidentifier field.
My flow is as "Source File" -> Derived Column (Convert to GUID) -> Data Conversion -> OLEDB destination
It works fine as far as there is a valid GUID data in the source, but it fails when there is a "NULL / Empty". However i made that field to ignore failure and it successfully passed by.
But I feel it's not an optimal solution to ignore the error and pass through. Please advice.
Any suggestions would be much appreciated except "Not Possible :P" as I seen enough.
I tired -- User == "" ? "" : (DT_GUID)("{" + User + "}")
but it has a syntax error
Version using SQL 2012.
Thanks in advance,
newbie 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

what exactly you want to do with GUID column having null value?

Want to keep in the original table?

OR

want to Ignore this data

OR

Want to place a new GUID?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

shafirmubarak (4/15/2014)
I tired -- User == "" ? "" : (DT_GUID)("{" + User + "}")

What is "User" in your expression?

Regarding the empty GUIDS, what is the exact error that you get?

Try setting the property "Retain null values from the source as null values in the data flow" to true in the flat file source.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

shafirmubarak SSC-Addicted Points: 493 More actions · Answer 3

Thanks for your quick response.

I would like to import as it is. I dont want to ignore the rows which has null GUID.

It's acutally child records which may sometimes doesnt have a GUID associated with it.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

shafirmubarak (4/15/2014)
Thanks for your quick response.
I would like to import as it is. I dont want to ignore the rows which has null GUID.
It's acutally child records which may sometimes doesnt have a GUID associated with it.

I didn't say anything about ignoring rows.

If the field is empty in the flat file, it can be imported as a NULL value.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Koen Verbeeck (4/15/2014)
shafirmubarak (4/15/2014)
Thanks for your quick response.
I would like to import as it is. I dont want to ignore the rows which has null GUID.
It's acutally child records which may sometimes doesnt have a GUID associated with it.
I didn't say anything about ignoring rows.
If the field is empty in the flat file, it can be imported as a NULL value.

🙂

shafirmubarak SSC-Addicted Points: 493 More actions · Answer 6

Perfect that helps, Thanks everyone for looking into this,

It is resolved and I am closing this.

Resolution :

It got resolved by setting the property

"Retain null values from the source as null values in the data flow"

to true in the flat file source.