December 10, 2009 at 2:33 am
Error is on the Excel Source component.
All columns in the spreadsheet are text.
Component is reading a named range starting in row 9
There are some non-alphabet characters (/ and -) in the first 8 rows. Record 878 is the first occurrence of &.
Googling the error proposed setting IMEX=1. I changed the connection string on the Excel connection to:
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\filename.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
The explanation matched my situation but adding this switch produced no change in the outcome.
I tried changing the excel source componient to use a sql query instead of mapping the table direct. I can preview the entire data with this SQL, but I still get the error when I run the package.
Driving me nuts ...
😉
B2B
December 11, 2009 at 3:13 am
Not a lot of forum interest in this one atm, so I gave up on SSIS.
Just for the record, I've done the whole thing with stored procs. Using the below code allowed me to read the range into a temporary table that I dropped after I had processed it.
SELECT *
INTO tblTempExcelTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Workbook.xls',
'SELECT * FROM [Range_Name]')
December 11, 2009 at 3:35 am
born2bongo (12/11/2009)
Not a lot of forum interest in this one atm, ...
Just for your future information, there are several contributory reasons for the lack of answers, I suspect.
1) Your post is just a long statement of what happened, leaving the reader to guess the question.
2) The error message which you were receiving, which I am guessing is the subject of the thread, has been truncated (by SSC.com?)
3) You provided no sample data.
But well done in solving the problem via your alternative method.
December 11, 2009 at 3:49 am
The details are in the header because in the past I've found I don't get a response if there is nothing specific to interest. Looks like I need to copy the details from the header to the body as well
Always difficult to know how to pitch it.
I posted my solution because I know people often stumble across a thread some time later, when they have the same problem. (And I don't always remember the answer :rolleyes:)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply