Date issue with Derived Column / Expression Language

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jeff,

I don't think that's the case. I have just created a package containing a DT_DBTIMESTAMP, DT_DBDATE & DT_DATE and managed to put the value "1500-12-31" into each of those columns.

-Jamie

|||Hey Jamie, thanks for taking the time to answer....but, did you attempt a date function on any of the dates. Try doing a DATEPART("YEAR",date_col) and see what happens.|||

Hey. That function works on any date after 1753-01-01, nothing before that.

Looks like you were right!!

-Jamie

|||Great. I wanted some independent verification. I just submitted this as a bug.

date conversion - derived column

I'm reading a .csv file that has a column such as 17June2007:00:00:00, how can I convert this column in my Derived Column routine so its inserted into my DateTime column in my database?

so far I've tried

DT_DateTimeStamp

DT_Date

DT_DBDate

DT_DbTime

and its kicking back errors on this column. What do I have to do to convert 17June2007:00:00:00 into a readable datetime for the database column?

I think you'll need to parse the date. Find the first colon, and work backwards. Something like:

(DB_TIMESTAMP) SUBSTRING([Your_Date], FindString([Your_Date], ":", 1), 4) ... and so on.

|||I've tried that and it still fails.

|||

Are you getting an error message?

|||yes

|||

Hi

I think you need to do it in 2 steps :

1. Use "Direvied Column" task convert you string "17June2007:00:00:00" to a propert Datetime string

"17 June 2007 00:00:00"

2. Use "Data Conversion" task to generate a new column of Date [DT_Date] datatype

|||

I've just finish trying that actually and it still fails. The field is defined as a string (DT_STR), I then create a new derived column and set that data type to DT_DATE and it fails, I even tried DT_TIMESTAMP, DT_DBDATE, and DT_DBTIME and all fail.

|||

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

|||

Now Im getting this:

[Data Conversion [1698]] Error: Data conversion failed while converting column "tDate" (733) to column "Copy of tDate" (1745). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I have the date field in the derived columns as a string, I then add a 'data conversion' task and take my date field and convert that to a DT_DATE and I get the above message

|||

NB2006 wrote:

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

The heck you say.... You can too change a data type in a derived column transformation. You most certainly do not need both.|||

Phil Brammer wrote:

NB2006 wrote:

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

The heck you say.... You can too change a data type in a derived column transformation. You most certainly do not need both.

I'm not SSIS expert but I thought thats what the derived column was doing, changing the dataType for that column. I've looked at both and the data conversion task appears to be doing the same thing as a derived column is doing with the data type aspect of it. Either way I'm still failing on this date thing and its driving me nuts.

|||

Hi Phil

You are right , "Derived column" on its own worked just as well.

Derived Column - <New Column Name>

Expression - SUBSTRING(Date,1,2) + " " + SUBSTRING(Date,3,4) + " " + SUBSTRING(Date,7,4) ..... or whtever you are using

DateType - Date (DT_Date]

Length - NA

|||How are you doing substrings when the length of the month name can vary? That is, how are you parsing the date field to apply to all months?

I'd try to spend more efforts on the source side to get this date field cleaned up.|||

Phil Brammer wrote:

How are you doing substrings when the length of the month name can vary? That is, how are you parsing the date field to apply to all months?

I'd try to spend more efforts on the source side to get this date field cleaned up.

I can't tweak the source at all. I have to work with it as it is. Its currently working in a DTS package, its when I migrated that DTS package to SSIS that the date format is an issue. The date in the source field has never changed and always has been formatted like "01JUN2007:00:00:00" and that format is loading fine in a DTS package and that package has no specail formatting being done and its loading into the table on SQL 2000 as 6/1/2007, so what do i need to get that format to work on my SQL 2005 database via a SSIS package?

|||

IGotyourdotnet wrote:

I can't tweak the source at all. I have to work with it as it is. Its currently working in a DTS package, its when I migrated that DTS package to SSIS that the date format is an issue. The date in the source field has never changed and always has been formatted like "01JUN2007:00:00:00" and that format is loading fine in a DTS package and that package has no specail formatting being done and its loading into the table on SQL 2000 as 6/1/2007, so what do i need to get that format to work on my SQL 2005 database via a SSIS package?

So what is the format of the file? "01June2007" or "01JUN2007"? That is, is the month always three characters?