SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Truncation Warnings


SSIS - Truncation Warnings

Author
Message
Dobbs77
Dobbs77
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 242
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
Alexander G.
Alexander G.
Say Hey Kid
Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)Say Hey Kid (693 reputation)

Group: General Forum Members
Points: 693 Visits: 315
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.
Dobbs77
Dobbs77
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 242
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. Smile I appreciate your help.

bph

BPH
VG-619426
VG-619426
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1022 Visits: 692
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
Dobbs77
Dobbs77
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 242
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
parody
parody
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 306
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).
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63596 Visits: 13298
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 :-)). 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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
parody
parody
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 306
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.
parody
parody
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 306
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.
ammumariamphilip
ammumariamphilip
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 10
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search