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 2, 2009 9:59 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 151, Visits: 420
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 2, 2009 10:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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 3, 2009 6:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 151, Visits: 420
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 3, 2009 7:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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 3, 2009 8:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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 3, 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 1, 2013 10:24 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 @ 2:25 PM
Points: 834, Visits: 2,280
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).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1437518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse