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
Row.IntegerColumn = vbNull
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?
UPDATE: A colleague of mine, Yuri Bykov, has come up with an answer. Check the comment below. Thanks, Yuri!