September 16, 2008 at 8:25 pm
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
September 17, 2008 at 12:33 am
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.
September 17, 2008 at 6:34 am
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
September 17, 2008 at 3:14 pm
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
September 18, 2008 at 6:35 am
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
February 17, 2010 at 8:58 am
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).
February 18, 2010 at 12:55 am
Aaron Cabrele (2/17/2010)
HiI 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".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 18, 2010 at 2:29 am
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.
February 18, 2010 at 3:05 am
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.
June 28, 2010 at 7:49 pm
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 🙂
June 29, 2010 at 7:07 am
absolutely, the issue wasnt so much how to do it, but how to automat it for 1000 + columns. Not something anyone wants to do one by one.
In the end I generated a dynamic insert statement form system tables to insert one row maxing out all data lengths (all varchar), exported it and used it as a dummy fikle to autop detect. Bit of a pain but not as bad as the alternative!
What would have been ideal is to be able to automatically use the target tables dt's as the source dt's. The table was built from the file spec so they are identical max lengths.
September 14, 2010 at 3:15 am
Hurrah! This has solved a problem that's been bugging me for a few days. Thanks!
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 14, 2010 at 6:00 am
This problem occurs while using the data transformations.
SOLUTION
You need to check the length of the attribute should be same at the database as you are setting in the transformation.
May 31, 2011 at 6:05 am
Something strange... i don't think that this problem is in data transformation. In my project i have only source->destination component and same warnings(source external/output and destination input column lengths are the same(as actual table value), but destination external column length is smaller).. And solution to go through all column list and edit their length is... not optimal for large structures.
July 24, 2012 at 10:38 am
[qu:-D:-D:-D:-D:-Dote]ammumariamphilip (6/28/2010)
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 :)[/quote]:-D
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply