Null inside a Script Component

Hope everyone had a good holiday season.  I did.

Something I’m dealing with today has me stumped.  I searched google (ahem – live search) to see if there was any information out there and I didn’t find anything.

I have a script componenet that I’m using as a transformation and I’m adding a colunm to the data flow inside of it and the new column is an integer (DT_I4).  Inside the script I’m testing to see if a varchar field has a number in it (hence why I’m using a script component – there is no IsNumeric() in SSIS) and setting it to NULL if the incoming data isn’t a number.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)Dim Result As Int32
   If System.Int32.TryParse(Row.VarcharColumn, Result) Then
      Row.IntegerColumn = Result
   Else
      Row.IntegerColumn = vbNull
   End If
End Sub

So here is the deal.  When I set the IntegerColumn = vbNull, it comes out in the data flow as 1.  If I set it = to Nothing, it comes out in the data flow as 0Does anyone know how to set an integer in the data flow = NULL inside a script transformation taskBy the way – the work around I came up with was to set the integer column = 98989898 ( or some other rediculous integer) and then use a derived column task to transform that back to null in the next step of the data flow.Anyone have thoughts?

Mark

UPDATE: A colleague of mine, Yuri Bykov, has come up with an answer.  Check the comment below.  Thanks, Yuri!

Advertisements

4 Responses to Null inside a Script Component

  1. Yuri Bykov says:

    Hey dude, read the manual! :o) Just kidding…

    You can find the answer here under “What the BufferWrapper Project Item Provides”: http://technet.microsoft.com/en-us/library/ms136045.aspx.

    Quote: “A write-only _IsNull property for each selected output column that you can use to set the column value to null.” The key word here is “write-only”, which means that you are able to set it (duh :o).

    So, here’s the solution to your problem:

    If System.Int32.TryParse(Row.VarcharColumn, Result) Then
    Row.IntegerColumn = Result
    Else
    Row.IntegerColumn_IsNull = True
    End If End Sub

    Have a good one!
    Russky.

  2. JB says:

    Ugly, looks like Microsoft missed the boat on this one.

  3. Mitch says:

    This solution helped me as well.
    Thank you very much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: