Flat files as Source are widely used in SQL Server Integration Services Tool. This article will discuss one common issue with Flat File Date time Columns.
Below is the Source data from txt file
Note: DOB column (Datatype: DT_DBTIMESTAMP) has two date formats (DD/MM/YYYY & MM/DD/YYYY).
When information is extracted the above file, the data in the table looks like below.
SSIS expects the 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 same even if you use a Data conversion Transformation.
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(),
"yyyy-MM-dd HH:mm:ss,fff"
, 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 Yellow) can be sourced from Package configurations(SSIS String Variable).
Naomi N edited Revision 1. Comment: Title change
Naomi N edited Original. Comment: Minor change, more tags