June 9, 2010 at 11:14 am
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
June 9, 2010 at 12:30 pm
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.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 9, 2010 at 2:38 pm
I just tried with a string (called 'Test') and this works:
Row.Test = Microsoft.VisualBasic.Constants.vbNullString
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 9, 2010 at 2:46 pm
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.
June 9, 2010 at 2:54 pm
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 9, 2010 at 3:00 pm
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.
June 10, 2010 at 3:16 am
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
June 10, 2010 at 9:52 am
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
June 10, 2010 at 10:04 am
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.
June 10, 2010 at 3:38 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply