• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1102
  • Last Modified:

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
0
onesegun
Asked:
onesegun
  • 8
  • 6
1 Solution
 
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now