Tuesday, September 02, 2014
   
Text Size
Login

SSIS Convert a string into a date

What?
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).

Why?
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.

How?
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:

  1.  -- Assuming [This_Date]="21/03/2012" 
  2.  (DT_DATE)(This_Date) 
  3.   
  4.   
  5.  -- Assuming [This_Date]="20120321" (YYYYMMDD) 
  6.  (DT_DATE)(SUBSTRING(This_Date,6,2) + "/" + SUBSTRING(This_Date,9,2) + "/" + SUBSTRING(This_Date,1,4)) 
  7.    
  8.   
  9.  -- Assuming [This_Date]="21032012" (DDMMYYYY) 
  10.  (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") 
  11.   
  12.   
  13.  -- Explicit: Assuming [StartDate]="21032012" (DDMMYYYY) 
  14.  (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:

  1.  -- Assuming EndDate is optional (can be null) and in format DDMMYYYY: 
  2.   
  3.  -- Derived column task 1: Replace 'Date' 
  4.  TRIM(Date) != "" ? TRIM(Date) : "00000000" 
  5.   
  6.  -- Derived column task 2: Add as new Column 'DC_Date'  
  7.  -- (note this is a separate task in the data flow to the previous one): 
  8.  (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:

  1.  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.". 
  2.   
  3.  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).

Additional

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

Known Issues

  1.  [Convert Date [4228]] Error: An error occurred while attempting to perform a type cast. 
  2.   
  3.  [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. 
  4.   
  5.  -- 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.

  1.  -- Standard 
  2.  TRIM(This_Date) == "" ? NULL(DT_DATE) : (DT_DATE)This_Date 
  3.   
  4.  -- if data source column is of datatype string and european date format: return SQL date 
  5.  (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)) 
  6.   
  7.  -- if data source column is of datatype string and european date format: return SQL datetime 
  8.  (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") 
  9.   
  10.  -- if nulls 
  11.  ISNULL(This_Number) ? 0 : This_Number 
  12.  ISNULL(This_String) ? "" : This_String 
  13.  ISNULL(This_Date) ? NULL(DT_DATE) : (DT_DATE)This_Date 
  14.  ISNULL(This_Date) ? "" : (DT_STR)This_Date 
  15.   
  16.  -- if blanks 
  17.  LTRIM(This_String)=="" ? "" : LTRIM(This_String) 
  18.  LTRIM(This_Date)=="" ? (DT_DATE)"1900-01-01" : (DT_DATE)This_Date 
  19.   
  20.  -- last column when datasource is flat file (account for line break): 
  21.  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:

  1.  -- assuming This_Decimal="00000.00" or "123456.78"  (Do NOT use "Replace" and instead "Add as new column") 
  2.  (DT_DECIMAL,2)TRIM(This_Decimal) 
  3.   
  4.   
  5.  -- assuming This_Numeric="0000000.0000" or "1234567.8901" 
  6.  LTRIM(This_Numeric) == "" ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(This_Numeric) 
  7.   
  8.   
  9.  -- further? 
  10.  ISNULL(ContributionValue) || (LTRIM(ContributionValue)=="") ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(ContributionValue) 
  11.   
  12.   
  13.  -- it's a number 
  14.  (DT_NUMERIC,12,4)TRIM(ContributionValue) 
  15.   
  16.   
  17.  -- a thousandth separator in the data (eg. 2,534.0000)? 
  18.  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.

  1.  -- I've been given data as a Flat File with fixed columns (Ragged Right).   
  2.  -- "Date" is the last column and can be blank in the source. 
  3.  -- "Date" must default to current date if blank for the target database (cannot be NULL). 
  4.   
  5.   
  6.  -- PROBLEM: 
  7.  -- assuming date could be blank (has spaces) or in format DDMMYYYY 
  8.  (TRIM(Date) == "") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00") 
  9.   
  10.  -- Yields: 
  11.  -- [MyDerivedColumnTask1 [4228]] Error: The conditional operation failed. 
  12.   
  13.   
  14.  -- SOLUTION: 
  15.   
  16.  -- a) create a first derived columns task that checks if the column is blank and defaults a value (Replace current column if you like) 
  17.  (TRIM(Date)=="") ? "00000000" : TRIM(Date) 
  18.   
  19.  -- b) then a second derived columns task after which checks if the value is "00000000" 
  20.  (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

  1.  -- Converting yyyy-mm-dd hh:ii:ss to ddmmyyyy 
  2.  RIGHT("00" + (DT_STR,2,1252)DATEPART("dd",DateVerified), 2)  
  3.  + RIGHT("00" + (DT_STR,2,1252)DATEPART("mm",DateVerified), 2)  
  4.  + (DT_STR,4,1252)DATEPART("yyyy",DateVerified) 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Quicklist Weblinks (JQW)

    • Mon 01-Sep-14
      Hi from France Download's link for the J3.1.1 version of JQW is broken ... have a nice day, Thanks ...
      tompouce33
  • Joes Word Cloud (JWC)

    • Sat 23-Aug-14
      Salut Joe, Thank you again for helping me. I had installed V2.2 for Joomla 2.5. Finally, as you ...
      Nathalie  
    • Wed 20-Aug-14
      Salut Nathalie! I could only access the under construction page of your website, so I'm not 100% sure.
      Webmaster  
    • Tue 19-Aug-14
      Hi Joe, Thank you for your prompt reply. Below, an "image" of the cloud on my website : "pour ante ...
      Nathalie  
    • Mon 18-Aug-14
      Hi Nathalie, Thanks for the message. The module does not see Virtuemart text but that sounds like ...
      Webmaster