July 18, 2006 at 9:54 am
Hi,
I have created a simple DTS task that takes an Excel file in input and simply extracts its lines towards a table in my SQL Server Database. It works, BUT, I never get the right number of lines in my destination table. Moreover, the order of the lines is changed...
In the "Options" tab from my Data Transformation Task properties, I can see that the "Fetch Buffer Size", "First line" and "Last line" parameters have to do with my problem, but I don't get the logic here. If the Fetch Buffer Size is too low, the task fails when I try to run it (and the error file tells me that the buffer size is to low). If it's too high, I only get one line into my database table... And each time, the order of the lines is wrong.
Can someone help me here ?
Thanks in advance.
Antoine
SMILE company (France)
July 18, 2006 at 10:50 am
Here u mean to say that if u have 10 records in an excel sheet only one record is getting transformed to destination database table? and also could u bee more specific of order ur mentioned here?
July 18, 2006 at 11:04 am
Yes for example I have 42 lines in my Excel sheet. If I put Buffer Size to 1, I get an error, and only 21 lines that are imported in database. If I put a higher Buffer Size (2 or more), I only get ONE line imported !!!
About the order, in the first case where I only have 21 lines, they are NOT in the same order after import to the database as in the Excel sheet. I don't understand the logic here, lines are all mixed up in the database...
Thanks,
July 19, 2006 at 5:56 am
I have had numerous problems importing data from Excel files in the past. Most the issues have been caused by data and formatting issues with the spreadsheet. You should make sure there are no hidden characters such as returns or breaks in the data. I have also had issues when copying Excel data-types which default to nvarchar and float when creating a DTS package to an existing table with varchar and decimal fields. One work-around I have used to create a temp table when creating the DTS package that uses the default Excel datat-types and then copy to the final destination table.
July 19, 2006 at 7:33 am
I think issue is from the spreadsheet, open a new spreadsheet, select all cells, format them to 'TEXT' then copy your original data to this new spreadsheet.
IN SQL Server, Creat a table by "Import Data", the wisard can create a table as well as the DTS Package. Your data will be copied from Excel to Table same order and same number of rows.
July 19, 2006 at 8:51 am
OK, I have now identified the problem. It does not come from hidden or special characaters (chariot return is allowded for example), nor from data types. It comes from the fact that one of my cell contains a text that is too long... And I get the following error message "data of lign 18, column 61 is too important for specified buffer size". Till' now I thaught the "buffer size" was the "Fetch Buffer Size" of "Options" in my Data Transformation Task parameters. But it seems like it has nothing to do with it. "Fetch Buffer Size" is only used to specify the number of lines to be tranformed at th time.
So I should be able to adjust this "buffer size", but where ?... That is the question.
Thanks...
July 20, 2006 at 6:36 pm
This will point you in the right direction -
July 21, 2006 at 2:44 am
I'm so greatfull to you, you've just given to me the solution to a problem I've been trying to solve since the beginning of the week...
I should have thought about Windows Registry before... Whatever, it works now !
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply