asked
How to set default value of output columns in SSIS script component transformation as NULL
Hi, Is there a way to set default value of output columns in script component transformation as NULL. For example, Input column col1 dataype is string I have following output columns, test_1 datatype is int test_2 datatype is date if my script component has some code like below, if(Col1=="1") { Row.test1 = 1; Row.test2= DateTime.Now; } In this case for Col1 values other than 1 I am always getting test1 with defualt value = 0 and test2 as default datetime value. Is there a way to avoid this and set Test1 & Test2 as NULLs if not assigned in script component. Thanks.
Can you please add more details for your script task? I assume the default values are assigned to these fields in the Db?
May be I am not able to understand it correctly, but the same could be handled it at the script component level as well. Moreover, what you do if the criteria does not fulfill in the script task? Based on that you could derived column's expressions.
The newly added columns to the outputs of the Script Component has their default values as NULL. For Synchronous Script Component Transformation, the columns which are passed by from the source to the output have values on the output same as on the input unless manipulated in the script. It cannot be done in other way for the synchronous transformation as the input and output is the same buffer on which the script operates. For the asynchronout stransformation, new buffer is created for each output and this buffer contains only columns added to that particular ouput. In that case the default values are NULL as expected. If you want to set particular field to NULL, then you have to set the corresponding **`Row.FieldName_IsNull = true`**. So in your case if (Col1=="1") { Row.test1 = 1; Row.test2= DateTime.Now; } else { Row.test1_IsNull = true; Row.test2_IsNull = true; }
