Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark