SSIS only 255 rows uploaded ?
Hi,
I have a simple Data Flow with an Excel Source and an OLE DB Destination to upload data from an Excel file to an SQL table. The Excel file has 8155 rows. Once the package is finished running, the SQL table only shows 255 rows. The data access mode is Table or view - fast load.
Any suggestions?
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
January 23, 2008 7:53 PM
UPDATED:
February 22, 2008 3:24 PM
Answer Wiki:
How many rows does SSIS show have been transfered?
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
It shows only 255 rows being transferred.
I also tried the Data Access mode as an SQL command
select * from `sheet1$`
but SSIS still shows the first 255 rows being transferred.
If I write an SQL command to fetch me a record after 255 rows, no rows are returned.
On the other hand, the same command to fetch me a record in the first 255 rows,returns that row.
I tried to use another Excel file with 30,000 records, just one column, with dummy values, all cells have the General format. All 30,000 rows were uploaded.
However, the Excel file with 8155 rows still extracts 255 rows in the Excel Source. All cells have the General format in this file, too.
What gives?
Actually, some cells have Text format. If that is the cause of the problem, can you suggest a fix? considering that I am given the Excel file, and cannot set the format of the cells.
Thanks
You can try BCPing in the file. I think BCP will process an Excel file.
You can also try setting up a linked server to the Excel file and access the data that way.
I would also add loggin to your package to find if any errors are being reported when the data is uploaded. Add a data viewer to the package in the BIDS to view the rows as they are moving across and see if more rows are being read and moving through your package.
Its deifficult to tell where the error is, could be the excel source itself, or your sql destination table. Check that there are no restrictions on your sql table which could prevent the upload of data, and if formatting in your excel sheet is a problem, you can always use a data conversion task to convert the data into the correct format.