SQL Server Integration Services
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.
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.
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.
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
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