SSIS: Dealing with empty strings in CSV

Hi Experts,

I was wondering if someone can help me.

I have a CVS file with a column name called 'Invoice #'. During importation I have renamed this column as 'invoicenumber' in the output column in the Flat File Source.

In Derived Column instead of the default NULL values for empty strings for this column that occurs in a SQL Server table I want to replace these with 'No Invoice Number'

I've put the below expression in the Derived Column:

ISNULL(invoicenumber) ? "No Invoice Number" : "invoicenumber"

Open in new window

.

However, the final table is still showing NULL for these empty strings.

I've also tried REPLACE function and this does not work.

Help!

OS
onesegunAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Try using the inline if logic (condition) ? true-part : false-part as mentioned before instead of Replace.  How about this:

LEN(invoicenumber) > 0 ? invoicenumber : "No Invoice Number"

Open in new window

0
 
Tony303Commented:
Hi,

SSIS is case sensitive, is that a problem.
Try also putting the column heading in braces...

So...

ISNULL([invoicenumber]) ?  "No Invoice Number" : [invoicenumber]

Open in new window


Hope this helps a bit.

T
0
 
ValentinoVBI ConsultantCommented:
If you can't get it to work with ISNULL then perhaps it's an option to use the LEN function:

LEN(invoicenumber) > 0 ? "invoicenumber" : "No Invoice Number"

Open in new window

Note that "invoicenumber" is a hardcoded string, if you expect the actual content of the column then you'll need to remove the quotes.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
onesegunAuthor Commented:
Hi Tony and Valentino,

Thanks for your input but unfortunately it is still frustratingly producing 'NULL' values for empty strings for both solutions.

I tried re-creating the table where the CSV file needs to go into (making the invoicenumber column not null in the create script) but it fails the package at the OLE DB Destination.

SSIS simply removes the braces from the solution below:

ISNULL([invoicenumber]) ?  "No Invoice Number" : [invoicenumber]

Open in new window


Any other ideas?

Thanks,

OS
0
 
ValentinoVBI ConsultantCommented:
The Flat File Source has got a property called RetainNulls.  To what is it currently set?  When set to False you should be getting empty strings instead of NULLs.  And when set to True you should be able to test on NULL with ISNULL.  Try changing this setting to see if you get better results.
0
 
onesegunAuthor Commented:
Ok so now I changed RetainNulls as false and the empty strings are coming through. However, when I apply the following expression in Derived Column:

REPLACE(invoicenumber,"","No Invoice Number")

Open in new window


I still get empty strings. I'm sure I have the syntax right.

Any ideas?

Thanks,

OS
0
 
onesegunAuthor Commented:
Yeah I tried the solution above but no joy.... :-(

I've imported the csv in Excel and it is string length 0. That LEN function should work surely....
0
 
ValentinoVBI ConsultantCommented:
Something must be differing from what has been specified.  The above expressions should all work, I use them all the time...

Would it be possible to post a small sample file with which you've got the issue?  Careful to not post any sensitive data!
0
 
onesegunAuthor Commented:
Data Flow Task
Hi Valentino,

I've attached an image of my DFT and also a small sample file of the csv I'm attempting to load with invoice numbers and with no invoice numbers.

Thanks,

OS
testfile.txt
0
 
ValentinoVBI ConsultantCommented:
What you've posted is not a CSV but a fixed width flat file.  And the columns don't match.  This is what line 5 and 6 look like:

xxxx	999200	xxxx001	xxxxxxxxx London		999200	xxxxxxxx xxx (UK) Ltd.	DZ	08/23/2012		181,747.05		
xxxx	325000	xxxx001	xxxxxxxxx London	xxxxxtel Renewal Costs FY12	30003068	INTERNAL	RV	09/24/2012	9049002405	8,388.00	

Open in new window

Scroll to the back and you'll see immediately that something's off...
0
 
ValentinoVBI ConsultantCommented:
ow, unless TAB is your delimiter?
0
 
ValentinoVBI ConsultantCommented:
Okay, I've tested your file and it's working perfectly (except for a missing CRLF on the last line but that doesn't impact invoice #).  Perhaps try to start over?  Delete the flat file source and connection manager and start from scratch.

The LEN expression works using the defaults.  The ISNULL expression works after settings the RetainNulls property to True.
0
 
ValentinoVBI ConsultantCommented:
BTW: why are you using that Data Conversion transform?  Try to remove that and use only the Derived Column.

In fact, I never use Data Conversion because you can do the same using Derived Column (and Derived Column supports copy/paste).
0
 
onesegunAuthor Commented:
Ok I've removed the data conversions.

The only reason I put it there was because I was getting errors in the OLE DB Destination regarding
column cannot convert between unicode and non-unicode string data types

The column name properties are nvarchar(255) in the SQL table I'm importing the data too.

Did you get this error and if so how do you recommend dealing with this?

Thanks,

OS
0
 
onesegunAuthor Commented:
Yippeee!!!

I got it working at last. It must have been that Data Conversion that I put in the middle.

Anyway, the problem seems to have been that the csv file was in non-unicode and my SQL table was in unicode.

By right clicking on the Flat File Source and clicking on Show Advance Editor I was able to amend the data type and output length on the flat file output columns.

Cheers

OS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.