Summary
Visual Studio Online is cloud version of TFS which we can connect using Visual Studio to retrieve projects collections and work items. The VSO does not give access to any backend database like CRM tools and exposes its data through REST API and ODATA.
Requirement : Pull project and work item collection from VSO and store the same in SQL Azure, which will be used for analysis.
Solution : Create SSIS package to connect to VSO using OData connection and pull VOS data like project , work items etc and connect to SQL Azure and store the desired data.
Prerequisite
- Install Odata for SSIS from ( https://www.microsoft.com/en-us/download/details.aspx?id=42280 )
- Create alternate account password in VSO, the same will be used in SSIS Odata connection manager.
Steps to create SSIS package
Create connection to Azure and VSO
Open SSDT and create new SQL Server Integration Service project, add a new package and go to the connection manager and right click, you will get the below window.
After clicking on new connection it takes to another window where we have to select OData.
Your connection details should look like the below for VSO.
AccountName - The collection path user is assigned to.
Password - The alternate password set in VSO.
AccountName - User account to login to VSO
Similarly create SQL Azure - Oledb connection which looks like the below one
Understand the VSO Odata service location
The VSO odata service is exposed at https://tfsodata.visualstudio.com/ which has detailed spacification of specifying the URL for different object collection.
Next is we can drag the Odata source and place in Dataflow task so the final look will be as below.
The WorkItem source details looks like shown below.
