![\"Visual](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/08\/Visual-Studio-Team-Services-logo-150x150-1.png\"){kind=logo}
<\/a><\/p>\nIn this blog, we will learn\u00a0How to read data from Azure DevOps in SSIS and load into SQL Server Table.<\/strong>\u00a0Azure DevOps was formally known as TFS Online \/ VSTS.<\/p>\n In this article we will learn, how to read Issues (Work Items) using Azure DevOps API (i.e. VSTS API). In last few years Microsoft has changed marketing names few times for TFS Service. For example everything started with\u00a0 Visual Source Safe (VSS) > TFS > TFS Online > Visual Studio Online > VSTS (Visual Studio Team Services)) and finally now its called Azure DevOps<\/strong>. In this article we might use terms like VSTS or TFS\u00a0 Online or\u00a0Azure DevOps<\/strong> but not to get confused its all about Calling Azure Dev Ops API.<\/strong><\/p>\n In this article we will try to cover few topics such as.<\/p>\n We will go through the steps to read Issues data from Visual Studio Team Service and Load into SQL Server using\u00a0\u00a0SSIS REST API Task<\/a> and\u00a0SSIS JSON Source.<\/a><\/p>\n In nutshell,\u00a0this post will focus on how to call VSTS API using SSIS.<\/p>\n So let’s get started.<\/p>\n There are two ways to call Azure DevOps API. You can use OAuth 2.0<\/a> or use PAT (i.e. Personal Access Token)<\/a>\u00a0In the next section we will see how to use OAuth 2.0 if you like more secure way to authenticate (OAuth 2.0 requires more steps).<\/p>\n Microsoft offers Static API Token (referred as PAT – Personal Access Token) which you can use like a password (it remains valid for 1 year before you have regenerate).<\/p>\n So let’s get started.<\/p>\n Call Azure DevOps API (formally known as VSTS \/ TFS Online) – Create Personal Access Token (i.e. PAT)<\/p><\/div><\/li>\n Change Permission \/ Expiration Date for Azure DevOps PAT (Personal Access Token) – Read \/ Write Work Items, Call Analytics API Example<\/p><\/div><\/li>\n<\/ol>\n Now lets configure JSON Source to read WorkItems by calling\u00a0 Azure DevOps Analytics API (in Previous few sections we call different set of APIs). Check this link<\/a> for more information on AzureDevOps Analytics API (its OData format).<\/p>\n For demo purpose we are calling WorkItems entity but you can read from many other entities like (Read Areas, Iterations, Projects, Teams, Tags etc).\u00a0Check this link<\/a> to see which Entity (i.e. Table) you can query for specific OData API version.<\/p>\n So let’s get started<\/p>\n Configure JSON Source – Call Azure DevOps Api using PAT (Personal Access Token)<\/p><\/div>\n <\/p>\n Now in this section we will see how to use more secure but little harder approach to authenticate. We will use OAuth 2.0 this time rather than static token (PAT) which we used in previous section.<\/p>\n In order to start, we will show several examples. ZappySys includes an\u00a0SSIS JSON Source<\/a>\u00a0that will help you to call VSTS API, read various data from VSTS with SSIS, create a new issue on VSTS with SSIS and do many more actions using REST API. To learn more about VSTS API check this help file<\/a>.<\/p>\n To access VSTS data using REST API call you need to create a VSTS app.\u00a0Follow the mentioned steps below to register VSTS OAuth App:<\/p>\n Visual Studio Team Service: My Apps<\/p><\/div>\n Visual Studio Team Service: My Apps[\/caption]<\/li>\n Visual Studio Team Service: New OAuth App Registration<\/p><\/div><\/li>\n Visual Studio Team Service: Select Authorization Scopes<\/p><\/div><\/li>\n Visual Studio Team Service: Your Apps with App\u2019s Credentials<\/p><\/div><\/li>\n<\/ol>\n finally, we are ready with an App. Let\u00b4s start with an example. In this example, we will show how to retrieve all Issues data from Visual Studio Team Service – TFS Online and create a new Issue in Visual Studio Team Service – TFS Online Work Items List under your Project with SSIS.<\/p>\n Now once you registered OAuth app in the Visual Studio Team Service – TFS Online website we can move to SSIS Part.<\/p>\n Connection Manager Panel: Select Connection<\/p><\/div><\/li>\n SSIS Connection Mangers List: Select O-AUTH Connection Manager<\/p><\/div><\/li>\n If you don’t know the ProjectID, then you can also enter ProjectName in place of ProjectID in Request URL. you can also call GET Request for getting Lists of Projects. the Request URL for getting the\u00a0list of Projects is:<\/p>\n For this, we will use\u00a0SSIS REST API Task<\/a><\/p>\n Drag and Drop REST API Task<\/p><\/div>\n<\/div>\n<\/li>\n * <\/strong>Select Request URL Access Mode to Url from Connection. SSIS REST API Tsk: Request Tab Configurations<\/p><\/div>\n Create New Variable from Variable Menu<\/p>\n SSIS User Variables: Create a new Variable and set Value<\/p><\/div>\n # Response Tab:\u00a0<\/strong><\/p>\n * Select Response Content Type:<\/strong> Json SSIS REST API Task: Response Tab Configurations<\/p><\/div><\/li>\n Drag and Drop JSON Source Component<\/p><\/div><\/li>\n here,\u00a0We have used 2 Parameters.<\/strong><\/p>\n * {accountName} <\/strong>is the name of the Visual Studio Team Services – TFS Online account. You can use Variable too in your Request URL, to make URL Dynamic<\/strong>, See the below-attached Screenshot. <\/li>\n<\/ol>\n SSIS JSON Source Component: Enter the Request URL<\/p><\/div>\n here, SearchText:\u00a0The SearchText for filtering WorkItems based on the keywords match. SSIS JSON Source Component: Configurations of Settings Tab<\/p><\/div><\/li>\n SSIS JSON Source Component: Pagination Tab Settings<\/p><\/div><\/li>\n SSIS JSON Source Component Configurations: Select Filter<\/p><\/div><\/li>\n SSIS JSON Source Component: See the Preview<\/p><\/div><\/li>\n Drag and Drop OLE DB Destination Component<\/p><\/div><\/li>\n OLE DB Destination Component – Configure for dumping WorkItems of Project from VSTS data in SQL Table<\/p><\/div><\/li>\n OLE DB Destination – Column Configurations<\/p><\/div><\/li>\n Fiddler – See All The Requests<\/p><\/div><\/li>\n Now let’s look at one more example to call Visual Studio Team Service(VSTS) – TFS Online REST API. This time we will create a new WorkItem using REST API call in SSIS. For this, we will use\u00a0SSIS REST API Task<\/a><\/p>\n Drag and Drop REST API Task<\/p><\/div><\/li>\n REST API Task: Configurations<\/p><\/div><\/li>\n Create New WorkItem in VSTS Account: Do Test Request\/Response<\/p><\/div><\/li>\n VSTS Site: See the Newly Created WorkItem<\/p><\/div><\/li>\n In this blog, we learned how to read Visual Studio Team Services – TFS(Team Foundation Server) Online data in SSIS, How to register an OAuth App for Visual Studio Team Service REST API, How to get all WorkItems data from Visual Studio Team Service\u00a0 – TFS(Team Foundation Server) Online and create a new WorkItem on VSTS with SSIS using\u00a0ZS JSON Source Component<\/a>\u00a0and\u00a0SSIS REST API Task<\/a> in a very simple way. You can achieve many more functionalities with the use of both of these tools. Check our blogs\/articles on REST API Task\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-rest-api-task\/<\/a>\u00a0and JSON Source Component\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-json-source\/<\/a> to find out what\u00a0these tools<\/em>\u00a0are capable of more.<\/p>\n Finally, you can use the following links for more information about the use of VSTS API with our tools:<\/p>\n Introduction In this blog, we will learn\u00a0How to read data from Azure DevOps in SSIS and load into SQL Server Table.\u00a0Azure DevOps was formally known as TFS Online \/ VSTS. In this article we will learn, how to read Issues (Work Items) using Azure DevOps API (i.e. VSTS API). In last few years Microsoft has […]<\/p>\n","protected":false},"author":6,"featured_media":4646,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[225,17,205,10],"tags":[614,6,25,26,160,271,3,12,370,368,367],"yoast_head":"\r\n\n
Prerequisite
\n<\/span><\/h2>\n\n
Call Azure DevOps API Using PAT (Personal Access Token)<\/span><\/h2>\n
Create PAT (Personal Access Token)<\/h3>\n
\n
<\/a><\/a>Call Azure DevOps OData API in SSIS – Read WorkItems\u00a0 Example (Analytics API Service)<\/h3>\n
\n
\n<\/li>\n
\n<\/li>\n\n
https:\/\/analytics.dev.azure.com\/YOUR_COMPANY_NAME\/Odbc\/_odata\/v3.0\/WorkItems?$select=WorkItemId,Title,WorkItemType,State,CreatedDate&$orderby=CreatedDate desc&$top=11<\/pre>\n<\/li>\n
\n
<\/a>Calling Azure DevOps API in SSIS using OAuth 2.0<\/h2>\n
Create VSTS App (for OAuth 2.0 API Call)<\/h3>\n
\n
<\/a>https:\/\/zappysys.com\/oauth<\/pre>\n<\/li>\n
<\/a>
\nFor example to read Work Items or Create new Issues programmatically you can check following scope (i.e. OAuth permission).Check – Work items (full)\u00a0 <\/strong>(i.e.\u00a0vso.work_full scope)
\n<\/strong><\/p>\n<\/a><\/a>Load VSTS data to SQL Server using SSIS JSON Source<\/h3>\n
Configure OAuth Connection Manager<\/h4>\n
\n
<\/a><\/a><\/a>SSIS OAuth Connection Manager: Advanced Tab Configurations<\/li>\n<\/a><\/li>\n
\nScope must be valid one from this list<\/a> (only those scope allowed which are checked during app creation). For example during app creation you check Work items (full) then you can enter vso.work_full<\/strong> in scope.<\/a><\/p>\nHow to get ProjectID?<\/h4>\n
https:\/\/{accountName}.visualstudio.com\/_apis\/projects?api-version=4.1<\/pre>\n\n
<\/a>
\nSome basic configurations need to be done in SSIS REST API Task.
\n# Request Tab:\u00a0<\/strong><\/p>\n
\n* <\/strong>Select Url Connection to your configured ZS-OAuth Connection Manager.
\n* <\/strong>Enter Request URL:<\/strong>\u00a0https:\/\/zappysys.visualstudio.com\/_apis\/projects?api-version=4.1
\n* <\/strong>Select HTTP\u00a0Request Method:<\/strong>\u00a0GET<\/p><\/blockquote>\n<\/a><\/a>
\n* Set Response Content Filter Expression:<\/strong>\u00a0$.value[1].id
\n* check the checkbox of\u00a0Save Response Content
\n<\/strong>* <\/strong>Set Save Mode <\/strong>to Save to Variable
\n* Select your Variable:<\/strong> {{User::ProjectID}}<\/p><\/blockquote>\n<\/a>Configure JSON Source Component<\/h4>\n
\n
<\/a>
\nEnter Web URL like this:<\/p>\nhttps:\/\/{accountName}.almsearch.visualstudio.com\/{project}\/_apis\/search\/workitemsearchresults?api-version=4.1-preview.1<\/pre>\n
\n<\/strong>* {project} <\/strong>is\u00a0Project ID or project name\u00a0(For more See this\u00a0Visual Studio Team Service API Documentation for getting filtered Issues data Request API<\/a>)
\nWe have used Search Work Items for Fetching Paginated Work Items(Issues, Bugs)<\/p>\n
\nTo make URL dynamic first create an SSIS variable like below screenshot and then change URL to use Variable Placeholder<\/a> like this<\/p>\nhttps:\/\/zappysys.almsearch.visualstudio.com\/{{User::ProjectID}}\/_apis\/search\/workitemsearchresults?api-version=4.1-preview.1\r\n<\/pre>\n<\/a>JSON Source Configuration for Pagination<\/h4>\n
\n
\nRequest Method: POST
\nBody Content-Type: JSON (application\/json)
\nRequest Body:
\n<\/strong><\/strong><\/p>\n{\r\n \"searchText\": \"a\",\r\n \"$skip\": <%page%>,\r\n \"$top\": 100,\r\n \"filters\": {\r\n \"System.TeamProject\": [\r\n \"ProductTesting\"\r\n ]\r\n }\r\n}\r\n<\/pre>\n
\n$skip:<\/strong> Number of results to be skipped
\n$top:<\/strong> Number of results to be returned.
\n
\nSystem.TeamProject: ProjectName in which you want to do filtering of WorkItems.<\/p>\n<\/a><\/a><\/a><\/a>Configure OLE DB Destination Component for dumping Contacts data into SQL<\/h4>\n
\n
<\/a><\/a><\/a><\/a>Create a new WorkItem on Visual Studio Team Service – TFS Online (Use SSIS REST API Task)<\/h2>\n
\n
<\/a>::::: Request Settings :::::\r\n\r\n* Select Request URL Access Mode to select Url from Connection.\r\n* Select Url Connection: Select your created OAUTH Connection Manager.\r\n* Enter Reqeust URL\r\n Request URL: POST https:\/\/{accountName}.visualstudio.com\/{project}\/_apis\/wit\/workitems\/${type}?api-version=4.1\r\n here, \r\n {accountName} - The name of the Visual Studio Team Services account.\r\n {project} - Project ID or project name\r\n {type} - WorkItems Type (Task, Bug, Issue, Requirement etc)\r\n* Select HTTP Request Method as PATCH method\r\n* Enter Request Body.\r\n Request Body:\r\n [\r\n {\r\n \"op\": \"add\",\r\n \"path\": \"\/fields\/System.Title\",\r\n \"value\": \"Sample task\"\r\n }\r\n ]\r\n* Select Body Content Type: Text\/Plain\r\n*Enter Request Header:\r\n Content-Type: application\/json-patch+json\r\n(For more Information See this\u00a0Visual Studio Team Service - TFS Online API Documentation for Creating\u00a0a\u00a0new\u00a0WorkItem\r\nhttps:\/\/docs.microsoft.com\/en-us\/rest\/api\/vsts\/wit\/work%20items\/create?view=vsts-rest-4.1)<\/pre>\n<\/a><\/a><\/a>Conclusion<\/span><\/h2>\n
References<\/span><\/h2>\n
\n