Data Conversion - NULL datetime causes failure

Hi,

I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an error output. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug? It seems to me it is. I can't believe this would be the desired behavior?!

Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?

Thanks

Do you have the retain nulls option checked in the Flat File Source?

If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

Code Snippet

([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

|||Thanks much - the checkbox in the flatfile source did the trick. Never noticed that before, but it worked great. Thanks again.

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty existing table!

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.Hi,

You can use a workaround in this case... Put a DEFAULT constraint on
your column and it will work...

Enjoy,

Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
aling a crit :

Quote:

Originally Posted by

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.
>
ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO
>
You should change it to these statements in SQLServer2000:
>
ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO
>
ah, ridiculous! right?
>
Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.

Daily Caching

I have set up a data-drive subscription to do a null report on a report
that takes 3 minutes to run.
I have it set up to run at 3 am and it works fine.
My question is when a user runs it at 6 am, does it use what I cached
plus the new data, or does it re-cache the whole thing?
This report is intense and I really do not want to run it during the day
if I don't have to.
If it constantly re-caches the report, would creating a snapshot on a
schedule be the better option?
Thanks.It depends on the execution settings of the report. If the report is set to
cache the execution and the DD subscription runs with all parameter
combinations, and the cache expiration is sufficiently long enough then
users will get cached renderings, they will not get any recent data.
Using a snapshot is another possibility, the only problem is that any
parameters that affect the query will be locked and users will not be able
to change them.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:uXbnj4a7EHA.2124@.TK2MSFTNGP15.phx.gbl...
>I have set up a data-drive subscription to do a null report on a report
>that takes 3 minutes to run.
> I have it set up to run at 3 am and it works fine.
> My question is when a user runs it at 6 am, does it use what I cached plus
> the new data, or does it re-cache the whole thing?
> This report is intense and I really do not want to run it during the day
> if I don't have to.
> If it constantly re-caches the report, would creating a snapshot on a
> schedule be the better option?
> Thanks.
>