Data flow task fails at source because of datetime conversion? SSIS 2008

Question

Ive seen this type of error before (eg truncation) with strings before but not with datetime fields.

Have a Data flow task that seems to fail at the source. The OLEDB data source is a call to a procedure and among the columns of the resultset is a datetime field GAPPOSTDT. The return value is a datetime and the procedure returns just fine with the expected results. Not so when I run this through the data flow task. Looking at the advanced properties of the oledb source, I see the type for this field set to database timestamp [DT_DBTIMESTAMP] which seems right.

What could be causing this field not to get mapped?

Ive tried simply deleting the dataflow task and recreating it. Same issue.

See error message below.

Error: 0xC020901C at Data Flow Task, OLE DB Source [1]: There was an error with output column "GAPOSTDT" (61) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "GAPOSTDT" (61)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "GAPOSTDT" (61)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[UPDATE #1]
SSIS 2008

So I changed the procedure output to return varchar(10) instead of datetime. The OLEDB Source in the dataflow now errors with the following

Error: 0xC020901C at Data Flow Task, OLE DB Source [1]: There was an error with output column "GAPOSTDT" (61) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". Error: 0xC020902A at Data Flow Task, OLE DB Source [1]: The "output column "GAPOSTDT" (61)" failed because truncation occurred, and the truncation row disposition on "output column "GAPOSTDT" (61)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Im now suspecting some "goofy" characters in the data. The collation being used in the source system is SQL_Latin1_General_CP1_CI_AS

[UPDATE #2]
Ok, I think I may have found the issue. In my procedure I have a "dummy" result because my procedure uses temp tables and this is one "work around" with SSIS (see other topics on this). My final result and the dummy result had their columns in the wrong order. So this put data into the wrong columns. I noticed this when I put on a data viewer. After it popped up, I noticed data into the wrong columns. Strange I thought, then after reviewing my procedure, I found the culprit.

Answer 1

There are always problems with datetime fields... ;)

Try to convert this field to string in the query on the source and then apply changes needed with Derived Column Transformation. Where I live we use date format dd.mm.yyyy, but I'm getting at least 3 other different formats of date...

Maybe this is not the best answer but it's worth to try... it suits me well... :)

Answer 2

That is because the data type of input column does not match the destination column well. You mentioned that you have seen that before fot the varchar column, that is because the size(data length) of input record for that column is smaller than the destination, if input is varchar(30), destination is varchar(20), that will cause this issue. Check your datatype, I am guessing there may have some conflicts when you are trying to do with datetime and datetime2, or datetimeoffset

for example:

input varchar(30)   destination varchar(30)  perfectly match
input varchar(20)   destination varchar(30)  fine but a warning
input varchar(30)   destination varchar(20)  if actual is smaller than 20, that is fine, but if bigger than 20, cause error