#ProjectOnline data via #ODATA and #SSIS in #SQL database table on-premise #MSProject #SharePointOnline #BI #SSRS #Office365 #Cloud

March 26, 2014 at 1:24 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Installation, Reporting, T-SQL | 8 Comments
Tags: , , , , , , , ,

Following on from my post earlier today regarding the SSIS component that enables you to extract data from ODATA feeds in Office 365, I have created a blog post that demonstrates this new functionality. See the steps below:

Firstly I installed the SQL Server data tools and Integration Services feature on my Test Project Server / SharePoint 2013 box (only server I had available at the time). Once you have access to SQL Server data tools, download and install the ODATA SSIS component from the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=42280

Also if this isn’t on a SharePoint / Project Server 2013 server – which in reality it wont be, you will also need the SharePoint client DLLs, these can be downloaded here:

http://www.microsoft.com/en-in/download/details.aspx?id=35585

When I tried the new SSIS component on the SQL 2012 Server the connection failed and it complained about the SharePoint.Client.Runtime.dll:

image

I didn’t test deploying the SharePoint client components to the SQL Server but I guess this should work.

Once everything is in place, launch SQL Server data tools for SQL Server 2012 and create a new Integration Services project:

image

Click OK and you will see the following screen:

image

On the control flow tab, at the bottom you will see Connection managers. Right click and create a new OLEDB Connection to the target database:

image

Before I created this, I did create a new empty SQL database:

image

Create another connection to the ODATA feed URL using the New Connection > ODATA option:

image

Create the ODATA connection to your ODATA tenant:

image

Specify an account that has access to the ODATA feed. Notice the Service document location is just the base ProjectData ODATA URL.

Click the All button and change the Microsoft Online Services Authentication to True:

image

Test the connection to confirm it is successful then click ok.

Add a Data Flow Task to the control flow:

image

Double click the data flow task and you will go to the Data flow tab:

image

Drag ODATA source from the toolbox to the data flow sheet:

image

Double click the ODATA source to see the properties:

image

Complete the details, selecting the ProjectOnlineODATAFeed connection and you will see the Collection property load the collection, in this case it is the tables on the ODATA feed:

image

Once loaded you will see the familiar ODATA entities:

image

In this example we will only use the Projects feed:

image

Click the Columns page on the left hand side and select the columns you want:

image

Click OK.

Drag the Destination Assistant from the SSIS toolbox to the Data Flow sheet and the new destination window will open:

image

Select the previously created SQL Server OLE DB connection:

image

Click OK.

image

Link the ODATA Source to the OLE DB Destination component using the blue data flow, then double click the OLEDB Destination component to load the editor – click yes to the warning / pop up:

image

Click the New button next to to the table details to create a new table, give the table a name, replace “OLE DB Destination” with the table name you want:

image

Click OK. At this point if we look at the SQL database we will see the new table and columns:

image

Back in the SSIS package click the Mappings page, here you can see the field mappings from the ODATA feed to the SQL table:

image

Click OK and you will see the following flow:

image

Run the package using the green arrow:

image

Once run successfully you will see the following:

image

If we switch back to the SQL server and see what is stored in the table you will see the project data from your Project online tenant:

image

Now the data is local you can use SQL Server Reporting Services to create reports, you can integrate this data into other LOB systems or create a custom OLAP cube etc. Once you are happy with the package you can set the package to run on a schedule to periodically copy the data down. There are plenty of references on the web for creating SSIS packages so have a search.

This is a basic example but hopefully gives you the idea of what is now possible Smile

About these ads

8 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Paul, great set of steps thanks for doing this. I am stuck on the step after you Dbl Click the OLEDB Destination Component. A popup msg says “This component has no available input columns”. I click yes to this msg then I do not have the “New” button next to the table details. Any idea on what I am missing. Thanks for your post

    • Hi Andrew, I have updated the post to make it clearer. You need to link the ODATA source to the OLEDB destination component. before the New button will appear next to the table option. Without the data flow link the OLEDB component doesn’t know what data / structure you need in a table so the New button will not be present. Hope that helps? Paul

      • Paul,, Thank-you that work. All connected and data downloaded. Regards Andrew

      • great to hear, Paul

  2. […] the new SSIS OData Connector for SQL 2012 since first hearing about it at Project Conf last month, Paul Mather wrote up a great step-by-step guide on getting it all up and running here so have a look at that […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.

Follow

Get every new post delivered to your Inbox.

Join 661 other followers

%d bloggers like this: