Re: SSIS Script Component: How do I write code leave column/field as NULL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Conan,


Nothing should be a NULL however what I think you will get is "01/01/0001". You could always check for this after the Script Task and use a Derived column transform to set it to NULL.

Derived column transformas can certainly so what you want using SUBSTRING() and (DT_DBTIMESTAMP)

Here is an article that may help you

http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

Allan,

Thank you for your feed-back.

I was hoping for a Script Component solution. I just think it will be
easier for me to pick up the Script Component than the Derived Column
transform, based on my past experience. I tried messing with the
Derived Column a couple times before and couldn't get a handle on it.

The other thing I'm worried about is that a text file is source of the
data. So the [OpenDate] is a text string in the format of yyyymmdd, so
I'll have to use a combination of the mid, right, and left functions
with a date/dateserial function (or concatenate them with /'s into
another text string and cast as DT_DBTIMESTAMP). Can this be done in
a Dirived Column transform?

Can "NULL(DT_DBTIMESTAMP)" be used in a VB script to return NULL? Do
you know what the syntax would be to return NULL in VB Script?

I'll save this post and I'll try this solution the next time I'm
working on this project, if I can't get the Script Component to work.

Thanks again for all of your help,

Conan

"Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote in message
news:885683c23058c97b4e7ed15801@xxxxxxxxxxxxxxxxxxxxx

Hello Conan,

I would do this in a Derived Column transform something like

[OpenDate] == "0000000" ? NULL(DT_DBTIMESTAMP) :
(DT_DBTIMESTAMP)[OpenDate]

might need playing with but should be a good start.

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com
|
http://www.konesans.com
Hello all,

I'm trying to test a column and return a date calculated from the
columns value or leave the field/column as NULL

Here is what I was trying (based on ActiveX scripts in DTS, this
would default to NULL when tested false):

If Row.OpenDate <> "00000000" Then
Row.oOpenDate = DateFromString(Row.OpenDate)
End If
Row.OpenDate is [DT_STR], Row.oOpenDate is [DT_DBTIMESTAMP], UDF
DateFromString is pasted below sig.
That didn't work. What was returned was "0000-00-00 00:00:00"

I tried this:

If Row.OpenDate <> "00000000" Then
Row.oOpenDate = DateFromString(Row.OpenDate)
Else
Row.oOpenDate = Null
End If
MS Visual Studio for Apps suggested I correct "Null" to
"DBNull.Value". I accepted it's correction and then it tells me
"Value of type DBNull.Value cannot be converted to 'Date'"
How do I get it so that the field/column is left as NULL if the IF
statement comes back FALSE?

Thanks for any help anyone can provide,

Conan Kelly

Function DateFromString(ByVal pstrDate As String) As Date
DateFromString = DateSerial(CInt(Left(pstrDate, 4)),
CInt(Mid(pstrDate,
5, 2)), CInt(Right(pstrDate, 2)))
End Function


.

Relevant Pages

  • Re: SSIS Script Component: How do I write code leave column/field as NULL
    ... Derived column transformas can certainly so what you want using ... easier for me to pick up the Script Component than the Derived Column ... So the is a text string in the format of yyyymmdd, ... I would do this in a Derived Column transform something like ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS Transform column to another datatype
    ... Or you could use a derived column transform with this as the expression where dt == column ... database, using SSIS Data Flow. ... convert a string value ... Derived Column Transormation, ...
    (microsoft.public.sqlserver.dts)
  • Re: Row sequence in derived column transform
    ... You cannot add a function to the derived column transform to derive an auto incrementing number. ... You wouldn't be going through the data twice. ... Both of these components are Synchronous components and as such are lightning quick. ...
    (microsoft.public.sqlserver.dts)
  • Adding NULL to SSIS Derived Column
    ... I have a flat file that I'm sending through a derived column transform, ... I'd like to send a NULL value to SQL server ...
    (microsoft.public.sqlserver.dts)