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

SSIS : IsDBNull Function is not working as expected

Hi,

Im using SSIS to create a flat file retrieving data from a table. Im using Visual Basic 2008 and SQL server 2008.

I have a Data flow Task inside which i have a OLE DB Source component which retrieves data from a table and sends it to a script component. One of the field that is retrieved from the table is a DateOfBirth Field. It is of datatype "date". When i use IsDBNull(DateOfBirth) in the Script component im getting a "RunTime Error: The column has a null value.".

Why is this happening? Is this a bug in SSIS?
0
asubbiah
Asked:
asubbiah
  • 4
  • 3
  • 2
  • +4
3 Solutions
 
GMGeniusCommented:
There is a really good article that I think might help you here
http://www.mssqltips.com/tip.asp?tip=2028
I think you need ISNULL(DateOfBirth)
 
0
 
neeraj523Commented:
try

if DateOfBirth = "" Then
0
 
vdr1620Commented:
Trying using ISNULL OR LEN (DateOfBirth) > 1 --- should definetly work
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Reza RadCommented:
you should:
Check the retain null values from the source as null values in the data flow option and then you will get your nulls

this will solve empty values from flat file in ssis problem

reference:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b2984e44-c353-4a6d-9761-8a589bcc5063




0
 
Mohamed AbowardaSoftware EngineerCommented:
Try the following:
If DateOfBirth Is Nothing Or DateOfBirth = "" Or Len(DateOfBirth) < 1  Then

End If

Open in new window

0
 
Mohamed AbowardaSoftware EngineerCommented:
Also try:
If DateOfBirth Is DBNull.Value Then

End If

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Please check null or convert null to blank in first query.
0
 
Anthony PerkinsCommented:
PatelAlpesh,

I ma not sure if you noticed, but this question is more than 6 months old and the author appears to be MIA.
0
 
Mohamed AbowardaSoftware EngineerCommented:
I have posted possible solution for this question
0
 
GMGeniusCommented:
I too also gave a good link to assist here. I recomend a split between all contributors
0
 
Anthony PerkinsCommented:
I recommend you award points to:
http:#a33595056
http:#a33602449
0
 
Mohamed AbowardaSoftware EngineerCommented:
It wouldn't be fair to split point between two experts practiced in the question and posted possible solution while the others not who actually posted possible solutions too.

I recommend closing this question by accepting each possible solution:
http:#33595056
http:#33602449
http:#33604029
0
 
GMGeniusCommented:
I agree - all contributors where helpfull
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now