SSIS: Flat File Source - Datetime Column Format Issue & Solution

1.0 Introduction

       
Flat files as Source are widely used in SQL Server Integration Services Tool. This article will discuss about one common issue with Flat File Date time Columns. (Details can be found in this SSIS MSDN Thread SSIS FlatFile Source Datetime Column Format Issue).

2.0 Problem Definition

            
Below is the Source data from txt file

 (Note: DOB column (Datatype: DT_DBTIMESTAMP) has two date formats (DD/MM/YYYY & MM/DD/YYYY).)

        And when the above file extract the above file, data in the table looks like below.

    Observations:

• I expected the 06/26/1988 value extraction to fail because of the poor data quality (It should have been 26/06/1988)

• I wanted 03/04/2013 value to be interpreted as 3rd April 2013, but it is inserted as 4th March 2013.

3.0 How does it work

        
        SSIS expects every date value to be in MM/DD/YYYY format & if it has an issue converting, then it assumes it to be in DD/MM/YYYY format. Hence 20/06/1988 didn’t fail & 03/04/2013 inserted as 4th March 2013.

        If it couldn’t convert a date value to either of the formats (DD/MM/YYYY & MM/DD/YYYY), for example, 13/13/2008, then it throws an error “Data conversion failed: The value could not be converted because of a potential loss of data."

(Note: The above behavior is the same even if you use a Data conversion Transformation.)

4.0 Solution

                
        We can overcome this default behavior of SSIS by declaring the Flat file source Datetime column datatype as String & Using DateTime.ParseExact () Function of C# to convert the string value to a specific Date format. (If the conversion fails, the error rows can be redirected to Error File. (Like Below)

Override the Input0_ProcessInputRow Function in the script component

do
          { 
              try
              { 
                  String Name = Row.Name; 
                  Int32 Age = Row.Age; 
                  Decimal Salary = Row.Salary; 
                  DateTime DateofB = DateTime.ParseExact(Row.DOB.ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture); 
               
                  Output0Buffer.AddRow(); 
                  Output0Buffer.Age = Row.Age; 
                    Output0Buffer.Name = Row.Name; 
                  Output0Buffer.Salary = Row.Salary; 
                  Output0Buffer.DateofB = DateofB; 
              }             
              catch(Exception E) 
              { 
                  ErrorBuffer.AddRow(); 
                  ErrorBuffer.Name = Row.Name; 
                  ErrorBuffer.DOB = Row.DOB; 
                  ErrorBuffer.ErrorDesc = E.Message; 
              } 
        } 
  
        while (Row.NextRow()); 
  
        if (Row.EndOfRowset()) 
          { 
              Output0Buffer.SetEndOfRowset(); 
          } 

 Note: The Date format in the above code (Highlighted in Blue) can be sourced from Package configurations (SSIS String Variable). More about the format strings can be found here.

---

See Also

List of Award Winning TechNet Guru Articles
SQL Server Integration Services Portal