How do I import mm/dd/yyyy values in a file to a database field of format yyyy-mm-dd?
ssis
Solution
If the incoming values from the CSV file are always formatted like `04/20/2012` (`MM/DD/YYYY`), then you don't have to perform any type casting. You just have to configure the flat file connection manager to treat the values in the file as `date` data type.
Let's assume that your CSV file looks something like this with a single column containing dates in .
In the SSIS package, create a flat file connection manager to read the CSV file. I stored the CSV in the path `C:\temp\Source.csv`
On the `Advanced` section, you will notice that the flat file connection manager named the first column as `Column1` and the DataType property is set to `string [DT_STR]`. However, the values in the file are actually dates. Either you can manually configure the data types or click on the `Suggest Types...` button.
On the Suggest Column Types, leave the default values and click OK. This will read the first 100 rows of the file and will determine the column type according to the data available in the file.
Once you click `OK` on the Suggest Column Types dialog, you will notice that the data type on the flat file connection manager for the `Column 0` has been changed to `date [DT_Date]`. Click OK to configure the flat file connection manager. You can also rename the column according to your requirements (say InvoiceDate or OrderDate etc.)
Now that you have the flat file connection manager configured, you can use it inside a Flat file source within a data flow task to read the data and populate your database. So, there is no need to manipulate values using a derived column transformation.
However, if your incoming file values are in string like `120420 (YYMMDD)`, these values cannot be configured as date data types. In these scenarios, you need to use Derived Column transformation as suggested in this answer.
Hope that helps.
Problem
I have a CSV file with date column in the following format mm/dd/yyyy (4/20/2012), and I need to load this column into a database that has datetime column in yyyy-mm-dd (2012-04-20). I have used derived column transformation for this purpose and written and expression like ``` (DT_WSTR)(SUBSTRING(ReceivedDateTime,1,4) + "-" + SUBSTRING(ReceivedDateTime,5,2) + "-" + SUBSTRING(ReceivedDateTime,7,2)) ``` Upon running my package, it's throwing the error `Unable to perform type cast`.