none
OLEDB Destination Fast Load Auto Truncation

    Question

  • I have one Data Flow task with OLEDB Source(for SQL Server) and OLEDB Destination(for SQL Server).

    In OLEDB destination I have used Fast Load option. Also I handled error rows to move to error table.

    In my source I have with columns column1(int) , Column2(Varchar(10)).

    Destination has two column column1(int) , Column2(Varchar(7)).

    Error table has 4 columns column1(int) , Column2(Varchar(10)), Error number(nvarchar(max)), Error Description(varchar(max))

    In source, column2 values are more than 7 charactors.

    When I run the package, destination loading only with first 7 charactors for Column2.Truncation happening for remaining charactors.I dont want to do like this.If the destination length is suufficient then the entire row should be moved to my error table.

    When I check Error tab option of destination Even Truncate also in disabled state to enable the redirect row.

    Tuesday, January 13, 2015 2:12 PM

Answers

  • Truncation must raise an error and the error rows should redirect in your scenario, so what you described does not make sense.

    No, when writing to SQL Server the data will be truncated, without error.
    The only thing you get is a warning when designing the package.

    You get truncation errors when you try to put data longer than the column width in the data flow buffer, i.e. at the source or at transformations, but not at the destination apparently.

    @Prabu: you can check the length using a conditional split. If the lenght is too long, redirect the row yourself to the error table.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Tuesday, January 13, 2015 9:57 PM

All replies

  • Truncation must raise an error and the error rows should redirect in your scenario, so what you described does not make sense.

    Perhaps you overlooked something, e.g. there is trimming of data happening somewhere in the package.

    Inspect what data hits the destination in DataViewer.


    Arthur

    MyBlog


    Twitter

    Tuesday, January 13, 2015 2:44 PM
    Moderator
  • Truncation must raise an error and the error rows should redirect in your scenario, so what you described does not make sense.

    No, when writing to SQL Server the data will be truncated, without error.
    The only thing you get is a warning when designing the package.

    You get truncation errors when you try to put data longer than the column width in the data flow buffer, i.e. at the source or at transformations, but not at the destination apparently.

    @Prabu: you can check the length using a conditional split. If the lenght is too long, redirect the row yourself to the error table.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Tuesday, January 13, 2015 9:57 PM