Search code examples
sql-serverssisdata-warehouse

Incorrect External column length in SSIS OLE DB source

I am transferring data from database_1 to database_2, I created a table i the destination DB like the one in the source DB, but when I connect the OLE DB source to the table and open the Advanced editor I found that the External column length for a specific column is different, the length in the sql server is varchar (50) but in the SSIS in Advanced editor is DT_SRT (30). I tried to edit it but when I check again it turned into 30 again.

When I execute the package an error rise saying that truncatination occurred on the column and the execution stops.

[Derived Column [2]] Error: The "Derived Column" failed because truncation occurred, and the 
truncation row disposition on "Derived Column.Outputs[Derived Column 
Output].Columns[DBusinessTypeNameAR]" specifies failure on truncation. A truncation error 
occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on             
component "Derived Column" (2) failed with error code 0xC020902A while processing input 
"Derived Column Input" (3). The identified component returned an error from the ProcessInput 
method. The error is specific to the component, but the error is fatal and will cause the Data 
Flow task to stop running.  There may be error messages posted before this with more 
information about the failure.

[OLE DB Source [79]] Error: The attempt to add a row to the Data Flow task buffer failed with 
error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OLE 
DB Source returned error code 0xC02020C4.  The component returned a failure code when the 
pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the 
component, but the error is fatal and the pipeline stopped executing.  There may be error 
messages posted before this with more information about the failure.

this is the source view :

enter image description here

this is the destination table :

enter image description here

this is the data flow :

enter image description here

this is the advance editor :

enter image description here

EDIT:

Microsoft SQL Server Data Tools for Visual Studio 2017 
Version 15.9.15
VisualStudio.15.Release/15.9.15+28307.812
Microsoft .NET Framework
Version 4.7.03062
****************************
SQL Server Data Tools   15.1.61906.03120
Microsoft SQL Server Data Tools

SQL Server Integration Services   15.0.1301.433
Microsoft SQL Server Integration Services Designer
Version 15.0.1301.433

the Derived Column only contains code page correction as the screenshot below.

enter image description here

Solution

  • There are two different things going on here.

    1. String truncation error from a Derived Column

    [Derived Column [2]] Error: The "Derived Column" failed because truncation occurred, and the 
truncation row disposition on "Derived Column.Outputs[Derived Column 
Output].Columns[DBusinessTypeNameAR]" specifies failure on truncation. A truncation error 
occurred on the specified object of the specified component.

    Note that the truncation message is coming from your Derived Column not the destination column. The derived column's length is 10, not 30 (or 50). Increasing the size of your DBusinessTypeNameAR derived column to 30 (DT_STR,30,1252) or 50 (DT_STR,50,1252) should remove the error.

    enter image description here

    2. Out-of-sync destination column metadata

    If your design-time database's column meta-data does not match the run-time database 's column meta-data (you said in the comments your target database connection string was set by an expression) this may explain the out-of-sync destination database column metadata.

    If you've ensured that the two db 'scenarios' (design-time db and run-time db) have the same field meta-data, the simplest way to re-sync out-of-sync destination table column meta-data for OLEDB Database destination component is to:

    1. Inside the OLE DB Destination Editor, click the table drop-down list to expand the list of target tables.
    2. Select a different table in the drop-down list.
    3. Move to a different "tab" in the editor.
    4. Back on the Connection Manager tab, select the original table from the table drop-down, and make sure that all mappings are in place (Mappings tab)
    5. Click ok and Save changes.

    enter image description here