Anyone that can help solving the following problem. Need to load date values into SQL 2005 with SSIS:
The format of the source data is the following: "1997-09-01" [DateStringColumn; varchar (10)] Need to convert this to smalldatetime datatype in this format (yyyy-mm-dd)
My dataflow in SSIS contain the following query:
Preview option in the dataflow (source editor) display the results still in this format: 9/1/19997 12:00:00 AM (i.e. m/d/yyyy format)
Problem: I now receive an error with the following message "conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value" - error in the SSIS dataflow task; when loading this to the destination DateResultColumn [with smalldatetime datatype]
Also note: The DateStringColumn does not contain any dates prior to 1900/01/01; these were allready set to a default value of 1900-01-01 if the original values was e.g. 0001-01-01.
Any help will be much appreciated. Regards, SQLBeginner587
asked Feb 10 '10 at 08:57 AM in Default
It's possible that you have data in the database that isn't date compliant. You could run a quick validation against the data using:
If you have to, you put in an error handler in the SSIS stream to dump the bad values out to an error table.
answered Feb 10 '10 at 09:17 AM
Grant Fritchey ♦♦
have you tried simply removing the - characters with
answered Feb 10 '10 at 09:02 AM
You should configure the error output of the Source to divert the rows that cause errors to the error output. Place a Multicast, Union All, Row Count, or other meaningless transform on the error output, and a Data Viewer on the connector. Now you'll know exactly what kind of values you're dealing with.
Second, the date WITH dashes is the ISO standard format. Without separators, the string is completely ambiguous. The 120 in the CAST statement specifically identifies the format as yyyy-mm-dd anyway.
answered Feb 10 '10 at 01:16 PM