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

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

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.

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.