SSIS Date to String Conversion Error

Question

I'm pretty new to SSIS and I'm running into an issue where I'm using a sql statement in an OLE DB Source step to pull my data and this data contains 5 DATETIME fields that need to be converted and all of the data is going into a flat file.

I'm doing the conversions in sql like so:

REPLACE(CONVERT(VARCHAR(10), birthdate, 101), '/', '') AS 'Date of Birth (MMDDYYYY)

In the SSIS Flat File Connection Manager my DataType is string [DT_STR] and my OutputColumnWidth is 8. This is working in SSIS without truncation as the above conversion creates an 8 character varchar. If I try converting to a varchar(8) it truncates the data in sql server management studio.

My problem is that my next conversion attempt is failing. Here's the sql code:

REPLACE(CONVERT(VARCHAR(10), exp_date, 103), '/', '') AS 'Expiration Date

I get the following error: "The value could not be converted because of a potential loss of data" even though everything has been reproduced with the same settings. This conversion converts the date to a 'DDMMYYYY' format.

I know from researching that this is a data conversion error but I have no idea how to get around it especially with the first conversion working correctly.

The interesting thing is that when I redirect the rows on error to a different flat file the dates get pulled and converted with no problem at all.

I'm at a loss here and would appreciate any help from the group.

Any reason why you are using '101' MM/DD/YYYY format for the first and '103' DD/MM/YYYY format for the second? (not that that should cause an issue!) — William Salzman
That's what's being requested by the department the flat file is being sent to. I'm in the process of converting automated reports that have run on Power Builder for years (not my doing) over to SSIS and this was the original format. — kdejarnet
I just looked at the metadata and found that the datatype and length for the 2nd conversion is set to DT_DBTIMESTAMP with a length of 0. I'm figuring this is why it's erroring though I'm not quite sure just yet how to change this. Any suggestions? — kdejarnet
I figured out why it wasn't working. When I first created the package only the birthdate was converted since that's where my attention was due to the different date format. After I ran it and verified that all of the data was being pulled I realized that I needed to convert the other 4 dates as well. The problem is that even though I made the change in sql and in the Flat File Connection Manager the metadata was still set to the original settings for those 4 fields. I recreated the step and everything converted correctly. Thanks for taking the time to respond. — kdejarnet
Good research. You should write that up and post it as an answer so that others can benefit. — William Salzman

Unknown Unknown · Accepted Answer · 2012-12-04T15:56:45

I figured out why it wasn't working. When I first created the package only the birthdate was converted since that's where my attention was due to the different date format. After I ran it and verified that all of the data was being pulled I realized that I needed to convert the other 4 dates as well. The problem is that even though I made the change in sql and in the Flat File Connection Manager the metadata was still set to the original settings for those 4 fields. I recreated the step and everything converted correctly. Thanks for taking the time to respond

SSIS Date to String Conversion Error

1 Answers