refreshing excel pivot table from ssis

Question

refreshing excel pivot table from ssis

StillaLearner

SSCrazy

Points: 2636
More actions
November 4, 2007 at 6:08 pm

#176644

my apologies if someone has already cover this but I cannot find any help on it so far.
Here's what I need to do:
I have a DTS package migrated to SSIS and this works OK.
The bigger job is an MS access app that then takes a bunch of the results (in a atble) and opens an excel spead sheet and refreshes it and then emails this.
I am new to SSIS and I believe I have to start with the DataFlow Job but not sure from this
Can someone please point out to me how I should do this - I want to keep this as simple as possible - both for me and from a maintenance point of view for the person after me
thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

vasile.t SSCommitted Points: 1636 More actions · Answer 1

in data flow task, you add an OLE DB source data adapter for the Access database. I think you should use then an Excel destination data adapter; you connect the Ole DB with the Excel adapter with the green arrow.

StillaLearner SSCrazy Points: 2636 More actions · Answer 2

thanks for your reply.

I didn't want to use the access app anymore and trying to replace it with an SSIS built solution.

Essentially I have a bunch of sql scripts that extract and manipulate data and it saves the results into a table in the db.

The excel sheets are pivot tables with effectivly the sql select * from tablename into a preformatted pivot template.

The refresh is just the same as if you were to open the excel spreadsheet, click the refresh ! and save the new version of the data and then email that spreadsheet.

thanks all I need to do - after running the sql updates - open a spreadsheet template and refresh the data in it and then email that refreshed excel to a bunch of users

thanks

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

You may want to take a look at Reporting Services.

Greg E

vasile.t SSCommitted Points: 1636 More actions · Answer 4

If the current solution doesn't have e-mail subscriptions, the users might be delighted to have some Reporting Services.

StillaLearner SSCrazy Points: 2636 More actions · Answer 5

thanks for your replies - I had already recommended reporting services as the best solution but for political - NOT technical reasons I am forced to try and kluge something together in SSIS, and I'm new to SSIS.

I am currently trying to replace a reporting type process where they have DTS jobs and a MS App using VB that does all the manipulations using excel and then sending the spreadsheets off to various people via email.

I have determined that I cannot do this (haven't found a solution) I don't have Office 2007 which would give me the com libs as we are only on Office 2003, and no other solutions.

Could I do this using activeX script task? But I don't know the language - is there a script out there that I could hack?

If I dfon't get any joy soon - I will be just restricted to migrating the DTS jobs into SSIS and still running the ms access apps

thanks

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

I'd be inclined to let Excel (and the users) do the work and pull the data rather than you pushing it. If, on a worksheet, one creates a pivot table from an external source (your query on your server) the users can refreshed it as needed by simply clicking the refresh data (red exclamation mark). Among other advantages, the pivot table source data is not constrained by Excel's row limit.

All that is required is to be sure that the users have select privileges on the query.

Would it make sense to create the initial workbook and distribute it to your users?

James Goodwin Hall of Fame Points: 3691 More actions · Answer 7

Pretty much anything you can do in Access VBA you can do with an ActiveX script task. You can use VBScript for an ActiveX script so the conversion isn't outrageously difficult. There are caveats, of course. For example, VBScript doesn't type variables so you can't do:

Dim xl as Excel.Workbook

you have to do:

Dim xl
Set xl = CreateObject("Excel.Workbook")

And, if you are planning on running this as an automated job, MS-Office will need to be installed on your SQL Server.

Good Luck,

JimFive

StillaLearner SSCrazy Points: 2636 More actions · Answer 8

Jim, James - thanks for your replies.

This is a long standing process - and they have always done the refresh and email out of the refreshed spreadsheet from this section.

Govt dept users aren't very receptive to new ideas!

I will give the activeX scripts another go later - after I have converted and optimised all the DTS code.

Having said that i am not a developer and have not done any series coding for oh about 10 years now and do not know VB or C## or anything beyoung pascal, some basic and cobol!

I get the feeling I'm going to struggle with this one - so please be patient with me

thanks

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 9

Set the Excel External Data Range Properties to "Refresh data on file open", and your government users (like mine) will not even know the difference. If you want to tell them that you are running a batch process to push the new data to them, I won't tell them any different.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 10

And I believe that credentials get cached in Excel. Which would make Reporting Services a better option. We do a lot against the cube - saves us a lot of time as security seems more integrated into the product. For example - securing a user to a Region.

Running Excel on your server is not really recommended by Microsoft either.

You might be stuck between a rock and a hard place. It would be much easier and better for you if the users would accept some of the newer technology. Quite the jump for a non programmer to jump into SSIS.

Much better for all if the developer gets the requirements and can chose the best way to address them. The user becoming the artictect usually doesn't result in a scalable solution.

I used to do quite a bit in Excel, but haven't the last few years. Any version has a very robust programming environment. In 15 minutes, we can run and email 150 + spreadsheets with no user involvement from RS. Seems a bit more scalable than the things I did in Excel.

My users were happy when they heard we could automate some of their Excel work for them. They also didn't seemed to fear that it would replace them, but that they had more productive things than pushing data around. Building something that replicates a tool you have available doesn't seem very cost effective.

Hope you find something that works for you. And my government dollars are well spent. ;>)

Maybe you could talk them into a proof of concept next year......

Greg E

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

1. Using Windows authentication, there is no need for Excel to cache the credentials.

2. There is no need to run Excel on the server in order for an external data source to refresh on open.

"...but that they had more productive things than pushing data around. Building something that replicates a tool you have available doesn't seem very cost effective."

I'd argue that opening a workbook is not "pushing data around". And if the "tool you have available" is Excel, the capability to automatically refresh the external data on open is built in.

(In fairness, perhaps I am just bitter that we have been unable to get RS installed, but I doubt that I would be tempted to use it in this case.)

StillaLearner SSCrazy Points: 2636 More actions · Answer 12

Hi Guys - thanks for all the input. I will certainly have a look at the auto refresh on open of the excel as I believe RS won't be installed - at least during the life of this contract.

I'm essentially here to port and optimise the dts packages into SSIS.

And yes I did tell them I had no experience using SSIS except knowing how to install and spell it - but they hired me anyway!

thanks

Rajiv-474102 SSC Rookie Points: 32 More actions · Answer 13

I am working on similar situation. What I did so far is that I transfered the rows from database in one worksheet of excel sheet. then I write macro to create pivot table in another sheet and in the third sheet Graph is generated automatically from the pivot table.

On final stage I will hide the first worksheet where data is in tabular format. So final result would be when any user open the excel sheet, they will get refreshed pivot table and chart.

Let me know if you guys need more info from me.

Thanks

StillaLearner SSCrazy Points: 2636 More actions · Answer 14

Rajiv - thanks for your outline. I am not at this stage looking to replace the excel spreadsheet they have - it works and the pivot tables work OK.

I am optimising the code, and processes - trying to streamline this. At the moment its all heavy on user intervention.

Provided I can get the code efficient - (can't update stats or create/delete indices in the prod environment though) I can at least get the source tables for the excel to refresh off quicker - they should be happy about it.

Once this is all done then I try and get them to send out the excel one final time and have the refresh on open set up.

thanks all