I’m currently using SSIS to import data into Replicon, editions of timesheet software that track time and expenses for attendance, payroll, project costing and client billing. As expected, there are always some opportunities to pick up experience along the way.
For some columns, Replicon expects the imported date format to be in a strict date format, i.e. 2010-10-01., or in other words, the date part only. In previous versions of SQL Server, this would mean whittling the time portion from the normal SQL Server datetime datatype, and inserting the remaining date format into Replicon.
The good news is SQL Server 2008, there is a nice new date format which is simply called DATE. This is much more readable, and here is an example of it below:
select CONVERT(date, getdate())
For today, 20th January 2010, this would provide the following result:
2010-01-20
Then it is possible to use this new DATE format to extract data from the source system, and use SSIS to write the information to a CSV file. However, when the information is written to a CSV file, the output to the CSV file might have the time portion added back into it, for example:
2010-01-20 00:00:00:000
Why does this occur? Well, if the output column type is set to anything else other than DT_DATE, then the output will include the time as well as the date. This holds true even if the original SQL query, extracting the data, takes the DATE format; when exporting to a CSV file, SSIS will just add the time portion to the end of the date portion to provide a datetime format.
This makes sense, since the resulting target CSV has been configured to provide a datetime format. So, SSIS is simply doing what it has been asked to do: it adds a default HH:MM:SS:ms is added to the date format to ensure that the CSV file is created in accordance with the configured column date types.
For Replicon, the date format YYYY-MM-DD HH:MM:SS:ms is invalid since it expects the date only, i.e. YYYY-MM-DD. So, this part of the import would fail.
So, it is important to ensure that the DATE format is preserved when the output is written to a CSV file. In the Flat File Connection Manager Editor, it is important to double-check that the column data type for the DATE is set to date (DT_DATE). A sample image of this requirement is provided below, and if it helps, please click on the image if you need to a larger image:
Hope that helps!
Jen Stirrup 