I have created an SSIS package which processes daily financial information to a sql server database.  These processes are to be outputted to excel spreadsheets to a readable report format for management to review.  Some of these reports are laid out in a way that is not just tabular output but requires customized placement of data on an excel spreadsheet to specific cells.  I am able to place an initial resultset of a query output from the database in a tabular excel template through SSIS but the issue is at the end of that placement in the spreadsheet I am required to place another output below that tabular output in a different format from the initial output which I have shown below. Attached I have an example of what one of the excel reports should look like.

So what is desired output? To Update data in the column C for TVI?

Hi Uri, The desired output is to first have a template with all the headers and position the data below the headers and title. For example the first resulset from the DB should be positioned from range A4:G4. The second result set from a query should be below the thrift title which is from range A9:C12. And the last query result set should be positioned below TVI which is range A15:C18. If I use the excel component in Visual Studio SSIS it will return the first dataset at the very last line in the spreadsheet. If you have a template as I show in the example it would put the first resultset below TVI which is positioned incorrect which basically giving you no control on the positioning. I am assuming this would need to have some sort of customization script task. The real question here is how can I have control on the positioning of the resultset of the data the is returned from the database. your help is greatly appreciated. Joe

Hi Joe,

If I understand correctly, you want to load data into excel file with in a specific range.

To achieve your requirement, we can create the ranges in the excel file in advance. Please select the cell range in your excel sheet, then right-click the range to define a name for the range to create some ranges with the specific ranges in excel file, then in the "Name of the Excel Sheet" drop down box of "Excel Destination Editor" you can see that Named range. Then simply select the range from the drop-down list or use a variable with the range name as the Excel Destination.

Thanks,
Katherine Xiong

Thx Katherine for your response.  Do you have an example that shows this type of solution?  Can this be done through SSIS which is what I am trying to use?  I hope the diagrams clarifies how I am trying to design these reports.

Thanks,

Joe

• Edited by SQLPilot747 15 hours 14 minutes ago Added diagram for more clarity

Thx Katherine for your response.  Do you have an example that shows this type of solution?  Can this be done through SSIS which is what I am trying to use?  I hope the diagrams clarifies how I am trying to design these reports.

Thanks,

Joe

• Edited by SQLPilot747 Tuesday, September 08, 2015 3:49 PM Added diagram for more clarity

Thx Katherine for your response.  Do you have an example that shows this type of solution?  Can this be done through SSIS which is what I am trying to use?  I hope the diagrams clarifies how I am trying to design these reports.

Thanks,

Joe

• Edited by SQLPilot747 Tuesday, September 08, 2015 3:49 PM Added diagram for more clarity

Hi Joe,

The following blog about import data to Excel sheet's specific region is for your reference:
http://getsetsql.blogspot.com/2012/01/using-ssis-load-data-to-excel-sheet-at.html

Thanks,
Katherine Xiong

Got it to work but had a question.  When I define the name range it always seems to skip the first row.  Is there a way to prevent this from happening.  Also when you input into the named range can you overwrite existing data that may exist?

Joe

Hi Joe,

As to the first issue, when the first row act as column name, it would always be skipped. To avoid this issue, we can uncheck the 'First row has column names' in Excel Connection Manager or define a name range with a row more for skip.

As to the second issue, the Excel Connection Manager does not have a setting that allows overwriting the data. So we cannot achieve this goal at this moment. If you have concern about this, it is my pleasure to help you to reflect your recommendation to the proper department for their consideration. Please feel free to submit your situation on our product to the following link https://connect.microsoft.com/SQLServer/. Your feedback is valuable for us to improve our products and increase the level of service provided.

Thanks,
Katherine Xiong

Got everything working.  Thanks Katherine for all your help as you have been a really great help.  I will place my feedback for further recommendation to future releases.