none
Error while importing Text file using Import Export Wizard

    Question


  • Hi all

    i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import



    Operation stopped...

    - Initializing Data Flow Task (Success)

    - Initializing Connections (Success)

    - Setting SQL Command (Success)

    - Setting Source Connection (Success)

    - Setting Destination Connection (Success)

    - Validating (Success)

    - Prepare for Execute (Success)

    - Pre-execute (Success)

    Messages

    Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
    (SQL Server Import and Export Wizard)

    - Executing (Error)

    Messages

    Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
    (SQL Server Import and Export Wizard)

    Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    (SQL Server Import and Export Wizard)

    Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
    (SQL Server Import and Export Wizard)

    Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. 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.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
    (SQL Server Import and Export Wizard)

    Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
    (SQL Server Import and Export Wizard)

    - Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)

    - Post-execute (Success)

    Messages

    Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
    (SQL Server Import and Export Wizard)

    Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
    (SQL Server Import and Export Wizard)

    Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
    (SQL Server Import and Export Wizard)

    - Cleanup (Success)

    Messages

    Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
    (SQL Server Import and Export Wizard)

     




    =============================


    **** from the error message (Executing)  error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...

    this cells contains the following text:  ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
    where the ";" is the delimiter

    moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
    where the ";" is the delimiter


    **** from the error message (Executing)  error number 1
    there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution


    i hope that i will find solution for my problem

    thank you



    Saturday, November 19, 2005 4:45 PM

Answers

  • The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

     

    Thanks.

    Wednesday, February 22, 2006 8:35 PM
    Owner

All replies

  • Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.

    Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.

    -Jamie
    Sunday, November 20, 2005 6:01 AM
    Moderator
  • dear sir
    thank for ur replay

    about what u say

    Import/Export wizard offers you the chance to save your package

    i didnt know how to do it?
    is it in the Import/Export wizard then in column maping click in Edit SQL ...

    also when u say

    then open it up and look at the metadata of the pipeline

    i didnt under stand it ..... sorry Smile


    but after long investegation i found the following

    i instal SQL2000 and SQL2005 and i create table inside each of them with the
    same spacifecation as follows

    all the colums with data type: varchar  and   length: 8000 for both tabels.

    i fined somthing very strange Tongue TiedTongue TiedTongue Tied

    - when i use the Import/Export wizard of SQL2000
    i can import the Text file to the table in SQL2000 and to the table in SQL2005

    - when i use the Import/Export wizard of SQL2005
    i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005

    - when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
    i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005

    Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!

    Tongue TiedTongue TiedTongue TiedTongue TiedTongue Tied


    Sunday, November 20, 2005 9:37 PM
  • I'm convinced also that some kind of bug must exist here. I keep getting this error:

    The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.

    I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.

    Dan

    Tuesday, January 31, 2006 6:14 PM
  • OK, time for me to eat some crow.

    Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.

    My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)

    Dan

    Friday, February 03, 2006 7:20 PM
  • Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.

    Thanks!!

     

    Monday, February 20, 2006 11:43 AM
  • I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000
    Tuesday, February 21, 2006 6:57 PM
  • I get the same error on all our development and production machines.

    I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.

    This will just show that there is a bug in SSIS 2005.

    Please help MS! This is a big problem for us!

    Wednesday, February 22, 2006 3:10 AM
  • I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message??? The file is ok because the import in SQL Server 2000 works fine.
    At first I thought that scandinavian characters are the problem but they are not.

    Can anyone help us?

    Wednesday, February 22, 2006 7:23 AM
  • I am quite convinced that the there is a bug in Import export wizard.
    It looks like that wizard can handle only 50 characters tops in one column.
    I resolved the problem by making the bulk insert in sql.

    BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)

    Hopefully this issue is resolved and fixed by Microsoft soon...

    Wednesday, February 22, 2006 8:12 AM
  • The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.

     

    Thanks.

    Wednesday, February 22, 2006 8:35 PM
    Owner
  • Thanks for your answer,

    I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
    When I changed the values in Advanced page as you told everything works fine ;)
    I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.

    There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
    I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.

    Thursday, February 23, 2006 7:00 AM
  • I think there is a bug in the product as well.  I have tried to import SQL generate documents and received these errors.  I have had to add the extra step of first importing them into a spreadsheet and then importing into SQL Server 2005.  If anyone knows of a patch, I would love to know.
    Thursday, March 09, 2006 9:27 PM
  • I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.
    Friday, March 10, 2006 9:20 PM
  • hi jaypee,

    I have just tried using the bulk insert as  follows

    BULK INSERT dbo.[table]

    FROM 'Y:\data.csv'

    with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)

    and I ge the following error for all the Datetime columns in the file.

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).

    any ideas?

     

    Wednesday, March 15, 2006 4:46 PM
  • QUICK FIX:

    I had the same issue,  FLAT FILE - SQL TABLE

    I had Success when I did the following
    # Imported flat file to ACCESS (.mdb)
    # Created a Integrations Services Package which Imports from ACCESS - SQL TABLE.

     



     

    Saturday, March 18, 2006 10:45 PM
  • We have the same problem importing data from an Excel spreadsheet in a complex SSIS package.

    The strangest and most irritating part of this problem, is that it doesn't occur when the package is executed on Windows XP.  Everything runs through fine, no data truncation when run on Windows XP where the developer edition of SQL Server 2005 is installed.  When we release the package to either of our test or production environments, which are running SQL Server 2005 Enterprise Edition on Windows Server 2003, the package fails with a Truncation Error.  It is truncating to 256 characters, even though the column settings are correctly set to WSTR 2000.

    This is really frustrating us, anyone any ideas, why it works in one evironment but not the other?

     

     

    Monday, March 27, 2006 1:58 PM
  • Most of your errors should be solved by clicking on "Advance" Tab when importing your flat-file

    you have to enter the length for each field
    Sunday, April 16, 2006 6:43 PM
  • I have the same problem. SQL Server 2005 has a better User Interface but has hatched a lot of issues like this one and other Import/Export tasks. Most of the problems I found were related to import of Excel to SQL Server. Most recent one is the import of numeric column to SQL Server Integer and the column becomes NULL in the new table.
    Monday, April 17, 2006 6:28 PM
  • Two solutions exist:
     - the first one is to force the size of the field that accuses the error for 8000.
     - In case that the first one does not solve, it tries to matter for a Access and alone later for the SQLServer2005, instead of using flat file, uses the Flat, leaves that it creates the table to be inserted.
     I wait to have helped.
     Greetings to the all,
    Marcomídia.
    Wednesday, April 19, 2006 7:17 PM
  • I had the same problem, importing simple text file with max column length of 80 chars, into a 255 column table.

    Saving the file as an XLS and importing with the Excel filter worked fine.

    But... it now works with SP1.

    Monday, April 24, 2006 9:42 PM
  • This does seem like a bug.

    I have a tab delimited file:

    "P08100" "WFS-PALB" "Palassis Basil" "2005" "'0505" "232,000" "OLIVES"
    "P00652" "2020-KENB" "Lannon Michael" "" "'0504" "7,500" "SYLVA"

    The second row causes the error on the 4th column which has a value of "2005" in the first row then "" in the second row.

    That is not truncation !

    Tuesday, May 02, 2006 8:38 AM
  • Michael,

    I successfully imported the file so I doubt there is a bug. Below is the contents of the .dtsx file from which i did it. Copy everything below into notepad, save it as Package.dtsx, and add it to your project.

    If you like, email me via the contact form on my blog (http://blogs.conchango.com/jamiethomson/contact.aspx) and I will reply with the package attached.

    Hope this helps.

    -Jamie

     

    ==============================================================

    <?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">CT\jtbx</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">CZC54223LY</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">5/2/2006 9:58:17 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">2</DTS:Property><DTS:Property DTS:Name="VersionGUID">{73BAE68B-61E6-455A-B49E-93F7F2FAA9DD}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

    <DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">file</DTS:Property><DTS:Property DTS:Name="DTSID">{3BFF26C8-AB4E-4A7A-A704-693F2484CB87}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">0</DTS:Property><DTS:Property DTS:Name="Format">Delimited</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="Unicode">0</DTS:Property><DTS:Property DTS:Name="HeaderRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="HeaderRowDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnNamesInFirstDataRow">0</DTS:Property><DTS:Property DTS:Name="RowDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="DataRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="TextQualifier">"</DTS:Property><DTS:Property DTS:Name="CodePage">1252</DTS:Property>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 0</DTS:Property><DTS:Property DTS:Name="DTSID">{4260559C-CEF1-4AFC-B2F0-28D89AC3A621}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 1</DTS:Property><DTS:Property DTS:Name="DTSID">{AD929D79-E666-4EEE-B8D9-DFC44758FA7C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 2</DTS:Property><DTS:Property DTS:Name="DTSID">{E5AA0528-4CC2-408D-AA0A-CC9D0EFF58C0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 3</DTS:Property><DTS:Property DTS:Name="DTSID">{1A64B5DD-0A54-4189-9578-8F62C7BD8E69}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 4</DTS:Property><DTS:Property DTS:Name="DTSID">{9B034BCE-80F2-4092-8BD8-0EEE8C0DDE54}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x0009_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 5</DTS:Property><DTS:Property DTS:Name="DTSID">{3F43BDBD-B635-45A2-9D47-8BA9A326ACAF}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn>

    <DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">50</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">Column 6</DTS:Property><DTS:Property DTS:Name="DTSID">{6F8DB49C-480E-4EF2-B54E-185E36057281}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">C:\Temp\example.txt</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

    <DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

    &lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="5" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="33179" y="19526" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

    &lt;font&gt;

    &lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

    &lt;/font&gt;

    &lt;mouseicon&gt;

    &lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

    &lt;/mouseicon&gt;

    &lt;/diagram&gt;

    &lt;layoutmanager&gt;

    &lt;ddsxmlobj /&gt;

    &lt;/layoutmanager&gt;

    &lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="6033" top="5159" logicalid="2" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

    &lt;control&gt;

    &lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

    &lt;/control&gt;

    &lt;layoutobject&gt;

    &lt;ddsxmlobj&gt;

    &lt;property name="LogicalObject" value="{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}" vartype="8" /&gt;

    &lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/layoutobject&gt;

    &lt;shape groupshapeid="0" groupnode="0" /&gt;

    &lt;/ddscontrol&gt;

    &lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{BB375E04-AF43-4A31-B403-E5CA2429E746}</DTS:Property><DTS:Property DTS:Name="DTSID">{8FA99FEF-18C0-4694-B118-DE28BCDA6FCB}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

    <DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

    &lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="8" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="33179" y="18600" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

    &lt;font&gt;

    &lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

    &lt;/font&gt;

    &lt;mouseicon&gt;&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

    &lt;/mouseicon&gt;

    &lt;/diagram&gt;

    &lt;layoutmanager&gt;&lt;ddsxmlobj /&gt;

    &lt;/layoutmanager&gt;

    &lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Flat File Source" left="16589" top="2461" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;&lt;control&gt;&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

    &lt;/control&gt;

    &lt;layoutobject&gt;&lt;ddsxmlobj&gt;&lt;property name="LogicalObject" value="{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}/components/1" vartype="8" /&gt;

    &lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/layoutobject&gt;

    &lt;shape groupshapeid="0" groupnode="0" /&gt;

    &lt;/ddscontrol&gt;

    &lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Counts the rows in a dataset." left="17357" top="6456" logicalid="5" controlid="5" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;&lt;control&gt;&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

    &lt;/control&gt;

    &lt;layoutobject&gt;&lt;ddsxmlobj&gt;&lt;property name="LogicalObject" value="{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}/components/39" vartype="8" /&gt;

    &lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/layoutobject&gt;

    &lt;shape groupshapeid="0" groupnode="0" /&gt;

    &lt;/ddscontrol&gt;

    &lt;ddscontrol controlprogid="MSDDS.Polyline" left="17989" top="3226" logicalid="6" controlid="6" masterid="0" hint1="0" hint2="0" width="1567" height="3730" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;&lt;control&gt;&lt;ddsxmlobj&gt;&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1"&gt;&lt;adornment percentpos="50" controlid="7" width="423" height="423" side="1" behavior="2" himetric="1799" distfromline="175" startobj="0" x="18907" y="4482" visible="1" allowoverlap="1" usepercent="1" /&gt;

    &lt;/polyline&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/control&gt;

    &lt;layoutobject&gt;&lt;ddsxmlobj&gt;&lt;property name="LogicalObject" value="{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}/paths/43" vartype="8" /&gt;

    &lt;property name="Virtual" value="0" vartype="11" /&gt;

    &lt;property name="VisibleAP" value="0" vartype="3" /&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/layoutobject&gt;

    &lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="5" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;&lt;point x="18388" y="3625" /&gt;

    &lt;point x="18388" y="5080" /&gt;

    &lt;point x="19156" y="5080" /&gt;

    &lt;point x="19156" y="6456" /&gt;

    &lt;/connector&gt;

    &lt;/ddscontrol&gt;

    &lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" left="18907" top="4482" logicalid="7" controlid="7" masterid="6" hint1="0" hint2="0" width="423" height="423" noresize="1" nomove="1" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="0" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="0" visible="1" snaptogrid="0"&gt;&lt;control&gt;&lt;ddsxmlobjectstreaminitwrapper binary="00080000a7010000a7010000" /&gt;

    &lt;/control&gt;

    &lt;layoutobject&gt;&lt;ddsxmlobj&gt;&lt;property name="VisualizerAdornment" value="-1" vartype="11" /&gt;

    &lt;/ddsxmlobj&gt;

    &lt;/layoutobject&gt;

    &lt;shape groupshapeid="0" groupnode="0" /&gt;

    &lt;/ddscontrol&gt;

    &lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}</DTS:Property><DTS:Property DTS:Name="DTSID">{E78EDAC6-BD92-4E81-B0C0-19BB63E2279C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

    <DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:PathAnnotation&gt;AsNeeded&lt;/dwd:PathAnnotation&gt;&lt;dwd:DestinationName&gt;Row Count Input 1&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;Flat File Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}-43</DTS:Property><DTS:Property DTS:Name="DTSID">{121441DE-4DD8-4647-9E73-D44BD67124E3}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

    <DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">Variable</DTS:Property><DTS:Property DTS:Name="DTSID">{C3788B33-DDD2-46CC-945B-C429B0A53ECD}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>

    <DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

    <DTS:Executable DTS:ExecutableType="{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

    <DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{ECE89EE0-006F-4AFC-BA1B-A2DA3B5D7CA3}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

    <components>

    <component id="1" name="Flat File Source" componentClassID="{90C7770B-DE7C-435E-880E-E718C92C0573}" description="Flat File Source" localeId="1033" usesDispositions="true" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Flat File Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">

    <properties>

    <property id="7" name="RetainNulls" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether zero-length columns are treated as null." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

    <property id="8" name="FileNameColumnName" dataType="System.String" state="default" isArray="false" description="Specifies the name of an output column containing the file name. If no name is specified, no output column containing the file name will be generated." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

    <connections>

    <connection id="6" name="FlatFileConnection" description="" connectionManagerID="{3BFF26C8-AB4E-4A7A-A704-693F2484CB87}"/></connections>

    <outputs>

    <output id="2" name="Flat File Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

    <outputColumn id="18" name="Column 0" description="" lineageId="18" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="17"><properties>

    <property id="19" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="21" name="Column 1" description="" lineageId="21" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="20"><properties>

    <property id="22" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="24" name="Column 2" description="" lineageId="24" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="23"><properties>

    <property id="25" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="27" name="Column 3" description="" lineageId="27" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="26"><properties>

    <property id="28" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="30" name="Column 4" description="" lineageId="30" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="29"><properties>

    <property id="31" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="33" name="Column 5" description="" lineageId="33" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="32"><properties>

    <property id="34" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn>

    <outputColumn id="36" name="Column 6" description="" lineageId="36" precision="0" scale="0" length="50" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="35"><properties>

    <property id="37" name="FastParse" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the column uses the faster, locale-neutral parsing routines." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property></properties></outputColumn></outputColumns><externalMetadataColumns isUsed="True">

    <externalMetadataColumn id="17" name="Column 0" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="20" name="Column 1" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="23" name="Column 2" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="26" name="Column 3" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="29" name="Column 4" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="32" name="Column 5" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/>

    <externalMetadataColumn id="35" name="Column 6" description="" precision="0" scale="0" length="50" dataType="str" codePage="1252"/></externalMetadataColumns></output>

    <output id="3" name="Flat File Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

    <outputColumn id="38" name="Flat File Source Error Output Column" description="Flat File Source Error Output Column" lineageId="38" precision="0" scale="0" length="0" dataType="text" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

    <outputColumn id="4" name="ErrorCode" description="" lineageId="4" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

    <outputColumn id="5" name="ErrorColumn" description="" lineageId="5" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

    </outputs>

    </component>

    <component id="39" name="Row Count" componentClassID="{DE50D3C7-41AF-4804-9247-CF1DEB147971}" description="Counts the rows in a dataset." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Row Count;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

    <properties>

    <property id="40" name="VariableName" dataType="System.String" state="default" isArray="false" description="Specifies the variable to hold the row count." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Variable</property></properties>

    <inputs>

    <input id="41" name="Row Count Input 1" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>

    </inputs>

    <outputs>

    <output id="42" name="Row Count Output 1" description="" exclusionGroup="0" synchronousInputId="41" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>

    </outputs>

    </component>

    </components>

    <paths>

    <path id="43" name="Flat File Source Output" description="" startId="2" endId="41"/>

    </paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package1</DTS:Property><DTS:Property DTS:Name="DTSID">{BB375E04-AF43-4A31-B403-E5CA2429E746}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

    Tuesday, May 02, 2006 9:07 AM
    Moderator
  • Hello Ladies and Gents,

    I to sufffer from the SQL 2005 Import issues mentioned in all your posts. I overcame this by just going straight to

    Choose Data Source | Advanced | Click Field | OutputColumnWidth | change from 50 to say 100.

    This worked for me and overcame all those indescribsable erorr messages that we all know and love. Microsoft, bless em all.

    Hope this helps and makes your day a slightley less stressful one!

    naexpert.

     

    Thursday, May 25, 2006 10:39 AM
  • I also have the same problem. Text file which I easly imported in SQL 2000 (SP4) by DTS but I'm not able to importable the same file to SQL 2005 SP1 (developer edition on Win XP). I changed field size to 4000, tried W2K and WXP but still got  this error :(

    So once more: Can anyone help us PLS?

    Monday, June 05, 2006 11:50 AM
  • Hello,

    I have the same error(s) as described above.

    I'm trying since 5 days to import data from a textfile, but it seems that SSIS doesn't like to do typeconversions.

    The "solutions" and quix fixes that were given here don't work here. Is there anywhere a tutorial or something like this? SSIS allways talks about "probably data loss when converting" but I can't see where this should happen.

    Regards,

    Jan

    Friday, June 16, 2006 4:20 PM
  •  jwagner20 wrote:

    I'm trying since 5 days to import data from a textfile, but it seems that SSIS doesn't like to do typeconversions.

    Not quite true. SSIS doesn't like to do implicit type conversion. For 2 reasons:

    1. Slower performance
    2. The conversions can be wrong.

    Hence, you are now required to do explicit conversions on the data.

    -Jamie

     

    Friday, June 16, 2006 4:29 PM
    Moderator
  • Hi Jamie,

    thanks for your answer, but I can't handle this problem by using explicit conversions.

    I opened a new thread for this, so maybe you got an idea:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=486927&SiteID=1&mode=1

    Regards,

    Jan Wagner

    Monday, June 19, 2006 2:56 PM
  • Importing from a flat file source is behaving very strange in SQL 2005.

    Trying to import a text file that ca be imported without any problem inton SQL2000, just keeps failing in SQL2005.

    Some information:
    The text file to be imported contains 50 columns, each column qualified with double quotes {"} and with {LF} as row delimter.

    First problem:
    Let's say, one row (any row) contains a {CR}{LF} in column 30, you only see up to 30 columns in the "Advanced" tab. Manually adding the missing 20 rows solves the problem.

    Second Problem:
    Let's say on column looks like this: "some text before ""some quoted text"" followed by any text".
    Now the wizard thinks that theres a new column starting with ""some quoted text"" [..].

    I haven't found a solution for this one yet.
    I'd appreciate any hint regarding this "bug".

    === Edited by Alex1123123 @ 20 Jun 2006 10:39 AM UTC===
    Concerning the Second Problem:

    The wizard says:

    "TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The preview sample contains embedded text qualifiers ("). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time."

    Why doesn't he support that? And why did SQL2000 support embedding text qualifiers?

    Tuesday, June 20, 2006 9:59 AM
  • Choose Data Source | Advanced | Click Field | OutputColumnWidth | change from 50 to say 100.

    This one worked for me. I changed it to 100 and it finally imported after hours of messing with it. The frustrating part is that is imports fine into SQL 7.0 and SQL 2000 with no issues.

    Saturday, July 08, 2006 6:44 AM
  • Hi all,
    I don't know if this will help because I didn't use the wizard. I was doing Bulk Inserts from csv files using a format file.
    In Sql Server 2000 you don't need to accurately specify the size of space to reserve. 12 will do for everything. In SQL Server 2005 it gives the Bulk load data conversion error (type mismatch or invalid character...) if the data is longer than 12 chars.

    Try specifying the actual size of the data in the column you are having problems with.

    Good luck chaps (and chapettes)

    Rib
    Thursday, July 13, 2006 2:50 PM
  • I agree with most posters here. There is a bug in the import wizard. I have a flat file that I’ve been trying to import and get the same error message. 

    ----------------------- snip -------------------------------------------

    Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
     (SQL Server Import and Export Wizard)

    ------------------------------------------------------------------------

    I’ve tried changing the column types, ignoring all but one field, creating a new table on insert, nothing seems to work. However, when inserting the same file into SQL Server 2000, I have no problems. I hope that we get a solution for this in SP2.

    My work around was a simple Bulk Insert in SQL 2005 on 1400 rows which executed in less than a second.

    ------------- snip -----------------------

    BULK INSERT dbo.BELLMICRO832
    FROM 'C:\CLAY\EDI\H14620.txt'
    with (FIELDTERMINATOR='\t')

    -------------------------------------------

    Friday, July 14, 2006 1:33 PM
  • Hi All,

    This is definitely NOT a bug.

    I also struggled with this concept when I first used the Import/Export wizard with 2005, but I have sinced learned that you need to tell the data flow what size field is coming through. This is nothing to do with the size on your source data, nor anything to do with the destination table. You can make the destination table as big as you like and the data will still fail with a truncation during the data flow.

    When you enter your data source, you have an advanced tab on the left. After that, click on the offending column and one of the properties under 'Misc' on the right is OutputColumnWidth. Enter the correct value here and your problems will be gone. If you read the description for OutputColumnWidth you will see: "The width of this column in the data flow."

    Hope this help and happy coding!

    SQL Later,

     

    Tuesday, August 15, 2006 12:26 PM
  •  

    Hi

    I followed the advice listed by R2 DJ above and found I was still getting it wrong.

    I would recommend you click on the 'suggest types' button whilst in the advanced tab and the column types and sizes are worked out for you. Much quicker than having to do this manually. I did have to change one column afterwards and this was a field which contained letters and numbers, I had to change it from an integer to a string. After alot of agro and surfing, and the comments above and selecting 'suggest types' my data did export into 2005.

    Hope this shows there can be light at the end of the tunnel with 2005.

    Coopster

     

     

    Wednesday, August 23, 2006 10:23 AM
  • I was having the same problem as listed above.  Out of frustration I set the Output Column Length to 1000 for all of my 75 columns and Voila! it worked.  So now I just have to figure what the actual lengths should be.  Thanks for the suggestion.
    Thursday, August 31, 2006 4:53 PM
  • Thanks,

    Thanks

    Boris

    Wednesday, September 13, 2006 12:05 PM
  • Unfortunately, I did not find importing from DB2 7.2 to SQL Server to be straight forward.  I had to deal with each table independently, and there were often exceptions that required special handling.  The basic process is as follows:

         Small tables - re-create by hand.

         Drop DB2 7.2 to text files, (comma delimited, double-quotes to identify character fields)
         I found it best to script the export (done thru Control Center, Delimited ASCII, under options:  de-select Prefix, select Suppress) and edit it as necessary.
         When in doubt, strip out formatting on the way down: 
              "replace(replace(replace(replace(Descript, chr(9), ' '), chr(10), ' '), chr(11), ' '), chr(13), ' ') as Descript, ..."
         Make sure you didn't lose any information in the export.

         Edit the text files to apply a header line (not necessary, but helps with SQL Server Import Wizard). 
         Don't enclose column labels in quotes, no leading spaces after commas (example:  "ID,Name First,Name Last,...")

         Import into a intermediate table (I had to finish up in a specific schema, so I used the default dbo schema for all my initial imports).
         Target text fields as NVarChar.
         May need to import some number fields (such as signed numbers, decimals and some dates) as text initially.

         Copy to final destination table, doing converts as necessary.

         Curse the mangy dogs that wrote DB2 7.2

    I've probably missed one or two wrinkles, but you get the idea.  Including the learning curve, this took me DAYS for 30 or so tables.

    Thanks,

    Kevin

    Monday, September 18, 2006 2:58 PM
  • Unfortunately, I did not find importing from DB2 7.2 to SQL Server to be straight forward.  I had to deal with each table independently, and there were often exceptions that required special handling.  The basic process is as follows:

         Small tables - re-create by hand.

         Drop DB2 7.2 to text files, (comma delimited, double-quotes to identify character fields)
         I found it best to script the export (done thru Control Center, Delimited ASCII, under options:  de-select Prefix, select Suppress) and edit it as necessary.
         When in doubt, strip out formatting on the way down: 
              "replace(replace(replace(replace(Descript, chr(9), ' '), chr(10), ' '), chr(11), ' '), chr(13), ' ') as Descript, ..."
         Make sure you didn't lose any information in the export.

         Edit the text files to apply a header line (not necessary, but helps with SQL Server Import Wizard). 
         Don't enclose column labels in quotes, no leading spaces after commas (example:  "ID,Name First,Name Last,...")

         Import into a intermediate table (I had to finish up in a specific schema, so I used the default dbo schema for all my initial imports).
         Target text fields as NVarChar.
         May need to import some number fields (such as signed numbers, decimals and some dates) as text initially.

         Copy to final destination table, doing converts as necessary.

         Curse the mangy dogs that wrote DB2 7.2

    I've probably missed one or two wrinkles, but you get the idea.  Including the learning curve, this took me DAYS for 30 or so tables.

    Thanks,

    Kevin

    Monday, September 18, 2006 3:15 PM
  • Try importing to Excel as an intermediate step.  Shouldn't be necessary, but works for me in most cases.

    Wednesday, September 27, 2006 2:21 PM
  • Ok, I had the same problem. First I checked if dat file is ok: not to many commas, CR LF are in right places after each line.

    THen trimmed trailing spaces in dat file.

    Then column size in DTS wizard has to be >= then mapped column in DB. Otherwise it gives again an error!

    Don't ask why :)

     

     

    Tuesday, October 24, 2006 11:56 AM
  • Alright, I read every reply to this thread and this error, and I feel like I have learned a lot.  However, my problem still isn't solved.  I am trying to import from Excel, not a flat file, and I get the error message about truncating.  I understand that with flat files, you have the Advanced item you can select when choosing a source, but the Advanced item is not available if you chose to import from Excel.  I would very much prefer to import from an Excel file, since I don't enjoy specifying delimiters or performing other types of text modifications, but for now I will try to import from a CSV after removing all commas.

    Is there any way to make it work using an Excel file?  Can I somehow change its settings for the "Notes" column to say the source column width is 1000 instead of 256?  I haven't been able to find a place to change that setting anywhere.

    Thanks!

    Tuesday, October 24, 2006 11:51 PM
  • Ok, I took care of the problem for myself, but it was another work-around.  I hated the idea of a flat text file so much, I decided instead to add a whole slew of XXXX's to the end of the notes field of the first record, and that made the import wizard properly discern a valid length for the source field of the source file, and as a result, it started with a valid length for the target location without me having to change it.

    I know this may just be my opinion, but this is a bug.  A user should not have to deal with this.  Either the Import Wizard needs to give the individual importing from an Excel file the "Advanced" option, or the Import Wizard needs to correctly identify the length for each source column.  Futhermore, I don't know why the length of the source column even has to be an issue.  Is the Import Wizard trying to save system resources on importing an Excel file by finding the smallest possible field length for each source field?

    Either way, a user should not get an error on a source field length, especially when the user does not have the option to adjust the field length value, like with an "Advanced" page.

    Ok, ok, I feel better now.

    • Proposed as answer by redant34 Friday, February 05, 2010 8:09 PM
    Wednesday, October 25, 2006 12:44 AM
  • I have also been having the same problem with the truncation and STATUS 4 error.

    After reading this thread I changed the OutputColumnWidth value to 8000 in the FlatFile Connection Manager under the Advanced tab. The problem is now fixed. In my SQL Server 2005 database, the field types are all varchar(MAX)

    Thank you to the person who suggested this solution. Very nice.

     

    Sunday, October 29, 2006 2:04 PM
  • I have read every post, tried every suggested solution, and there is definately a BUG.  I am importing XML, so there is no option to suggest types, nor does the advanced tab  provide length options.  I cannot even change the ErrorOnTruncation property to False. 

    Truncation should not cause the transaction to abort.  It should just show a warning, and this would solve a lot of problems.

    Since I could not change the column width, I tried to remove, and then later I tried to truncate the field manually in the Derived Columns tool in the Business Intelligence Dev Studio, but the error occured before it got to Derived Columns, in the Data Source tool. 

    The only option that allowed my import to complete was to delete the guilty field in the Advanced Editor of the Data Source tool under the Input and Output Properties tab.

    Tuesday, December 05, 2006 4:00 PM
  • The solution to this mess for me is to use SQL 2000s import utility to pump data into SQL 2005 tables.  It eliminates the truncation error messages.
    Thursday, December 14, 2006 10:25 PM
  •  

    Same issue here.  WTF?  It just shouldn't be this time consuming.

    Thanks to all who posted suggestions.  If it's not a bug, it's just crappy.

     

    Thursday, February 15, 2007 9:24 PM
  • Thanks R2 DJ

    You have saved what little hair I have left on my head !!

    Steve

    Tuesday, March 20, 2007 12:04 AM
  • Thank you so much, this info is very helpful!!!Wink
    Monday, April 02, 2007 2:08 PM
  •  

    This has been a problem for me as well. I think I have it. I am getting this error when there are single quotes in the text which happens a lot when people are able to enter free text. SQL 2000 does not get this error when importing, but apparently SQL 2005 does. I remove the single quotes and all is well. Howvever, I replaced all instances of double quotes with single quotes in order to export Oracle data to a flat file and then import into SQL. Now what do I replace the double quotes with. I am using Double quotes as the text qualifier.

    Monday, April 16, 2007 3:03 PM
  • Alex1123123 :

    Have you found a work around or a confirmation that this is a bug ? I am using " as text delimiter and comma  as column delimiter and of my text file fields contains "virus found in sent message ""Need Help, account restricted.""". In 2000 it loads alright as :  virus found in sent message "Need Help, account restricted." .  How ever in 2005 SSIS, the field is treated as two fields, separating at comma in the text so floating into next field, causing failure. Any work around appreciated.

    Thanks.

    Thursday, May 31, 2007 6:49 PM
  • I am getting the same error.  Is there a servicve patch for this yet?

     

     

     

    Wednesday, August 01, 2007 11:03 PM
  • Increasing field lengths in "Advanced" tab of Flat File Source solves the problem, but there is nevertheless a bug in the Import utility in that if you try to increase the field lengths later using "Edit Mappings" the destination table is created with the correct size fields but the import still fails with the same truncation error. This needs to be highlighted if it isn't fixed. My OS is Vista Ultimate, not sure if it works for other OS.

       

     

    Wednesday, August 22, 2007 12:15 AM
  • Hi all,

    I had the same problem. However, I just changed the "OutputColumWidth" within the Advanced option of Data Source, according to the length I have previously defined for my DB in SQL Server 2000 and it works.

    In other words, If you are importind dat to an existing database, try fixing the width of columns according to the leght you defined for your fields within the table.

    Hope it helps.

    Regards.
    Wednesday, August 29, 2007 6:21 PM
  • i have tried every solution suggested by everyone with no luck. i have a flat file with about 3million rows. something is definately amiss with the wizard

    Thursday, September 13, 2007 3:12 AM
  • I have had success with both solutions.

     

    Here's my solution:


    I've had a csv-file built in excel.

    At the beginning it didn't work, with the same problems as described in this thread.

     

    So I started thinking and got this SOLUTION:

     

    I just inserted an ' at the beginning and ending of each value-cell, with an excel function of course so that every cell was clearly formatted as text.

     

    Example:

     

    Cell1

    'test'

    Now, it worked with the import wizard as well as with the sql query "bulk".

     

    I hope I could help somebody with my solution.

     

    Best regards,

    Alex

    Monday, November 05, 2007 6:30 PM
  • Nobody has yet come up with the best way of importing an Excel worksheet.   The import wizzard doesn't seem to scan an adequate number of rows to determine types and lengths.  There needs to be a way to tell it to scan the whole darn spreadsheet tab with every single cell in every single row looked at.  That's the only way to be sure.

    Access seems to be able to handle just about anything on an import so why can't SSIS?

    Bill

    Saturday, November 24, 2007 12:38 AM
  • OK, it looks to me like the best way is to use Access to import the data from Excel and then use the SSIS import and export wizard to import the table(s) from Access to SQL Server.  One rather strange thing was that the import wizard doesn't seem to be able to deal with Access 2007 files.  I had to save it as a 97-2003 Access database and then the wizard worked OK.  I also found that if any of your data in a column contains blanks it's better to import it as a text type rather than a numeric type and then you can deal with it later.

    I certainly hope the 2008 version of SQL server does a better job on this stuff.

    Saturday, November 24, 2007 1:42 AM
  • On the 2007 connection stuff it looks like there is a solution, though it wasn't at all obvious from the choices the wizard gave me.  You use the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" to connect to Excel 2007 or Access 2007 files. Here's a link to the article where I found it:

    http://msdn2.microsoft.com/en-us/library/ms178430.aspx

    Bill

    Saturday, November 24, 2007 1:48 AM
  • I also have the same issue.  It goes beyond just the import wizard.  I import excel files via a vbScript function on a website.  I have two files, one will import fine and one trunacates.  The funny thing is the field the data is trunacating is set to NText on the database side, so it shouldn't be a problem.  Second, the two files appear to be identical which is more confusing.

     

    I believe there is something with the Excel files that limits the column to 255 char's (try to expand a column beyond that number in Excel, you cant).  The question is what triggers it to botch up some columns in one file and not the other.

     

    Any help would be appreciated.

     

    Thanks,

     

    Dean.

     

    Thursday, November 29, 2007 10:32 AM
  • Monday, December 03, 2007 3:06 PM
  • If I'm reading KB 281517 correctly it says that the registry key to determine how many rows in the input data are read is currently set to 8 and you can increase it to 16 at most.  If that is correct I don't think it provides much of an improvement, but thanks for the reference anyway.  Now if one could increase it to 5,000 or so, then that might be in improvement.

     

    I notice that for CSV files, you can specify in the import wizard how many rows to scan.  It seems this should also be the case in the Excel import wizard, but it doesn't seem to provide that capability.  I still have not tried importing from an Excel 2007 file to see if it does a better job now that I know that it is possible to import from Excel 2007 if you use the "Microsoft Office 12.0 Access Database Engine OLE DB Provider".

     

    So far, I've been pretty happy with using Access 2007 to import the Excel files and then import them into SQL server from there.

     

    Thanks,

    Bill

     

    Monday, December 03, 2007 4:39 PM
  • I had the same problem (using excel). I set the reg val to 0 according to the following text in the KB..

    -----

    The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.

    -----

     

    It worked for me.

     

    Monday, December 03, 2007 5:16 PM
  • Thanks to R2 DJ,

     

    "When you enter your data source, you have an advanced tab on the left. After that, click on the offending column and one of the properties under 'Misc' on the right is OutputColumnWidth. Enter the correct value here and your problems will be gone."

     

    This was it for me.

     

    Where I think people were getting lost is we expect to set column attributes on the window where you can click Edit Mappings button which is 3 or 4 screens past where you had said to set them.

    Sunday, January 06, 2008 5:31 PM
  • You've got something there about there being a difference between the size of the field in the output table being different than that OutputColumnWidth. I don't think I'm out of the woods yet in trying to import a giant tab delimited file, but I'm getting there by increasing that parameter for the fields complaining of being truncated.

     

    Tuesday, January 08, 2008 3:41 PM
  • Here is the simple way:

     

    1. Ctrl-Shft-End this selects all the rows and fields (make sure there are field headers in the first line.

     

    2. Start up 2007 Access create a blank database.

     

    3. Once the database table is displayed Ctrl -V to paste in the records.  Access creates all of the fields and all of the rows automatically in the right format.  Save the database in Access 97-2003 format (no I do not know why the new format is not supported.)

     

    4. Then upsize or use DTS (SSIS) to import the table into SQL Server.

     

    Excel and DTS(SSIS) have always been problematic but Access is a snap.  If someone tells you that they don't have Access tell them simply to buy it.  It will save millions of wasted hours.

     

     

    Friday, February 15, 2008 9:00 PM
  • Change your FIELDTERMINATOR property to be FIELDTERMINATOR='","'

    Sunday, April 20, 2008 2:50 AM
  • This post was a great help.   Kudos to those that figured out you must use the advanced tab in the wizard to set the size of the output for the fields.
    Wednesday, April 30, 2008 2:33 PM
  • I have the same problem before.  But it was fixed after I adjust 2 things when importing flat text file.

     

    On "advance" tab

    - change datatype to String [DT_STR]   (not the text stream)

    - Make the OptputColumnWidth have bigger width

     

     

     

    Friday, June 06, 2008 1:59 AM
  • I am still getting same error.(Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "MTGXXXX" (1058)" failed because truncation occurred)

    I am trying to extract pipe delimited file from our source system to SQL table which is all varchar 255. I tried everything , increased outputwidth in advance.

    Is it because there is space after end of row?

    Please help.

     

    Saturday, June 07, 2008 2:17 AM
  • I have absolutely no idea why this is happening :-

     

    1)

    BULK INSERT dbo.[test]

    FROM

    'C:\Cust.txt'

    WITH (FIELDTERMINATOR='#')

     

    2)

     Directory of C:\

    06/15/2008  11:13 AM             7,416 CBG_Incidents.txt
    06/23/2008  10:48 AM            13,257 Cust.txt
                   3 File(s)         20,673 bytes
                   0 Dir(s)  29,826,895,872 bytes free
    3)

    Msg 4860, Level 16, State 1, Line 2

    Cannot bulk load. The file "C:\Cust.txt" does not exist.

     

    Any help will be appreciated..

     

    Thanks

    Wednesday, June 25, 2008 12:53 PM
  • this is answer. i don't know exactly but setting in "advanced" tab i can solve my problem. Thank Bob.

     

    Thursday, September 18, 2008 7:12 PM
  • On the "can't find the file" error:

    I had this same problem because it was looking at the C: drive on the server where the SQL Server is running (not the local C drive of the client you are running Mgt Studio on).  I simply moved the file up to the server and loaded it from there.

     

    Jaypee, thank you for the Bulk Insert idea, I was able to use it to get passed this problem.

    Monday, November 03, 2008 9:12 PM
  • SUBJECT: Importing quoted numbers from a flat file

    I've had trouble importing a flat file using the 2008 Import Export Wizard.  In particular, when the flat file has qualified (ie double quoted) numbers, no conversion of string or integer type to an integer (int, bigint etc) column seemed to work, and I got the truncation errors others have reported here.

    My workaround was to remove the double quotes from numbers the flat file data source, in case anyone is having the same issue.

    If anyone has had better success than this, I'd appreciate hearing about it.

    Thanks,

    NLPer

    Wednesday, March 04, 2009 5:34 PM
  • Here is the simple way:

     

    1. Ctrl-Shft-End this selects all the rows and fields (make sure there are field headers in the first line.

     

    2. Start up 2007 Access create a blank database.

     

    3. Once the database table is displayed Ctrl -V to paste in the records.  Access creates all of the fields and all of the rows automatically in the right format.  Save the database in Access 97-2003 format (no I do not know why the new format is not supported.)

     

    4. Then upsize or use DTS (SSIS) to import the table into SQL Server.

     

    Excel and DTS(SSIS) have always been problematic but Access is a snap.  If someone tells you that they don't have Access tell them simply to buy it.  It will save millions of wasted hours.

     

     

    I have been getting this error for hours. I used access and it loaded 1st time!

    Thanks
    Tuesday, May 19, 2009 4:21 PM
  • I had the same issue and resolved it by increasing the sizes of the fields in the Advanced tab "Above" and "Below" the one that SQL said was too small.  It appears that SQL Server 2005 may be reporting the wrong field back that has the size issue.

    Chris.

    Wednesday, April 07, 2010 6:41 PM
  • A few years later your post helped someone.  Totally fixed my issue.  Thanks so much!

    Monday, July 12, 2010 5:31 PM
  • Here is the simple way:

     

    1. Ctrl-Shft-End this selects all the rows and fields (make sure there are field headers in the first line.

     

    2. Start up 2007 Access create a blank database.

     

    3. Once the database table is displayed Ctrl -V to paste in the records.  Access creates all of the fields and all of the rows automatically in the right format.  Save the database in Access 97-2003 format (no I do not know why the new format is not supported.)

     

    4. Then upsize or use DTS (SSIS) to import the table into SQL Server.

     

    Excel and DTS(SSIS) have always been problematic but Access is a snap.  If someone tells you that they don't have Access tell them simply to buy it.  It will save millions of wasted hours.

     

     

    I have been getting this error for hours. I used access and it loaded 1st time!

    Thanks


    This is probably the best "short-term" and easiest way to work around this issue.  I too have spent the past few days trying to inport some data from Excel sheets into SQL 2005 tables. 

    I've been getting the "text was truncated" error, even though I've increased the size of the destination field to the max SQL will allow for nvarchar, 4000.

    Also, there are responses here to 2 different issues, one is importing from a flat file and the other importing from Excel.  Yes, you can save the Excel data as a flat file and import that and you then see the Advanced tab.  I've tried that method and even using that method and setting my output columns to 4000, I get the error.  I've pasted the offending column's data into Textpad and the widest column was 395 characters ...so using 4000 as my field size should have been more than enough.

    So, whether converting Excel to a tab-delimited flat file or whether trying to import directly from Excel, I get this truncation error.

    I'm admitting defeat at this point and will first import the data into Access, and then from Access into SQL 2005.

    Friday, January 14, 2011 5:56 PM
  • Here is the simple way:

     

    1. Ctrl-Shft-End this selects all the rows and fields (make sure there are field headers in the first line.

     

    2. Start up 2007 Access create a blank database.

     

    3. Once the database table is displayed Ctrl -V to paste in the records.  Access creates all of the fields and all of the rows automatically in the right format.  Save the database in Access 97-2003 format (no I do not know why the new format is not supported.)

     

    4. Then upsize or use DTS (SSIS) to import the table into SQL Server.

     

    Excel and DTS(SSIS) have always been problematic but Access is a snap.  If someone tells you that they don't have Access tell them simply to buy it.  It will save millions of wasted hours.

     

     

    I have been getting this error for hours. I used access and it loaded 1st time!

    Thanks


    This is probably the best "short-term" and easiest way to work around this issue.  I too have spent the past few days trying to inport some data from Excel sheets into SQL 2005 tables. 

    I've been getting the "text was truncated" error, even though I've increased the size of the destination field to the max SQL will allow for nvarchar, 4000.

    Also, there are responses here to 2 different issues, one is importing from a flat file and the other importing from Excel.  Yes, you can save the Excel data as a flat file and import that and you then see the Advanced tab.  I've tried that method and even using that method and setting my output columns to 4000, I get the error.  I've pasted the offending column's data into Textpad and the widest column was 395 characters ...so using 4000 as my field size should have been more than enough.

    So, whether converting Excel to a tab-delimited flat file or whether trying to import directly from Excel, I get this truncation error.

    I'm admitting defeat at this point and will first import the data into Access, and then from Access into SQL 2005.


    I'm also having this issue.  All my columns are mapped.  Putting it into an access database and then inserting is not an appropriate solution.

     

    Has anyone found a real solution to this yet?

    Monday, January 24, 2011 8:41 PM
  • I have change the file format from unicode to ansi , and then this error disappeared. I wonder whether this is the reason.

    • Proposed as answer by Shahed G Friday, September 28, 2012 5:15 AM
    Friday, September 09, 2011 5:52 AM
  • I was having same problem while importing a text file, "|" delimited, i tried import export Wizard, it gave me same error again and again, I tried bulk insert it gave me the exact column names which were creating problems:

    BULK INSERT [DBO].[PAR]
    FROM 'C:\05102012\PAR.TXT'
    with (FIELDTERMINATOR='|',ROWTERMINATOR='\n')

    Then I used Import Export wizard and:

    • Selected Flat File as Datasource
    • In Advanced Option in Left pane, I selected Column Name (Erroneous One)
    • In MISC, Change Datatype  to string[DT_STR]
    • In Misc, Change OutputColumnWidth to 8000

    After this change all worked fine.

    Wednesday, May 23, 2012 11:21 PM
  • perfect answer... Thank you bob!!  Also, I would just add to click back on the Data flow task again so that the new metadata settings are refreshed after changing the input source length(s).

    • Edited by MarkDBA Tuesday, July 03, 2012 3:54 PM
    Tuesday, July 03, 2012 3:52 PM