Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 28th, 2008, 04:54 AM
Registered User
Join Date: May 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Disconnected edit - Ole db properties (ms 2000)


We use Sql Server 2000 (still).

I'm trying to import an Excel file with using DTS. In the beginning of the file (the first 100 rows) the first column contains strings of only few characters (like 'R03' and 'J01X'). Somewhere on the 100th row the column contains a string of about 400 characters. Since DTS only uses the top 8 rows to figure out the space it needs, the DTS execution will always end up with error message 'Data for source column 1 is too large for the specified buffer size'. The column size in the destination table is varchar(1000).

One workaround is to sort Excel rows so that rows which contain long strings are on top. As such this is not an option for me, because I need to maintain the original row order for further processing. (By the way, can I be sure that DTS doesn't mess with row order while importing the data? I have an incremental id as the first column in my destination table.) Ok, I could add an extra column with an incremental id manually to my excel file and then sort the file any way I would like to. Yet, I find this a poor solution, one reason being that I would have to do it manually every time I have a new file. This means I would need more instructions for person(s) who do it + it's more prone to human errors.

I know it's possible to use 'Disconnected edit -> Connections -> Text file (source) -> OLE DB properties -> Max characters per delimited row' for text files. Yet when using Excel file quite the same properties are not available.

After a long background explanation, what I would like to know is: do anyone of you know a link to a place where the OLE DB properties for Edit all package properties dialog window are described so that I could figure out if there's a property value which I could modify in order to get this work. Or maybe someone knows already which property I should modify? Any other solutions are also the most welcome.

Thanks in advance,
Old June 4th, 2008, 09:49 PM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

First try converting the excel file to csv file tab delimited and run your import

Jaime E. Maccou

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database migration MS Access 2003 to MS SQL 2000 ayazhoda SQL Server 2000 3 April 23rd, 2007 11:38 AM
OLE-DB grstad Classic ASP Basics 7 January 28th, 2007 09:25 AM
MS OLE DB Provider For "Sql Server"/"ODBC Drivers" ZArrinPour SQL Server 2000 1 April 20th, 2006 09:30 AM
remote connection to MS SQL 2000 db Adam H-W Classic ASP Databases 0 July 19th, 2005 02:54 AM
MS OLE DB Provider for ODBC -not working 4 me humour General .NET 6 August 3rd, 2004 03:19 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.