Script Task Code Help Needed

Question

Post reply

Script Task Code Help Needed

Ed Perlstein

Valued Member

Points: 51
More actions
December 4, 2008 at 5:05 pm

#128333

I added a Script Task to a ForEach Loop container. I want to add code in the script to check if the variable is null and ,if so, to set the variable to "". In the following example, the use of IsNull is showing (when I hover over IsNull) as an undeclared name (exact msg is "Name 'IsNull' is not declared". Any suggestions?
If IsNull(Dts.Variables("JobSiteCode")) Then
Dts.Variables("sJobSiteCode").Value = ""
Else
Dts.Variables("sJobSiteCode").Value = Dts.Variables("JobSiteCode")
End If
Also, this is the first time I've worked with the DTS Object. Is my use of the DTS object properties accurate?

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

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

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 1

Try this.

Set Option Strict Off [/b]at the top of Script Task

If Dts.Variables("JobSiteCode").Value <> "" Then [NOT NULL]

Dts.Variables("sJobSiteCode").Value = Dts.Variables("JobSiteCode")Else

Dts.Variables("sJobSiteCode").Value = ""

End If

NOT NULL -was my comment.

I was basically trying to say if the value is not equal to empty / is not null.Which means it has a value and is not null

Sorry for the misleading info

Ed Perlstein Valued Member Points: 51 More actions · Answer 2

Thanks for your reply. I don't understand your syntax of "Then [Not Null]", and either does the compiler. If I remove that, then the code doesn't address Null results. I have to admit that I'm coming from a VB6 environment and do not know VB .Net, so if your method of addressing Nulls is sound but your syntax was written wrong, please let me know.

Thanks

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

Try this:

If Dts.Variables("JobSiteCode") Is Nothing Then

Dts.Variables("sJobSiteCode").Value = ""

Else

Dts.Variables("sJobSiteCode").Value = Dts.Variables("JobSiteCode")

End If

You can also use the following:

If IsNothing(Dts.Variables("JobSiteCode")) Then

Dts.Variables("sJobSiteCode").Value = ""

Else

Dts.Variables("sJobSiteCode").Value = Dts.Variables("JobSiteCode")

End If

fstop Default port Points: 1482 More actions · Answer 4

Thanks. I can't say if it will work or not b/c I've been pulled onto another project and I have to put this aside for awhile. I do know that the syntax was accepted by the compiler. However I'm concerned that Is Nothing is not the same as testing for Is Null b/c my understanding is that they are not the same values (from what i understand, nothing is not a value but null is).

Paul_J Hall of Fame Points: 3698 More actions · Answer 5

Not sure what you mean by "Null". Where is the value coming from? From a database call? If so, you may need to compare against DBNull.Value.

fstop Default port Points: 1482 More actions · Answer 6

The value is coming from a database call issued in an ExecuteSQL task and passed through to a ForEach Loop Container for use by the Script. Variables have been created to hold the data. The database field is a nullable field. So I would like to test for a Null value and pass a blank to the next step, Insert, instead of a Null value.

You're probably correct regarding the use of DBnull, since my research indicates that VB .Net uses DBnull rather than Null. Unfortunately, being a VB6 programmer my .Net expertise is limited.