SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


This table contains cells that are outside the range of cells....


This table contains cells that are outside the range of cells....

Author
Message
cafescott
cafescott
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 733
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,
cafescott
cafescott
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 733
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. ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search