I'm retarded and need help with Derived Column or Script Task

I am moving data from an oracle database to sql server. Two of the source fields are dates, which sometimes contain values I know to be incorrect ('0001/01/01' and '1900/01/01').

I'd like to use either the derived column or script task (or any other appropriate one) to update these incorrect values (in both columns) to null before inserting into sql server, smalldatetime field.

In sql 2000 dts, I would simply use a VBScript IF statement, but I'm somewhat clueless when it comes to .Net.

Does anyone have any ideas or sample code that may help?

Thank you much.

In a derived column you could do a comparison expression such as:

[ORACLE_COL] == (DT_DBTIMESTAMP)"0001/01/01" || [ORACLE_COL] == (DT_DBTIMESTAMP)"1900/01/01" ? NULL(DT_DBTIMESTAMP) : [ORACLE_COL]

|||

That worked perfectly, thank you. However it also looks like it's slowing me down quite a bit. I'm moving a lot of data, 16 million records, from oracle to sql server. Moving this into a temp table without the derived column task only took 8 minutes. After adding the new task I can see it would take far longer.

Do you know of any good learning resources for SSIS besides Books on Line?

Thank you.

|||

www.sqlis.com is the recognised community site.

The guys that run it (Darren and Allan) are also running a new wiki site: http://wiki.sqlis.com/

-Jamie

|||I found that the WROX press book "SQL Server 2005 Integration Services",, ISBN 0-7645-8435-9 is a very good resource. I normally do not like using anything other than online resources either.

IIF Issues

I am trying to format dates that are in string format. When I use:
=IIF(Fields!MyDate.Value = "","EMPTY","STRING")
I get EMPTY and STRING right where they should appear. When I use:
=IIF(Fields!MyDate.Value = "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
I get #Error for all of the empty strings and correctly formatted dates for
everything else... what am I doing wrong?Try inserting spaces( i used 5 spaces)...I had the same problem, it does not
seem to like "".
~aparna
"MER78" wrote:
> I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates for
> everything else... what am I doing wrong?|||IIF always evaluates two sides of an expression, which is where the error
occurs. You can create a custom code function that has a regular If
statement, and call it like so:
=code.MyFunctionName(Fields!MyDate.Value)
Right-click on the report background and click Properties to get to the code
area.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:02AA454F-2678-4E0E-A910-F639933B6788@.microsoft.com...
>I am trying to format dates that are in string format. When I use:
> =IIF(Fields!MyDate.Value = "","EMPTY","STRING")
> I get EMPTY and STRING right where they should appear. When I use:
> =IIF(Fields!MyDate.Value => "","",Format((Convert.ToDateTime(Fields!MyDate.Value)), "MMM. d, yyyy"))
> I get #Error for all of the empty strings and correctly formatted dates
> for
> everything else... what am I doing wrong?|||YUCK... that's so much more of a hassle... what's the point of creating
branching code if it executes all cases?
"Jeff A. Stucker" wrote:
> IIF always evaluates two sides of an expression, which is where the error
> occurs. You can create a custom code function that has a regular If
> statement, and call it like so:
> =code.MyFunctionName(Fields!MyDate.Value)
> Right-click on the report background and click Properties to get to the code
> area.
> Cheers,
> '(' Jeff A. Stucker|||I've had success using = nothing
Hope this helps.
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I've had success using = nothing
hth
"MER78" wrote:
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
> > IIF always evaluates two sides of an expression, which is where the error
> > occurs. You can create a custom code function that has a regular If
> > statement, and call it like so:
> >
> > =code.MyFunctionName(Fields!MyDate.Value)
> >
> > Right-click on the report background and click Properties to get to the code
> > area.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker|||I tend to agree with you. But that's how IIF was designed eons ago for
VB/VBScript, not really for branching, but inline evaluation and population
of data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:9925D007-E1CD-47C6-8A48-FE06F826CDD1@.microsoft.com...
> YUCK... that's so much more of a hassle... what's the point of creating
> branching code if it executes all cases?
> "Jeff A. Stucker" wrote:
>> IIF always evaluates two sides of an expression, which is where the error
>> occurs. You can create a custom code function that has a regular If
>> statement, and call it like so:
>> =code.MyFunctionName(Fields!MyDate.Value)
>> Right-click on the report background and click Properties to get to the
>> code
>> area.
>> Cheers,
>> '(' Jeff A. Stucker