locked
converted date source to destination fails in ssis, not in ssms RRS feed

  • Question

  • Hi we run 2012 ssis enterprise.  And 2016 enterprise for the db engine.  The column you see below is in an oledb source component's cte.  The select from that cte in the oledb source is coalesce(minCreatedOn,'1/1/1900') EffectiveStartDate.  CreatedOn is data type Date.  CreatedTime is Time(7).  I found the dateadd expression on the internet as a solution to adding a time to a date.  The destination is of data type datetime2(7).  From what I've seen, the dateadd expression does what it is supposed to.

    min(dateadd([day],DATEDIFF([day],'19000101',CreatedOn),cast(CreatedTime asdatetime2(7))))minCreatedOn

    Sometimes the dataflow that contains the source and destination works.   Sometimes it doesn't.  Presumably because there is some value that can appear in the dataset (not always) that triggers a (metadata?) bug in ssis.  When it doesn't work, I get the error you see below.  On a population that causes failure in ssis, I have no problem inserting the data directly into the target table using ssms and the exact same query I use in the oledb source.  We've seen a similar ssis problem that involved cast of a dateoffset field that works on some values and fails on others.  Depending on where this post goes, i'll post the exact cast expression and data types involved there.

    [OLE DB Source [1]] Error: There was an error with output column "EffectiveStartDate" (74) 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.".

    [OLE DB Source [1]] Error: The "output column "EffectiveStartDate" (74)" failed because truncation occurred, and the truncation row disposition on "output column "EffectiveStartDate" (74)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

       
    Friday, September 29, 2017 12:45 PM

Answers

  • thx, its a date.  I don't think that is relevant.

    But you have specified the length  like this datetime2(7).

    You need to increase this.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by ArthurZ Monday, October 2, 2017 2:33 AM
    • Marked as answer by db042190 Tuesday, October 3, 2017 11:34 AM
    Friday, September 29, 2017 1:47 PM
  • not exactly but I think you are on to something kapil.   I looked at the output and external meta data in the oledb source's advanced editor thx to your first post.  And saw something surprising.  The "output columns" data type attribute was dt_str length 8.  I went and created an exact duplicate of that oledb source (no destination) from scratch and was surprised that this time the same "output columns" attribute  was db timestamp with precision dt_dbtimestamp2.

    here is what I think happened.  At one time that ssis source select had only '1/1/1900' hardcoded for that column.  The additional cte and date logic was added later.  I'm guessing ssis doesn't always do what it is supposed to do on a change like that.  ie make that output columns attribute the same as it would have made it had the source been coded that way from the beginning.

    I ran the DFT the new way and it worked.  This still doesn't explain why it worked at least once before the wrong way but I don't recall the exact circumstances so I wont pursue that question without being able to recreate that situation.

    • Marked as answer by db042190 Tuesday, October 3, 2017 11:34 AM
    Friday, September 29, 2017 3:14 PM

All replies

  • Hello,

    Did you check the length of your datatype is equal at both source and destination?

    From the error it seems the size is not equal and it result at truncation.

    Increase the length of datatype and it will work.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by Kapil.Kumawat Friday, September 29, 2017 1:07 PM
    • Unproposed as answer by db042190 Friday, September 29, 2017 1:37 PM
    Friday, September 29, 2017 1:07 PM
  • thx, its a date.  I don't think that is relevant.
    Friday, September 29, 2017 1:37 PM
  • thx, its a date.  I don't think that is relevant.

    But you have specified the length  like this datetime2(7).

    You need to increase this.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by ArthurZ Monday, October 2, 2017 2:33 AM
    • Marked as answer by db042190 Tuesday, October 3, 2017 11:34 AM
    Friday, September 29, 2017 1:47 PM
  • not exactly but I think you are on to something kapil.   I looked at the output and external meta data in the oledb source's advanced editor thx to your first post.  And saw something surprising.  The "output columns" data type attribute was dt_str length 8.  I went and created an exact duplicate of that oledb source (no destination) from scratch and was surprised that this time the same "output columns" attribute  was db timestamp with precision dt_dbtimestamp2.

    here is what I think happened.  At one time that ssis source select had only '1/1/1900' hardcoded for that column.  The additional cte and date logic was added later.  I'm guessing ssis doesn't always do what it is supposed to do on a change like that.  ie make that output columns attribute the same as it would have made it had the source been coded that way from the beginning.

    I ran the DFT the new way and it worked.  This still doesn't explain why it worked at least once before the wrong way but I don't recall the exact circumstances so I wont pursue that question without being able to recreate that situation.

    • Marked as answer by db042190 Tuesday, October 3, 2017 11:34 AM
    Friday, September 29, 2017 3:14 PM