SQLTeam.com | Weblogs | Forums

How to specifically position multiple queries in a single spreadsheet using SSIS

sql2012

#1

I have created an SSIS package which processes daily financial information in a SQL Server database. At the end of these processes I need to output to an excel template for management to review. Some of these reports are laid out in a way that is not just a tabular output but requires customized placement of data on the template to specific cells which I am having trouble to figure out how to do this. For example in the first query resultset the data placement is in the range of A4:G4. The second query resultset is using ranges C9:C12 and the last query resultset is using ranges C15:C18. How can I customize the placement of these resultset to excel with using SSIS.


#2

you can't do that unless you write a script component that launches excel and does the data manipulation/placement. Of course that will require Excel to be installed on the server running the package.


#3

Hi gbritton,

  Do you have any examples or links that might show me how I could script this out?  Any help would be much appreciated. 

Thanks,
Joe


#4

Try here:

Automating Excel


#5

Thanks gbritton this is a great example.

Also if you are interested there was an article I just found that allows you to define a range using a range name in an excel template which will allow you to importing data from SSIS SQL Server to Excel using a specific location on that spreadsheet which does give you some flexibility with selecting custom cells without having to code but I can see the coding giving a tremendous amount of flexibility in addition.

Thanks again.

Thanks,
Joe