InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 18:19:17 GMT20http://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxright click on component and click show advanced editor. you can change the meta data column typeTue, 07 May 2013 09:11:54 GMTpradhab 47355http://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxThat did not work for me.So what I finally did for a csv source to Table. I added a data convesion task and the one coulum which was a headache, I created alises for that and then set that coulmn's the data type as the destination table. so it worked and all got copied but I don't like it.I am going to try Excel now and I am afraid it not going to workFri, 05 Oct 2012 12:40:30 GMTzeeaayhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHey Paul,thanks a bunch. I will try and let you know how it comes up.Regards,Thu, 04 Oct 2012 13:56:08 GMTzeeaayhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHi all,Finally, I just erased the Ole Db destination, added a new one and that's all, problem solved. It seems like a sincronization problem between the metadata and the UI. Please let me know if it works for you?Kind Regards,Thu, 04 Oct 2012 11:22:21 GMTPaul Hernándezhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxSo would some one a guru of SSIS will be able to rescue us out and the nation. Please do share your success experience and expertise achieved over the years.sometimes minds just gets frozen and there is no bail out.Thu, 04 Oct 2012 10:11:32 GMTzeeaayhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHi zeeaay,I´m facing the same problem. I opened the advance editor of every step and refresh the metadata but in the Ole Db Destination the metadata is wrong. I'm also using CSV files as the source. It is just a warning and I know that it won't be any error, but a warning symbol in the DFT is not nice.Any comment would be appreciatedKind RegardsThu, 04 Oct 2012 02:24:16 GMTPaul Hernándezhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxI applied this solution but it keeps on changing the warning from souce to destination and back and forth.Here is my issue and it just keep on torturing me.My source is CSV and I have one column that has length of 50My destination is OLE DB, and there; that column's length is 15I have to set the error as ignore and process shows Green but table is empty.I set the coulm to 15 at source (csv) and warning switches from destination to SourceI do the refresh and warning comes back to destinationWed, 03 Oct 2012 09:12:46 GMTzeeaayhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspx[qu:-D:-D:-D:-D:-Dote][b]ammumariamphilip (6/28/2010)[/b][hr]Hello AllTruncation 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 buttonand also ensure to check the value of the length is reflecting correctly.The truncation warnings vanished immedaitely :)[/quote]:-DTue, 24 Jul 2012 10:38:30 GMTppgarzahttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxSomething 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.Tue, 31 May 2011 06:05:19 GMTvionexhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxThis problem occurs while using the data transformations.[b]SOLUTION[/b]You need to check the length of the attribute should be same at the database as you are setting in the transformation.Tue, 14 Sep 2010 06:00:40 GMTgirish.khullarhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHurrah! This has solved a problem that's been bugging me for a few days. Thanks!Tue, 14 Sep 2010 03:15:18 GMTThomasRushtonhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxabsolutely, 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.Tue, 29 Jun 2010 07:07:26 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHello AllTruncation 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 buttonand also ensure to check the value of the length is reflecting correctly.The truncation warnings vanished immedaitely :)Mon, 28 Jun 2010 19:49:53 GMTammumariamphiliphttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxscrap 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.Thu, 18 Feb 2010 03:05:50 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHmm 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.Thu, 18 Feb 2010 02:29:44 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspx[quote][b]Aaron Cabrele (2/17/2010)[/b][hr]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.[/quote]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".Thu, 18 Feb 2010 00:55:06 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHiI 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).Wed, 17 Feb 2010 08:58:23 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxSuccess. 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. Thu, 18 Sep 2008 06:35:38 GMTBPHhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHi,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.VGWed, 17 Sep 2008 15:14:38 GMTVG-619426http://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxThanks. 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 tableIn 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. bphWed, 17 Sep 2008 06:34:02 GMTBPHhttp://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxHard 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.Wed, 17 Sep 2008 00:33:53 GMTAlexander G.http://www.sqlservercentral.com/Forums/Topic570714-148-1.aspxI 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, bphTue, 16 Sep 2008 20:25:32 GMTBPH