Set column to Null in script component

Question

Set column to Null in script component

stricknyn

SSCertifiable

Points: 6560
More actions
June 9, 2010 at 11:14 am

#140776

Hi all,
Is there a way to set a column to null in a script component? I've tried:
Row.PAIDDATE = System.DBNull.Value 'Cannont be converted to date error message and
Row.PAIDDATE = System.DBNull ' Cannot be converted to string error message
Nothing seems to work.
Thanks,
Strick

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I have run into the same problem and after much googling I discovered that VBA does not support Null in the same context it used to. as such Null is a property and can not be assigned as a value.

you can check for a null condition but you can not asign it as a value. Bummer hey.

At any rate I did find quite a few good discussions on asigning a null as a derived column.

http://consultingblogs.emc.com/jamiethomson/archive/2006/10/12/SSIS_3A00_-NULLs-in-expressions-gotcha.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

I just tried with a string (called 'Test') and this works:

Row.Test = Microsoft.VisualBasic.Constants.vbNullString

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

Phil, do you have any additional references added. I tried that back when I was working on my script and that command was not recognized. I suspect I was missing a reference but I was never able to find what reference to add.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Dan.Humphries (6/9/2010)
Phil, do you have any additional references added. I tried that back when I was working on my script and that command was not recognized. I suspect I was missing a reference but I was never able to find what reference to add.

Not a thing. This was a very simple test package that I use for trying things out and I just dragged in a script component and played around with it until I found something that worked. I used 2008 - maybe you are using 2005 and it's different?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

hmm maybe that is it. I do have 2005 and I opened up one of my packages and tried the command and it does not like it at all. I remember seeing that in a thread or post somewhere and I tried everything for 2005 but was never able to get it to work. I ended up going to a derived column in the end.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Can't you use the Nothing keyword from VBA to set the column value to null?

Or, why don't you use a derived column to set the column values to null?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SSIS_NewBi Right there with Babe Points: 776 More actions · Answer 7

I got the same problem initially in Bids 2005

Type in Microsoft.VisualBasic.Constants.

where you press"." you will see two options in intelli sense window

"Common" and "All"

In all I was not able to see it in common window but could find it in All window.

Just try it

this is little strange but it worked

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

'

' Add your code here

'

MsgBox("Test: " & Microsoft.VisualBasic.Constants.vbNullString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Dan.Humphries SSChampion Points: 12125 More actions · Answer 8

Ok I am starting to think I need to reinstall my bids.

I just tried it on another machine and it works fine but it still does not work on my local machine. maybe I just need to get out a bigger hammer.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

stricknyn SSCertifiable Points: 6560 More actions · Answer 9

Hi guys,

Well I feel silly. I knew all columns in Sript component has a IsNull property, but I always assumed it was a readonly (Get) property. As it turns out, you can actually set this property. Basically setting it to true will set the column to null. ie:

Row.PAIDDATE_IsNull = True

Thanks,

Strick