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!
Add to Technorati Favorites
Like this:
Like Loading...
Published by Jen Stirrup
Jennifer Stirrup is the Founder and CEO of Data Relish, a UK-based AI and Business Intelligence leadership boutique consultancy delivering data strategy and business-focused solutions. Jen is a recognized leading authority in AI and Business Intelligence Leadership, a Fortune 100 global speaker, and has been named as one of the Top 50 Global Data Visionaries, one of the Top Data Scientists to follow on Twitter and one of the most influential Top 50 Women in Technology worldwide.
View all posts by Jen Stirrup