Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: dts package help

    I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.

    I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:

    Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
    Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).

    I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.

    Any help is appreciated.
    Last edited by -Dman100-; 04-18-06 at 00:38.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.

    I appreciate the help. Thank you.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.

    -PatP

  5. #5
    Join Date
    Jan 2004
    Posts
    124
    I double checked and I did specify an Excel file as the connection, not a text file.

    It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •