'SSIS loading data error

I have a CSV file which contains this data:

EmployeeCode,EmployeeName,EmployeeSalary,Date
101,raju,1000,2/2/2003
102,krish,100,3/4/2005
103,rishbh,320,12/9/2007
104,rani,4690,12/8/2008
105,olesia,2000,17/4/2009
106,olga,2000,12/6/2010
107,mellisa,3330,12/4/2011

And I have table called employees:

EmployeeCode nvarchar(50)
EmployeeName nvarchar(50)
EmployeeSalary money
Date datetime

When I try to load this CSV file into my table using the SSIS package it gives me an error

1) [ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type date of the specified target column.

2) [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (2) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (9). 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.

However If I remove date column from the CSV and try to insert everything works well. What is wrong with date column, why is it not taking the values?

sql-serverssisbusiness-intelligencemsbi


Solution 1:[1]

Use OLE DB Destination rather than ADO NET Destination

This issue is due to default datatype of flat file is picked "string [DT_STR] " which is equal to varchar in database , but you have made your destination table data type nvarchar is equal to Unicode string [DT_WSTR] .

Either using derived column Change data type string [DT_STR] to Unicode string [DT_WSTR]

or

Go to show advance editor (source ) >> Input and output Properties output columns >> DAta type Property >> change data type to Unicode string [DT_WSTR] for EmployeeCode and EmployeeName.

Solution 2:[2]

First of all you should use OLE DB destination instead of ADO.Net Destination if your table is huge and now come to issue ,in your table data type of date is as 'datetime' so either you have to change your input data to date time by using Right click on component and go to advance editor. Otherwise you can data conversion between source and destination and convert date as "DB_Datetime".I hope you will not face any issue when you do accordingly.

Solution 3:[3]

I had the same issue and my answer is going to sound silly. I struggled with it for 2 days and ended up realizing that the date format was wrong in my CSV file.

for example:

105,olesia,2000,17/4/2009 -- this is invalid because it ends up taking 17 as a month and its invalid

blew my mind when I realized it! Hope this helps anyone else going forward!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Neharika Tammana

Related Questions