Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import NULL GUID - SSIS Help needed Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2014 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:17 AM
Points: 9, Visits: 97
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 :)
Post #1561810
Posted Tuesday, April 15, 2014 5:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 856, Visits: 1,505
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?
Post #1561817
Posted Tuesday, April 15, 2014 6:42 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,636, Visits: 11,509
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1561843
Posted Tuesday, April 15, 2014 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:17 AM
Points: 9, Visits: 97
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.
Post #1561845
Posted Tuesday, April 15, 2014 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,636, Visits: 11,509
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1561846
Posted Tuesday, April 15, 2014 7:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 856, Visits: 1,505
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.


Post #1561870
Posted Tuesday, April 15, 2014 9:26 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:17 AM
Points: 9, Visits: 97
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.
Post #1561941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse