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,