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

Nz(FieldName,'') Equivalent in SSIS for Microsoft Jet Ole db provider Expand / Collapse
Author
Message
Posted Tuesday, June 02, 2009 9:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 138, Visits: 381
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?
Post #727857
Posted Tuesday, June 02, 2009 10:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Try the ISNULL() function instead.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #727862
Posted Wednesday, June 03, 2009 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 138, Visits: 381
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?
Post #728105
Posted Wednesday, June 03, 2009 7:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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
Post #728139
Posted Wednesday, June 03, 2009 8:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #728259
Posted Thursday, September 03, 2009 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 12:51 PM
Points: 1, Visits: 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.
Post #782192
Posted Monday, April 01, 2013 10:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 794, Visits: 2,080
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 from Google.

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



Shawn Melton
PS C:\>(Find-Me).TwitterUri
@wshawnmelton
PS C:\>(Find-Me).BlogUri
meltondba.wordpress.com
Post #1437518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse