June 5, 2014 at 12:15 pm
Hello again. I had my SSIS package working; it gathers data and depending whom is currently being referenced, copies the appropriate spreadsheet and fills it with the data. As my end users requested changes to the output spreadsheets I had to change the column names and remove a couple of fields from the output. Now, the package is broken. I continue to receive this error: "An OLE DB error has occurred...This table contains cells that are outside the range of cells defined in this spreadsheet."
I've researched the error and there aren't many solutions for it. I wish that Microsoft was more helpful in dealing with problems with the Excel Connection Manager, since this area of SSIS sure is maddening to work with at times.
I've spent most of today deleting and recreating my Data Flow that fills the spreadsheet and the error persists. I've restarted VB a few times too. I'm following these steps:
In the Data Flow task drop an OLE DB Source. I select the view I'm using. It has 10 fields. They all show up on the Internal and External Column lists. I verify there are 10 fields.
I put a Data Conversion task after the OLE DB Source. I map all ten fields to their Unicode equivalents, making the Output Alias for each one the same as the Input column name.
Then I put the dreaded Excel Connection control down, connected to the Data Conversion. I ensure that the Connection Manager is a spreadsheet template with 10 columns. This template has a worksheet named "Excel_Destination." I specify a SQL command:
select * from
[Excel_Destination$A4:J4]
In the Mapping section I use the converted fields in the order with which they will appear in the spreadsheet. I look at the template spreadsheet and verify that J is the last column. ("J" is the 10th letter in the alphabet.)
I run the SSIS package and continue to get the error. Now, in the past I have just recreated the steps above for hours and hours and suddenly the package would work. However, does anyone have more systematic way of solving this issue?
Thanks,
June 6, 2014 at 7:49 am
The problem has gone away. In my Data Flow, in the Excel Destination, I changed it from using the 'SQL command' to the 'table or view' option. Then I clicked the "New" button. First I got a warning (I forgot to write it down). Then the "create table" SQL statement appears. I choose my Excel sheet (Excel_Destination$, which I mentioned in the top post). Now when I run the package, I don't get that error.
I think that when I click "New" that SSIS is rebuilding the internal table that exists within the spreadsheet. That's my explanation for what seems to be a very obscure and mysterious area of the SSIS to Excel realm.
At least I can move forward. I like SSIS when it is working. 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply