Nz(FieldName,'') Equivalent in SSIS for Microsoft Jet Ole db provider

Question

Nz(FieldName,'') Equivalent in SSIS for Microsoft Jet Ole db provider

sql server developer

SSCarpal Tunnel

Points: 4746
More actions
June 2, 2009 at 9:59 pm

#155773

Hi All,
I'm trying to access the MS access tables in SSIS using Microsoft Jet Ole db provider.
I'm trying to find the Null values and replace it by some value.
When i use Nz(FieldName,'') i'm receiving an error saying invalid function Nz. But if I use the same in MS Access db it is running successfully.
Is there any equivalent of Nz function that I can use in SSIS using Microsoft Jet Ole db provider for accessing MS Access data?

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Try the ISNULL() function instead.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 2

Hi thanks for your reply.

IsNull() function in MS access checks whether the values is null or not.

But I want it in a way that if it is Null i'm going to replace it by ~.

Something like IsNull(FieldName,'~') that we use in SQL Server.

Is there a function equivalent to that besides Nz(fieldname, '~') in MS Access?

steveb. SSC-Forever Points: 46733 More actions · Answer 3

you can use the COALESCE function in SQL server to perform a simliar opertion to Nz

select coalesce(FieldName,'NewValue')

http://msdn.microsoft.com/en-us/library/ms190349.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

sql server developer (6/3/2009)
Hi thanks for your reply.
IsNull() function in MS access checks whether the values is null or not.
But I want it in a way that if it is Null i'm going to replace it by ~.
Something like IsNull(FieldName,'~') that we use in SQL Server.
Is there a function equivalent to that besides Nz(fieldname, '~') in MS Access?

I thought you wanted to do this through SSIS? In SSIS the function is IsNull(FieldName,'~'). In Access it's Nz(). Which is it that you really want to do?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

donald_walton SSC Journeyman Points: 97 More actions · Answer 5

In SSIS, to replace a null value - used the Derived Column box with the following systax:

ISNULL( [FLDA]) == TRUE ? 'X' : [FLDA]

Relates to ' If FLDA is null, move 'X' to FLDA, else move FLDA to FLDA'.

This will check FLDA for nulls - it it is equal to nulls, it will replace it with 'X' (or any other character you specify), otherwise, it just keeps the content of FLDA intact.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 6

For folks that come across this post...

NZ() function is not available outside of Access so you can't use it in SSIS. What I came across was having to do something like this:

SELECT IIF(ISNULL(Column1),'',Column1) AS MyColumn

I found the above by falling over this MSDN forum post[/url] from Google.

Caveat: I am working with SQL Server 2008 R2 so I have not tried this on SQL Server 2005.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton