1

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

Guissous Allaeddine
  • 425
  • 1
  • 4
  • 19
  • 1
    Can you provide info on your dev environment via Help > About Microsoft Visual Studio? Specifically version of VS and SQL Server Integration Services version. – katzbatz Jun 08 '22 at 21:09
  • 1
    Are you working with more than one destination database environment? Are your connection string values derived from project parameters or expressions? – katzbatz Jun 08 '22 at 21:10
  • 1
    Your error text shows that there is a Derived Column.Outputs[Derived Column Output].Columns[DBusinessTypeNameAR]. What is the expression of the referenced Derived Column? – katzbatz Jun 08 '22 at 21:15
  • 1
    your table definition says its a length of 50. But you have it on 30 in SSIS. You need to correct it - remove the coulumn and add it – SqlKindaGuy Jun 08 '22 at 22:00
  • 1
    You need to check derived columns, and try to remove it first. – kucluk Jun 09 '22 at 03:41
  • @katzbatz first and third comments are in the Edit section, second comment : no, there is only one destination database "landing DB", the connection string is an expression. – Guissous Allaeddine Jun 10 '22 at 07:48
  • @SqlKindaGuy are you suggesting removing the column from the source table ? because I can't do that, it contains thousands of records, if you mean the destination column ? the error is raised in the source and not the destination, – Guissous Allaeddine Jun 10 '22 at 07:51
  • @kucluk please refer to the Edit section for the derived column, it only a correction for the code page, it was 1256 I made it 1252 as the destination. – Guissous Allaeddine Jun 10 '22 at 07:53
  • No, i meant in SSIS Derived column editor :) – SqlKindaGuy Jun 10 '22 at 10:25

1 Answers1

1

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

katzbatz
  • 332
  • 2
  • 16
  • thank you for your respond but, I put 50 in the derived column's length, and the error still existe, when I take the screen shot, I was experimenting trying to solve the issue, sorry for misleading you, Worth mentioning that the records for the DBusinessTypeNameAR column is Arabic text, and its type is varchar(50), I disable the validation of the metadata and the error is gone but I get ???????????? in the destination table. – Guissous Allaeddine Jun 11 '22 at 13:24
  • Supporting Arabic text would require you to either use Arabic collation for the source field and destination field, or use unicode (nvarchar) strings for source, derived column and destination. See https://stackoverflow.com/q/3560173/5818960 for brief but solid help on how to do that. – katzbatz Jun 12 '22 at 01:15
  • I recreated the destination table with nvarchar(50) for DBusinessTypeNameAR and in SSIS I added a data conversion component to convert the output of the source from varchar(50) to nvarchar(50) and it works, thank you for the support. – Guissous Allaeddine Jun 12 '22 at 04:56