question

pvsrinivasrao avatar image
pvsrinivasrao 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.
sql-server-2008ssisbids
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please add more details for your script task? I assume the default values are assigned to these fields in the Db?
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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; }
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.