Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.

I used two "Derived Column" tasks for this after giving up with a "Data Conversion" task. I then parse the date, month and year out using SUBSTRING:

-- Assuming [This_Date]="21/03/2012"

-- Assuming [This_Date]="20120321" (YYYYMMDD)
(DT_DATE)(SUBSTRING(This_Date,6,2) + "/" + SUBSTRING(This_Date,9,2) + "/" + SUBSTRING(This_Date,1,4))

-- Assuming [This_Date]="21032012" (DDMMYYYY)
(TRIM(This_Date)=="")? (DT_DBTIMESTAMP)"1901-01-01 00:00:00" : (DT_DBTIMESTAMP)(SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00")

-- Explicit: Assuming [StartDate]="21032012" (DDMMYYYY)
(DT_DATE)(SUBSTRING((DT_STR,8,1252)StartDate,1,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,3,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,5,4)) 

Error: Description: "Invalid character value for cast specification". Exceptions for text file where I needed two derived column tasks, the first checks if the Date was blank and puts a NULL string. The second takes this string and converts it to a date:
-- Assuming EndDate is optional (can be null) and in format DDMMYYYY:

-- Derived column task 1: Replace 'Date'
TRIM(Date) != "" ? TRIM(Date) : "00000000"

-- Derived column task 2: Add as new Column 'DC_Date' 
-- (note this is a separate task in the data flow to the previous one):
(ISNULL(Date) || (TRIM(Date) == "00000000")) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")

So although your SSIS IDE (Visual Studio?) will give you a useless Microsoft message saying something like:
Error: Data conversion failed while converting column "This_Date" (3833) to column "This_Date" (3932).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Extract dates from string dates" (3614)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DC_This_Date" (3833)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
All this blab actually means is that there is a chance one of your date values is BLANK, NULL or contains a non-numerical value in a number such as COMMA (,) and therefore the parsing (to extract year for example) will fail and stop the task (based on default settings).

-- Take a "dd/mm/yyyy" string and convert to date (when (DT_DATE)ThisDate by itself doesn't work)
(DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4))

Known Issues
[Convert Date [4228]] Error: An error occurred while attempting to perform a type cast.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Convert Date" (4228) failed with error code 0xC0209029 while processing input "Derived Column Input" (4229). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

-- often when processing a datasource, the function is being applied to a NULL date timestamp.

Dealing with Date NULLs:
Fix: Put conditional statements to account for NULLs.
-- Standard
TRIM(This_Date) == "" ? NULL(DT_DATE) : (DT_DATE)This_Date

-- if data source column is of datatype string and european date format: return SQL date
(TRIM(This_Date) == "") ? (DT_DATE)"1900-01-01" : (DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4))

-- if data source column is of datatype string and european date format: return SQL datetime
(TRIM(This_Date) == "") ? (DT_DBTIMESTAMP)"1900-01-01" : (DT_DBTIMESTAMP)( SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00.000")

-- if nulls
ISNULL(This_Number) ? 0 : This_Number
ISNULL(This_String) ? "" : This_String
ISNULL(This_Date) ? NULL(DT_DATE) : (DT_DATE)This_Date
ISNULL(This_Date) ? "" : (DT_STR)This_Date

-- if blanks
LTRIM(This_String)=="" ? "" : LTRIM(This_String)
LTRIM(This_Date)=="" ? (DT_DATE)"1900-01-01" : (DT_DATE)This_Date

-- last column when datasource is flat file (account for line break):
ISNULL(Last_Column) ? "" : TRIM(Last_Column)

Last Troubleshooting Check:
The problems here relate to when our datasource was a flat file connection. We had to change the Format under General to Ragged Right (I had been setting it to "fixed width" but then got problems with it not understanding the last two characters on the row (CR & LF).

Fixes of interest:

-- assuming This_Decimal="00000.00" or "123456.78"  (Do NOT use "Replace" and instead "Add as new column")

-- assuming This_Numeric="0000000.0000" or "1234567.8901"
LTRIM(This_Numeric) == "" ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(This_Numeric)

-- further?
ISNULL(ContributionValue) || (LTRIM(ContributionValue)=="") ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(ContributionValue)

-- it's a number

-- a thousandth separator in the data (eg. 2,534.0000)?
ISNULL(ContributionValue) || (LTRIM(ContributionValue) == "") ? "0.0000" : TRIM(REPLACE(ContributionValue,",",""))

My Most Frequent Fix:
The data files I have to work with are rarely consistent. On some rows the date column will have a string of 8 characters long in the format DDMMYYYY and on other rows, this is blank with spaces instead, I still get the Error: The conditional operation failed.
-- I've been given data as a Flat File with fixed columns (Ragged Right).  
-- "Date" is the last column and can be blank in the source.
-- "Date" must default to current date if blank for the target database (cannot be NULL).

-- assuming date could be blank (has spaces) or in format DDMMYYYY
(TRIM(Date) == "") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")

-- Yields:
-- [MyDerivedColumnTask1 [4228]] Error: The conditional operation failed.


-- a) create a first derived columns task that checks if the column is blank and defaults a value (Replace current column if you like)
(TRIM(Date)=="") ? "00000000" : TRIM(Date)

-- b) then a second derived columns task after which checks if the value is "00000000"
(TRIM(Date) == "00000000") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")

Date back into String
-- Converting yyyy-mm-dd hh:ii:ss to ddmmyyyy
RIGHT("00" + (DT_STR,2,1252)DATEPART("dd",DateVerified), 2) 
+ RIGHT("00" + (DT_STR,2,1252)DATEPART("mm",DateVerified), 2) 
+ (DT_STR,4,1252)DATEPART("yyyy",DateVerified)

Add comment



0 JustinAnderson Wednesday, 8th April 2015, 6:40 pm
Hey Joel, I have a doozy for ya!

what if your date is a string like this...

Wed, Apr 1, 2015 12:00 AM
0 JustinAnderson Friday, 10th April 2015, 3:56 pm
I actually figured it out afer much googling. I needed to add several differnt derived columns but I got it to work with something like this...
Started here
Wed, Apr 1, 2015 12:00 AM
datestring = SUBSTRING([colname],5,50)
created another derived column object with these
month = UPPER(SUBSTRING(LTRIM(datestring),1,3))

day = RIGHT("0" + SUBSTRING(dates tring,1,FINDSTR ING(datestring, ",",1) - 1),2)

year = SUBSTRING(dates tring,FINDSTRIN G(datestring,", ",1) + 1,5)

then another derived with this

([month] == "JAN") ? "01" : ([month] == "FEB") ? "02" : ([month] == "MAR") ? "03" : etc...running out of space
then the finally: date = (DT_DBTIMESTAMP )(year + "-" + MM + "-" + day)

Hope this helps anyone else having that issue. :P
0 Joel L Saturday, 11th April 2015, 6:53 am
Nice one Justin!

Personally I'd have used a SQL task (either T-SQL or MySQL would do) and used SQL to get the DATEPART() or MONTH() functions respectively. Work would have gotten me to create an SSIS component and do it with a C# script. If you're not using a database and instead just flat files, then the method of parsing the date and using ternary operators to convert the month name, is probably the recommended solution.
0 Jason A Tuesday, 10th March 2015, 5:14 pm
Where do you place this logic? This this all go into the Expression field? Can this be called for multiple Derived Columns from a single source?
+1 Joel L Wednesday, 11th March 2015, 10:07 am
Hi Jason,

Yes these go into the expression field and set the output to a different named variable. If you have a single source, then are you using multiple derived columns to send to multiple destinations? This article was to deal with the usual Date conversion errors I get in SSIS when reading from a text file.