How do I fix Invalid character value for cast specification on a date column in flat file

Question

I have a CSV file with a  LF  delimiting each row and a date column with the date format as  12 20 2010   including quotation marks   My destination column is a SQL Server 2008 database table of type date  not datetime   In my Flat File Connection Manager  I have configured the date column to be data type date  DT DATE  with TextQualified set to true and the column delimiter as  LF   it is the last column on each row   I have the text qualifier set to    When I try to load this into an OLE Destination I get the following error      TRN DORPS  760   Error  SSIS Error Code DTS E OLEDBERROR   An OLE DB error has occurred  Error code  0x80004005    An OLE DB record is available   Source   Microsoft OLE DB Provider for SQL Server   Hresult  0x80004005  Description   Invalid character value for cast specification       TRN DORPS  760   Error  There was an error with input column   CYCLE DATE    874  on input  OLE DB Destination Input   773   The column status returned was   The value could not be converted because of a potential loss of data      If I attach a data viewer  the value in the pipeline is 2010-12-20 00 00 00 0000000 - is this time component what s causing the problem  I try to strip out the time component with  DT DATE  DT DBDATE  CYCLE DATE  but to no avail as it stays the same in the pipeline

User · Accepted Answer

I was ultimately able to resolve the solution by setting the column type in the flat file connection to be of type "database date [DT_DBDATE]"

Apparently the differences between these date formats are as follow:

DT_DATE A date structure that consists of year, month, day, and hour.

DT_DBDATE A date structure that consists of year, month, and day.

DT_DBTIMESTAMP A timestamp structure that consists of year, month, hour, minute, second, and fraction

By changing the column type to DT_DBDATE the issue was resolved - I attached a Data Viewer and the CYCLE_DATE value was now simply "12/20/2010" without a time component, which apparently resolved the issue.

User · Answer

The proper data type for  2010-12-20 00 00 00 0000000  value is DATETIME2 7    DT DBTIME2      But used data type for CYCLE DATE field is DATETIME - DT DATE  This means milliseconds precision with accuracy down to every third millisecond  yyyy-mm-ddThh mi ss mmL where L can be 0 3 or 7    The solution is to change CYCLE DATE date type to DATETIME2 - DT DBTIME2

User · Answer

In order to simulate the issue that you are facing  I created the following sample using SSIS 2008 R2 with SQL Server 2008 R2 backend  The example is based on what I gathered from your question  This example doesn t provide a solution but it might help you to identify where the problem could be in your case   Created a simple CSV file with two columns namely order number and order date  As you had mentioned in your question  values of both the columns are qualified with double quotes     and also the lines end with Line Feed   n  with the date being the last column  The below screenshot was taken using Notepad    which can display the special characters in a file  LF in the screenshot denotes Line Feed     Created a simple table named dbo Destination in the SQL Server database to populate the CSV file data using SSIS package  Create script for the table is given below   CREATE TABLE  dbo   Destination        OrderNumber   varchar  50  NULL       OrderDate   date  NULL   ON  PRIMARY  GO   On the SSIS package  I created two connection managers  SQLServer was created using the OLE DB Connection to connect to the SQL Server database  FlatFile is a flat file connection manager     Flat file connection manager was configured to read the CSV file and the settings are shown below  The red arrows indicate the changes made    Provided a name to the flat file connection manager  Browsed to the location of the CSV file and selected the file path  Entered the double quote     as the text qualifier  Changed the Header row delimiter from  CR  LF  to  LF   This header row delimiter change also reflects on the Columns section     No changes were made in the Columns section     Changed the column name from Column0 to OrderNumber     Changed the column name from Column1 to OrderDate and also changed the data type to date  DT DATE     Preview of the data within the flat file connection manager looks good     On the Control Flow tab of the SSIS package  placed a Data Flow Task     Within the Data Flow Task  placed a Flat File Source and an OLE DB Destination     The Flat File Source was configured to read the CSV file data using the FlatFile connection manager  Below three screenshots show how the flat file source component was configured         The OLE DB Destination component was configured to accept the data from Flat File Source and insert it into SQL Server database table named dbo Destination  Below three screenshots show how the OLE DB Destination component was configured         Using the steps mentioned in the below 5 screenshots  I added a data viewer on the flow between the Flat File Source and OLE DB Destination             Before running the package  I verified the initial data present in the table  It is currently empty because I created this using the script provided at the beginning of this post     Executed the package and the package execution temporarily paused to display the data flowing from Flat File Source to OLE DB Destination in the data viewer  I clicked on the run button to proceed with the execution     The package executed successfully     Flat file source data was inserted successfully into the table dbo Destination     Here is the layout of the table dbo Destination  As you can see  the field OrderDate is of data type date and the package still continued to insert the data correctly     This post even though is not a solution  Hopefully helps you to find out where the problem could be in your scenario

[sql-server] How do I fix 'Invalid character value for cast specification' on a date column in flat file?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to etl

Examples related to ssis