SSIS - Truncation Warnings

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS - Truncation Warnings

22 posts, Page 1 of 3

1 2 3 »»»

SSIS - Truncation Warnings

Rate Topic

Display Mode

Topic Options

Author

Message

BPH

Posted Tuesday, September 16, 2008 8:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 7:21 PM
Points: 34, Visits: 169

I have created a package to import data into a table that has been created per a data dictionary. I used Bryan Knight's book as a guide to get everything set up and am confident with that part. The package runs when I sart debugging, and it appears to be going through all the rows in the text file, then it ends without inserting any rows, and I see a list of Truncation warnings pertaining to 66 columns in the rows under the progress tab.

My data flow task finishes successfully, although no rows make it into the table.

Several of these warnings appear:

[Load alphaData [346]] Warning: Truncation may occur due to inserting data from data flow column "Gl Co Cd" with a length of 50 to database column "GL_CO_CD" with a length of 5.

Any thoughts? Thanks, bph

BPH

Post #570714

Alexander G.

Posted Wednesday, September 17, 2008 12:33 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 8:57 AM
Points: 287, Visits: 286

Hard to tell, as I don't know what your data flow actually does.
But the error message says that you try to stuff a 50 character string in a 5 character field.
That does not work without any truncation.

My guess: You do a string datatype transformation (50 is the default length for a string with this component). If your source data is longer than 50 characters you have to set the truncation error handling in the data transformation to "ignore".

If I am wrong it would be very helpful to have more information about your data flow.

Post #570762

BPH

Posted Wednesday, September 17, 2008 6:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 7:21 PM
Points: 34, Visits: 169

Thanks. First the data flow component.

Flat File Source (csv) >> Row Count Transform pointing at table in db for row count audit>> OLE DB DataSource pointing to db and destination table

In Control flow component...

I use a For Each Loop to go move through a directory with several .csv files, audit rowcount, and archive the file that has been processed.

It seems like Flat File Source defaults to a to a string(50) even though the destination table has the proper field sizes set up per data dictionary.

I'm relatively new with this, as I'm sure you can tell. :) I appreciate your help.

bph

BPH

Post #570945

VG-619426

Posted Wednesday, September 17, 2008 3:14 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, August 05, 2010 11:54 AM
Points: 222, Visits: 676

Hi,

You can edit the flatfile output column width, in falt file connection manager. Make column output width same as table column width for that column.
MAke sure, you have defined width in both sorce (o/p width) and destination (table column) properly. As the warning says, there could be truncation after the package.In the destination you have to make sure this though.

VG

Post #571341

BPH

Posted Thursday, September 18, 2008 6:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 7:21 PM
Points: 34, Visits: 169

Success. I went through all 82 columns and set the output datatype and length for each column to match the table and data dictionary specs. I thought the engine would be smart enough to allow the package to proceed depsite the warnings. Evidently the engine is much smarter than I.

Thnaks to VG and Alexander G.

BPH

Post #571704

parody

Posted Wednesday, February 17, 2010 8:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

Hi

I have the exact same problem, however my source file has 1200 columns which is being split into 2 tables so it is not feasable to go through every source column and set the length.

I cant seem to get the warning to be ignored (always displayed in the error list). I have tried turning validate external metadata off, delay validation, ignore failure for truncation and error.

Obviously I can get it to import (even without ignoring the errors, as the destination table is corrcetly defined and no truncation does actually occur. But it makes the package ugly with all the yellow exclamtion marks and slow as warnings are re-evaluated every time the package is edited - even just moving a task to a different position on screen!

Any ideas? Effectively I just want to suppress the warning dialogue (like you can in TFS for database projects).

Post #867173

Koen Verbeeck

Posted Thursday, February 18, 2010 12:55 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364, Visits: 6,462

Aaron Cabrele (2/17/2010)

Hi

I have the exact same problem, however my source file has 1200 columns which is being split into 2 tables so it is not feasable to go through every source column and set the length.

You could do this: (in this solution, I assume all of your data are strings)

right before you write your data to you ole db destination, you place a data conversion component. In the dialog, you select the first column and then you go to the final column and you select all columns with CTRL+SHIFT+left mouse click. Then select the checkbox. This automatically adds all your columns to the data conversion component.

And here comes the tricky part. Open the XML file of the package in a text editor (preferably a good one, not notepad Smile

). In this file, locate the metadata for your data conversion component and your ole destination and do a search and replace on the following items:

* the datatype of the columns (if necessary, for example non-unicode to unicode (dataType="wstr"))
* the length of the strings (for example: length="10")

The only obstacle now is to match the output of the data conversion (which has changed the names of the columns to something like "Copy of ColumnA") to the columns of your database table. Perhaps you could try with some clever 'search and replace' to match the output of the data conversion to the column names of the database table. In the ole destination dialog, you can then right-click between the two list and select "match items by name".

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #867783

parody

Posted Thursday, February 18, 2010 2:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

Hmm I certainly see what you are getting at. The only problem with this is every columns length is different so It wont be so much a search and replace on the length, but going through and changing each one (back to where we started). Unless I am missunderstanding you (ok, might be able to do some funky stuff in excel or sql to generate the correct xml, as we have the data dictionary from the 3rd party).

I have already tried a conversion step in the hope it might take the target's datatypes somehow. One of the benefits of not using the conversion is that at least all my source and tartget columns map automatically by name!!

Feels like any method is extreemly manual, and seems odd that SSIS doesnt have the functionality to cope with large scale imports.

My last resort is the "suggest data types" option but our sample files currently only contain 10 rows which is not representative of the production files (we wont get those til later). My concern is using a type too short for the source which would then cause the data to actually be truncated rather than just warning of it.

Post #867837

parody

Posted Thursday, February 18, 2010 3:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

scrap that idea... it tries to set char fields to bigint where it shouldnt.

I might use a data generation plan to max out the data types in the staging table use all "X" for example, output that to file then use that file as a dummy import, then hopefully allowing the suggest types to work as I want it.

Post #867847

ammumariamphilip

Posted Monday, June 28, 2010 7:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 9:36 PM
Points: 1, Visits: 7

Hello All

Truncation may occur due to inserting data from data flow column "column_name" with a length of 77 to database column "column_name" with a length of 7."

To resolve this, I had to:

open the Data Flow task
From the source task right click and select
open Advanced Editor.
go to Input and Output Properties tab
expand OLE DB Source Output
Expand External columns and check the specific columns
which give the error and the corresponding length.
This length should ideally be the lenght of the column as designed in your database table.
Now Expand Output Columns
click on corresponding column which gives the error and you vcan notice that the length value won't be the same as that in your external column or your database design .
change Length property of the column to that of the actual table design length and refresh .

I also went to target side:

open Advanced Editor (on OLE DB Destination)
go to Input and Output Properties tab
Click on Refresh button
and also ensure to check the value of the length is reflecting correctly.

The truncation warnings vanished immedaitely :)

Post #944350

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions