Excel PowerPivot.: How to execute SQL query inside PowerPivot

Power Pivot is one of the best faeture in excel. Though PowerPivot can be used to serve different purposes, however; this post only include: how you can run your SQL code into Powerpivot and manipulte the data into the excel?

Why?

If you use Excel Power Pivot as front end tool then by implementing this process you can avoid asking developer a new excel sheet each time the data is updated.

How?

Before you start you must need to have Powerpivot enable in your excel, if so then your excel Tabs are like below:

Fig 1: Different tabs in the excel

If you don't find the tab 'POWERPIVOT' in your excel then you need to work a little more to add that. Please follow the below link to make it work: 

Step 1 :Make connection with your data source 

Fig 2: Manage option under POWERPIVOT

After you click the manage window you will find a popup window which like below, from there you will have option to connect with data source.

Fig 3. Connect with the data source

You need to follow the wizard to complete the connection. 

Step 2: PowerPivot Design

After you establish connection with the data source, then click on Design tab as like below

                         Fig 4: Design Tab from powerpivot manager

Under the design window you wil find following Tabs like picture and you need to choose 'Table properties'

Fig 5: Design- table properties

And then you need to click Table properites where you will write/paste the SQL code to generate the data directely from the data source.

Fig 6: Add SQl to the table property window

After putting the SQL code in the above window,  Please click 'validate' button before you click the 'Save' button; it will validate your SQL code. Now press 'Save' button then SQL query will run and get the data from the database and populate the data in your excel.

Excel connection Manager Error: Error Code 0x80004005

I was working with loading data from list of excel to the SQL database and the package was using SSIS foreach loop container, Excel source and OLEDB destination.

while I was running the package Excel connection Manager was giving the error as like below screenshot (Fig 1)

Fig 1: Error found at excel connection manager

Problem started when I have changed source from specific file to the whole folder path from the expression window:

                         Fig 2: Expression window from excel connection manager property

Error message was like below:

"Excel Connection Manager"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005 "

I was looking into fix the issues, after googling it I found different things can happen with the same error code: 0x80004005, however; after trying different way I found the fix which is changing the Run64BitRunTime property from TRUE to FALSE. 

How to do?

At first. right click on the Project under solution explorer and open the property window which look like below:

Fig 3: Find out the property window

Now from the property window, go to the Configuaration Properties->Debugging and then change the value for Run64BitRunTime from TRUE to FALSE and then click OK to save the changes.

                        

Fig 4. Update property value

It may still show error when you click Excel source and edit it, however if you run the package or loop container it will run and save the data to the database.