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
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?