Featured

OneITVSO data to Database

There are two different VSO’s apart from TFS. One is VSO and the Other is OneITVSO. I will cover the VSO part later in my posts. In this post I have covered all the code needed to generate .exe file which would load the workitems for us.

I had to touch on the C# code to get the OneITVSO data since, they have not enabled Analytics section to most of us. The only way you get directly to Power BI is export option in Query Editors pane as shown below. But the problem is it doesn’t allow us to schedule refresh so, we have to manually refresh and publish it every day.

Since I am no C# or .Net expert so, don’t judge the code and reuse the code and modify as you wish. And also I have not included all the columns since there are hundred of columns in VSO so you may have to modify them as well. This code has pulled 1500 are loaded in less than 15 sec so if you got huge work items then you could reduce the columns used in the code.

I have to First cover the important aspect which is references which I have struggled to get from NuGet packages.

You need to get the personal Access Token (PAT) to get access to workitems from our code. first click on Security on your profile name then you should see the below screen and then you will get the page to create a new token.

Below screenshot shows you the folder structure and App.config that I have use for project details such as sql connection string, area path, personal access token etc..,. I usually place all the code at the end in the order that I have developed so you copy one by one without any confusion.

We have used two “.cs” files two tables one for “work items” (all work items) and one for “work item links” (to get parent of work items).

We also need two sql table structures to store which are given at the end. One point to note is that, I am not sure what is the size of string returned for the each columns so i have created varchar(max) for all the columns except for the date. (nvarchar gives an error). Finally we will Merge the data loaded in two sql tables into one final table which contains Parent for all workitems.

While working I observed that server returns different DateTime when compared to local system DateTime. replace DateTime.ParseExact(TargetDate, “dd-MM-yyyy h.mm.ss tt”, null) which I used in code with DateTime.Parse(TargetDate).

You are are finally done with the code. Build and run you will get the .exe file which can be scheduled in SQL Agent Jobs. Feel free to post your comments related to errors or any issues. I will take my time in fixing your issues.

List of Columns in OneITVSO are listed in below table:

Available ColumnsUsed Columns in code
Microsoft.VSTS.Common.AcceptanceCriteriaSystem.WorkItemType
Microsoft.VSTS.CodeReview.AcceptedBySystem.Title
Microsoft.VSTS.CodeReview.AcceptedDateSystem.TeamProject
Microsoft.VSTS.Common.ActivatedBySystem.Tags
Microsoft.VSTS.Common.ActivatedDateSystem.State
Microsoft.VSTS.Common.ActivitySystem.RevisedDate
Microsoft.VSTS.Feedback.ApplicationLaunchInstructionsSystem.Rev
Microsoft.VSTS.Feedback.ApplicationStartInformationSystem.RelatedLinkCount
Microsoft.VSTS.Feedback.ApplicationTypeSystem.Reason
System.AreaIdSystem.IterationPath
System.AreaPathSystem.IterationId
System.AssignedToSystem.Id
Microsoft.VSTS.CodeReview.ContextSystem.History
Microsoft.VSTS.CodeReview.ContextCodeSystem.Description
Microsoft.VSTS.CodeReview.ContextOwnerSystem.CreatedDate
Microsoft.VSTS.CodeReview.ContextTypeSystem.CreatedBy
System.AttachedFileCountSystem.CommentCount
System.AuthorizedAsSystem.ChangedDate
System.AuthorizedDateSystem.ChangedBy
Microsoft.VSTS.TCM.AutomatedTestIdSystem.AssignedTo
Microsoft.VSTS.TCM.AutomatedTestNameSystem.AreaPath
Microsoft.VSTS.TCM.AutomatedTestStorageSystem.AreaId
Microsoft.VSTS.TCM.AutomatedTestTypeMicrosoft.VSTS.Scheduling.TargetDate
Microsoft.VSTS.TCM.AutomationStatusMicrosoft.VSTS.Scheduling.StoryPoints
System.BoardColumnMicrosoft.VSTS.Scheduling.StartDate
System.BoardColumnDoneMicrosoft.VSTS.Scheduling.RemainingWork
System.BoardLaneMicrosoft.VSTS.Scheduling.OriginalEstimate
Microsoft.Custom.Status.BriefCommentsMicrosoft.VSTS.Scheduling.FinishDate
Microsoft.Custom.BusinessOwnerMicrosoft.VSTS.Scheduling.Effort
Microsoft.Custom.BusinessSponsorMicrosoft.VSTS.Scheduling.DueDate
Microsoft.VSTS.Common.BusinessValueMicrosoft.VSTS.Scheduling.CompletedWork
System.ChangedByMicrosoft.VSTS.Common.ValueArea
System.ChangedDateMicrosoft.VSTS.Common.TimeCriticality
Microsoft.VSTS.Common.ClosedByMicrosoft.VSTS.Common.StackRank
Microsoft.VSTS.Common.ClosedDateMicrosoft.VSTS.Common.Severity
Microsoft.VSTS.CodeReview.ClosedStatusMicrosoft.VSTS.Common.Risk
Microsoft.VSTS.CodeReview.ClosedStatusCodeMicrosoft.VSTS.Common.ReviewedBy
Microsoft.VSTS.CodeReview.ClosingCommentMicrosoft.VSTS.Common.ResolvedReason
System.CommentCountMicrosoft.VSTS.Common.ResolvedDate
Microsoft.VSTS.Scheduling.CompletedWorkMicrosoft.VSTS.Common.ResolvedBy
Microsoft.Custom.CostOfDelayMicrosoft.VSTS.Common.Rating
System.CreatedByMicrosoft.VSTS.Common.Priority
System.CreatedDateMicrosoft.VSTS.Common.Issue
Microsoft.Custom.BoardColumnChangeDateMicrosoft.VSTS.Common.ClosedDate
Microsoft.Custom.DemandSourceTypeMicrosoft.VSTS.Common.ClosedBy
System.DescriptionMicrosoft.VSTS.Common.BusinessValue
Microsoft.VSTS.Scheduling.DueDateMicrosoft.VSTS.Common.Activity
Microsoft.VSTS.Scheduling.EffortMicrosoft.VSTS.Common.ActivatedDate
Security.EGRCIdMicrosoft.VSTS.Common.ActivatedBy
Security.ExceptionStatusMicrosoft.VSTS.Common.AcceptanceCriteria
Microsoft.Custom.ExecutiveOverrideMicrosoft.Custom.Requestor
System.ExternalLinkCountMicrosoft.Custom.PlannedStartDate
Microsoft.VSTS.Scheduling.FinishDateMicrosoft.Custom.BusinessOwner
Microsoft.VSTS.Build.FoundIn
Microsoft.Custom.Status.Health
System.History
System.HyperLinkCount
IcM.DeliveryType
IcM.IncidentCount
IcM.IncidentIDs
IcM.Severity
IcM.RepairItemType
System.Id
Microsoft.VSTS.Build.IntegrationBuild
Microsoft.Custom.InvestmentType
Microsoft.VSTS.Common.Issue
Microsoft.Custom.ITOwner
System.IterationId
System.IterationPath
Microsoft.Custom.LegalRegulatoryCompliance
Microsoft.VSTS.TCM.LocalDataSource
System.NodeName
Microsoft.Custom.NotToExceed
Microsoft.VSTS.Scheduling.OriginalEstimate
TfsMigrationTool.ReflectedWorkItemId
Microsoft.Custom.OutOfScope
Microsoft.VSTS.TCM.Parameters
Microsoft.Custom.PlannedStartDate
Microsoft.Custom.PortfolioApproved
Microsoft.VSTS.Common.Priority
Microsoft.VSTS.TCM.QueryText
Microsoft.VSTS.Common.Rating
System.Reason
System.RelatedLinkCount
Microsoft.VSTS.Scheduling.RemainingWork
System.RemoteLinkCount
Microsoft.VSTS.TCM.ReproSteps
Microsoft.Custom.Requestor
Microsoft.VSTS.Common.ResolvedBy
Microsoft.VSTS.Common.ResolvedDate
Microsoft.VSTS.Common.ResolvedReason
System.Rev
Microsoft.VSTS.Common.ReviewedBy
System.RevisedDate
Microsoft.VSTS.Common.Risk
Microsoft.Custom.RROE
Security.Severity
Security.ServiceHierarchyId
Security.ServiceTreeIdLink
Security.ServiceHierarchyIdType
Microsoft.VSTS.Common.Severity
Microsoft.VSTS.Common.StackRank
Microsoft.VSTS.Scheduling.StartDate
System.State
Microsoft.VSTS.Common.StateChangeDate
Microsoft.VSTS.Common.StateCode
Microsoft.Custom.Status.Accomplishments
Microsoft.Custom.Status.Planned
Microsoft.VSTS.TCM.Steps
Microsoft.VSTS.Scheduling.StoryPoints
Microsoft.VSTS.CMMI.SubjectMatterExpert1
Microsoft.VSTS.CMMI.SubjectMatterExpert2
Microsoft.VSTS.CMMI.SubjectMatterExpert3
Microsoft.VSTS.TCM.SystemInfo
System.Tags
Microsoft.VSTS.Scheduling.TargetDate
System.TeamProject
Microsoft.VSTS.TCM.TestSuiteAudit
Microsoft.VSTS.TCM.TestSuiteType
Microsoft.VSTS.TCM.TestSuiteTypeId
Microsoft.VSTS.Common.TimeCriticality
System.Title
Microsoft.VSTS.Common.ValueArea
System.Watermark
System.WorkItemType
Microsoft.Custom.WSJFStack

Final Code:

Program.cs:

using Microsoft.TeamFoundation.WorkItemTracking.WebApi;
 using Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models;
 using Microsoft.VisualStudio.Services.Common;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Data;
 using System.Data.SqlClient;
 using System.Configuration;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models;
using Microsoft.VisualStudio.Services.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OneITVSOToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            ExecuteQuery e = new ExecuteQuery();
            e.RunGetBugsQueryUsingClientLib();
            ExecuteQuery2 e1 = new ExecuteQuery2();
            e1.RunGetBugsQueryUsingClientLib();
        }
    }
    public class ExecuteQuery
    {
        readonly string _uri;
        readonly string _personalAccessToken;
        readonly string _project;
        readonly string DestinationTableWorkItem =  ConfigurationManager.AppSettings["DestinationTableWorkItem"];
        readonly string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
        readonly string areapath;
        public ExecuteQuery()
        {
            _uri = ConfigurationManager.AppSettings["uri"];
            _personalAccessToken = ConfigurationManager.AppSettings["personalAccessToken"];
            _project = ConfigurationManager.AppSettings["project"];
            areapath = ConfigurationManager.AppSettings["areapath"];
        }

        /// <summary>
        /// Execute a WIQL query to return a list of bugs using the .NET client library
        /// </summary>
        /// <returns>List of Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models.WorkItem</returns>
        public void RunGetBugsQueryUsingClientLib()
        {
            Uri uri = new Uri(_uri);
            string personalAccessToken = _personalAccessToken;
            string project = _project;

            DataTable table = new DataTable();
            table.Columns.Add("WorkItemType", typeof(string));
            table.Columns.Add("Title", typeof(string));
            table.Columns.Add("TeamProject", typeof(string));
            table.Columns.Add("Tags", typeof(string));
            table.Columns.Add("State", typeof(string));
            table.Columns.Add("Reason", typeof(string));
            table.Columns.Add("IterationPath", typeof(string));
            table.Columns.Add("Id", typeof(string));
            table.Columns.Add("History", typeof(string));
            table.Columns.Add("CreatedDate", typeof(DateTime));
            table.Columns.Add("CreatedBy", typeof(string));
            table.Columns.Add("ChangedDate", typeof(DateTime));
            table.Columns.Add("ChangedBy", typeof(string));
            table.Columns.Add("AssignedTo", typeof(string));
            table.Columns.Add("AreaPath", typeof(string));
            table.Columns.Add("TargetDate", typeof(DateTime));
            table.Columns.Add("StoryPoints", typeof(string));
            table.Columns.Add("StartDate", typeof(DateTime));
            table.Columns.Add("RemainingWork", typeof(string));
            table.Columns.Add("OriginalEstimate", typeof(string));
            table.Columns.Add("FinishDate", typeof(DateTime));
            table.Columns.Add("Effort", typeof(string));
            table.Columns.Add("DueDate", typeof(DateTime));
            table.Columns.Add("CompletedWork", typeof(string));
            table.Columns.Add("ValueArea", typeof(string));
            table.Columns.Add("TimeCriticality", typeof(string));
            table.Columns.Add("StackRank", typeof(string));
            table.Columns.Add("Severity", typeof(string));
            table.Columns.Add("Risk", typeof(string));
            table.Columns.Add("ReviewedBy", typeof(string));
            table.Columns.Add("ResolvedReason", typeof(string));
            table.Columns.Add("ResolvedDate", typeof(DateTime));
            table.Columns.Add("ResolvedBy", typeof(string));
            table.Columns.Add("Rating", typeof(string));
            table.Columns.Add("Priority", typeof(string));
            table.Columns.Add("Issue", typeof(string));
            table.Columns.Add("ClosedDate", typeof(DateTime));
            table.Columns.Add("ClosedBy", typeof(string));
            table.Columns.Add("BusinessValue", typeof(string));
            table.Columns.Add("Activity", typeof(string));
            table.Columns.Add("ActivatedDate", typeof(DateTime));
            table.Columns.Add("ActivatedBy", typeof(string));
            table.Columns.Add("AcceptanceCriteria", typeof(string));
            table.Columns.Add("Requestor", typeof(string));
            table.Columns.Add("PlannedStartDate", typeof(DateTime));
            table.Columns.Add("BusinessOwner", typeof(string));

            VssBasicCredential credentials = new VssBasicCredential("", _personalAccessToken);

            Wiql wiql = new Wiql()
            {
                Query = "Select * " +
                        "From WorkItems " +
                        "Where [System.AreaPath] = '" + areapath + "' " +
                        "And [System.TeamProject] = '" + project + "' " +
                        "Order By [State] Asc, [Changed Date] Desc"
            };

            //create instance of work item tracking http client
            using (WorkItemTrackingHttpClient workItemTrackingHttpClient = new WorkItemTrackingHttpClient(uri, credentials))
            {
                //execute the query to get the list of work items in the results
                WorkItemQueryResult workItemQueryResult = workItemTrackingHttpClient.QueryByWiqlAsync(wiql).Result;

                if (workItemQueryResult.WorkItems.Any())
                {
                    int skip = 0;
                    const int batchSize = 100;
                    IEnumerable<WorkItemReference> workItemRefs;
                    string WorkItemType, Title, TeamProject, Tags, State, Reason, IterationPath, History, CreatedBy, ChangedBy, AssignedTo, AreaPath, ValueArea, TimeCriticality;
                    string Severity, Risk, ReviewedBy, ResolvedReason, ResolvedBy, Rating, Issue, ClosedBy, BusinessValue, Activity, ActivatedBy, AcceptanceCriteria, Requestor, BusinessOwner;
                    string StoryPoints, RemainingWork, OriginalEstimate, Effort, CompletedWork;
                    string StackRank, Priority;
                    string CreatedDate, StartDate, ChangedDate, TargetDate, DueDate, FinishDate, ResolvedDate, ClosedDate, PlannedStartDate, ActivatedDate;
                    int count = 0;
                    do
                    {
                        workItemRefs = workItemQueryResult.WorkItems.Skip(skip).Take(batchSize);
                        if (workItemRefs.Any())
                        {
                            // get details for each work item in the batch
                            List<WorkItem> workItems = workItemTrackingHttpClient.GetWorkItemsAsync(workItemRefs.Select(wir => wir.Id)).Result;
                            Console.WriteLine("Loading WorkItems from {0} to {1}", skip, batchSize + skip);

                            foreach (WorkItem workItem in workItems)
                            {
                                try { WorkItemType = workItem.Fields["System.WorkItemType"].ToString(); } catch (Exception e) { WorkItemType = null; }
                                try { Title = workItem.Fields["System.Title"].ToString(); } catch (Exception e) { Title = null; }
                                try { TeamProject = workItem.Fields["System.TeamProject"].ToString(); } catch (Exception e) { TeamProject = null; }
                                try { Tags = workItem.Fields["System.Tags"].ToString(); } catch (Exception e) { Tags = null; }
                                try { State = workItem.Fields["System.State"].ToString(); } catch (Exception e) { State = null; }
                                try { Reason = workItem.Fields["System.Reason"].ToString(); } catch (Exception e) { Reason = null; }
                                try { IterationPath = workItem.Fields["System.IterationPath"].ToString(); } catch (Exception e) { IterationPath = null; }
                                try { History = workItem.Fields["System.History"].ToString(); } catch (Exception e) { History = null; }
                                try { CreatedDate = workItem.Fields["System.CreatedDate"].ToString(); } catch (Exception e) { CreatedDate = null; }
                                try { CreatedBy = workItem.Fields["System.CreatedBy"].ToString(); } catch (Exception e) { CreatedBy = null; }
                                try { ChangedDate = workItem.Fields["System.ChangedDate"].ToString(); } catch (Exception e) { ChangedDate = "01-01-1900 0.00.00 AM"; }
                                try { ChangedBy = workItem.Fields["System.ChangedBy"].ToString(); } catch (Exception e) { ChangedBy = null; }
                                try { AssignedTo = workItem.Fields["System.AssignedTo"].ToString(); } catch (Exception e) { AssignedTo = null; }
                                try { AreaPath = workItem.Fields["System.AreaPath"].ToString(); } catch (Exception e) { AreaPath = null; }
                                try { TargetDate = workItem.Fields["Microsoft.VSTS.Scheduling.TargetDate"].ToString(); } catch (Exception e) { TargetDate = "01-01-1900 0.00.00 AM"; }
                                try { StoryPoints = workItem.Fields["Microsoft.VSTS.Scheduling.StoryPoints"].ToString(); } catch (Exception e) { StoryPoints = null; }
                                try { StartDate = workItem.Fields["Microsoft.VSTS.Scheduling.StartDate"].ToString(); } catch (Exception e) { StartDate = "01-01-1900 0.00.00 AM"; }
                                try { RemainingWork = workItem.Fields["Microsoft.VSTS.Scheduling.RemainingWork"].ToString(); } catch (Exception e) { RemainingWork = null; }
                                try { OriginalEstimate = workItem.Fields["Microsoft.VSTS.Scheduling.OriginalEstimate"].ToString(); } catch (Exception e) { OriginalEstimate = null; }
                                try { FinishDate = workItem.Fields["Microsoft.VSTS.Scheduling.FinishDate"].ToString(); } catch (Exception e) { FinishDate = "01-01-1900 0.00.00 AM"; }
                                try { Effort = workItem.Fields["Microsoft.VSTS.Scheduling.Effort"].ToString(); } catch (Exception e) { Effort = null; }
                                try { DueDate = workItem.Fields["Microsoft.VSTS.Scheduling.DueDate"].ToString(); } catch (Exception e) { DueDate = "01-01-1900 0.00.00 AM"; }
                                try { CompletedWork = workItem.Fields["Microsoft.VSTS.Scheduling.CompletedWork"].ToString(); } catch (Exception e) { CompletedWork = null; }
                                try { ValueArea = workItem.Fields["Microsoft.VSTS.Common.ValueArea"].ToString(); } catch (Exception e) { ValueArea = null; }
                                try { TimeCriticality = workItem.Fields["Microsoft.VSTS.Common.TimeCriticality"].ToString(); } catch (Exception e) { TimeCriticality = null; }
                                try { StackRank = workItem.Fields["Microsoft.VSTS.Common.StackRank"].ToString(); } catch (Exception e) { StackRank = null; }
                                try { Severity = workItem.Fields["Microsoft.VSTS.Common.Severity"].ToString(); } catch (Exception e) { Severity = null; }
                                try { Risk = workItem.Fields["Microsoft.VSTS.Common.Risk"].ToString(); } catch (Exception e) { Risk = null; }
                                try { ReviewedBy = workItem.Fields["Microsoft.VSTS.Common.ReviewedBy"].ToString(); } catch (Exception e) { ReviewedBy = null; }
                                try { ResolvedReason = workItem.Fields["Microsoft.VSTS.Common.ResolvedReason"].ToString(); } catch (Exception e) { ResolvedReason = null; }
                                try { ResolvedDate = workItem.Fields["Microsoft.VSTS.Common.ResolvedDate"].ToString(); } catch (Exception e) { ResolvedDate = "01-01-1900 0.00.00 AM"; }
                                try { ResolvedBy = workItem.Fields["Microsoft.VSTS.Common.ResolvedBy"].ToString(); } catch (Exception e) { ResolvedBy = null; }
                                try { Rating = workItem.Fields["Microsoft.VSTS.Common.Rating"].ToString(); } catch (Exception e) { Rating = null; }
                                try { Priority = workItem.Fields["Microsoft.VSTS.Common.Priority"].ToString(); } catch (Exception e) { Priority = null; }
                                try { Issue = workItem.Fields["Microsoft.VSTS.Common.Issue"].ToString(); } catch (Exception e) { Issue = null; }
                                try { ClosedDate = workItem.Fields["Microsoft.VSTS.Common.ClosedDate"].ToString(); } catch (Exception e) { ClosedDate = "01-01-1900 0.00.00 AM"; }
                                try { ClosedBy = workItem.Fields["Microsoft.VSTS.Common.ClosedBy"].ToString(); } catch (Exception e) { ClosedBy = null; }
                                try { BusinessValue = workItem.Fields["Microsoft.VSTS.Common.BusinessValue"].ToString(); } catch (Exception e) { BusinessValue = null; }
                                try { Activity = workItem.Fields["Microsoft.VSTS.Common.Activity"].ToString(); } catch (Exception e) { Activity = null; }
                                try { ActivatedDate = workItem.Fields["Microsoft.VSTS.Common.ActivatedDate"].ToString(); } catch (Exception e) { ActivatedDate = "01-01-1900 0.00.00 AM"; }
                                try { ActivatedBy = workItem.Fields["Microsoft.VSTS.Common.ActivatedBy"].ToString(); } catch (Exception e) { ActivatedBy = null; }
                                try { AcceptanceCriteria = workItem.Fields["Microsoft.VSTS.Common.AcceptanceCriteria"].ToString(); } catch (Exception e) { AcceptanceCriteria = null; }
                                try { Requestor = workItem.Fields["Microsoft.Custom.Requestor"].ToString(); } catch (Exception e) { Requestor = null; }
                                try { PlannedStartDate = workItem.Fields["Microsoft.Custom.PlannedStartDate"].ToString(); } catch (Exception e) { PlannedStartDate = "01-01-1900 0.00.00 AM"; }
                                try { BusinessOwner = workItem.Fields["Microsoft.Custom.BusinessOwner"].ToString(); } catch (Exception e) { BusinessOwner = null; }

                                table.Rows.Add(WorkItemType, Title, TeamProject, Tags, State, Reason, IterationPath, workItem.Id, History, DateTime.ParseExact(CreatedDate, "dd-MM-yyyy h.mm.ss tt", null), CreatedBy, DateTime.ParseExact(ChangedDate, "dd-MM-yyyy h.mm.ss tt", null), ChangedBy, AssignedTo,
                                               AreaPath, DateTime.ParseExact(TargetDate, "dd-MM-yyyy h.mm.ss tt", null), StoryPoints, DateTime.ParseExact(StartDate, "dd-MM-yyyy h.mm.ss tt", null), RemainingWork, OriginalEstimate, DateTime.ParseExact(FinishDate, "dd-MM-yyyy h.mm.ss tt", null), Effort, DateTime.ParseExact(DueDate, "dd-MM-yyyy h.mm.ss tt", null), CompletedWork, ValueArea, TimeCriticality,
                                               StackRank, Severity, Risk, ReviewedBy, ResolvedReason, DateTime.ParseExact(ResolvedDate, "dd-MM-yyyy h.mm.ss tt", null), ResolvedBy, Rating, Priority, Issue, DateTime.ParseExact(ClosedDate, "dd-MM-yyyy h.mm.ss tt", null), ClosedBy, BusinessValue,
                                                Activity, DateTime.ParseExact(ActivatedDate, "dd-MM-yyyy h.mm.ss tt", null), ActivatedBy, AcceptanceCriteria, Requestor, DateTime.ParseExact(PlannedStartDate, "dd-MM-yyyy h.mm.ss tt", null), BusinessOwner);
                                //Console.Write(" "+count++);
                            }
                        }
                        skip += batchSize; 
                    }
                    while (workItemRefs.Count() == batchSize);
                }
                else
                {
                    Console.WriteLine("No work items were returned from query.");
                }

                using (var bulkCopy = new SqlBulkCopy(ConnectionString))
                {
                    // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
                    foreach (DataColumn col in table.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.DestinationTableName = DestinationTableWorkItem;
                    bulkCopy.WriteToServer(table);
                }
            }
        }
    }
}

WorkItemLinks.cs:

using Microsoft.TeamFoundation.WorkItemTracking.WebApi;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models;
using Microsoft.VisualStudio.Services.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OneITVSOToSQL
{

    public class ExecuteQuery2
    {
        readonly string _uri;
        readonly string _personalAccessToken;
        readonly string _project;
        readonly string DestinationTableWorkItemLink = ConfigurationManager.AppSettings["DestinationTableWorkItemLink"];
        readonly string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
        readonly string areapath;
        public ExecuteQuery2()
        {
            _uri = ConfigurationManager.AppSettings["uri"]; 
            _personalAccessToken = ConfigurationManager.AppSettings["personalAccessToken"]; 
            _project = ConfigurationManager.AppSettings["project"];
            areapath = ConfigurationManager.AppSettings["areapath"];

        }
        public void RunGetBugsQueryUsingClientLib()
        {
            Uri uri = new Uri(_uri);
            string personalAccessToken = _personalAccessToken;
            string project = _project;

            DataTable table = new DataTable();
            table.Columns.Add("Source", typeof(string));
            table.Columns.Add("Target", typeof(string));
            table.Columns.Add("Rel", typeof(string));

            VssBasicCredential credentials = new VssBasicCredential("", _personalAccessToken);

            //create a wiql object and build our query
            Wiql wiql = new Wiql()
            {
                Query = "Select * " +
                        "From workitemLinks " +
                        "Where [Source].[System.AreaPath] = '" + areapath + "'" 
            };

            //create instance of work item tracking http client
            using (WorkItemTrackingHttpClient workItemTrackingHttpClient = new WorkItemTrackingHttpClient(uri, credentials))
            {
                //execute the query to get the list of work items in the results
                WorkItemQueryResult workItemQueryResult = workItemTrackingHttpClient.QueryByWiqlAsync(wiql).Result;

                if (workItemQueryResult.WorkItemRelations.Any())
                {
                    int skip = 0;
                    const int batchSize = 100;
                    IEnumerable<WorkItemLink> workItemRefs;
                    string rel, source, target;
                    int count = 0;
                    do
                    {
                        workItemRefs = workItemQueryResult.WorkItemRelations.Skip(skip).Take(batchSize);
                        if (workItemRefs.Any())
                        {
                            // get details for each work item in the batch
                            //List<WorkItem> workItems = workItemTrackingHttpClient.GetWorkItemsAsync(workItemRefs.Select(wir => wir.Id)).Result;
                            Console.WriteLine("Loading WorkItems from {0} to {1}", skip, batchSize + skip);
                            foreach (WorkItemLink workItemLink in workItemRefs)
                            {
                                try { source = workItemLink.Source.Id.ToString(); } catch (Exception e) { source = null; }
                                try { target = workItemLink.Target.Id.ToString(); } catch (Exception e) { target = null; }
                                try { rel = workItemLink.Rel.ToString(); } catch (Exception e) { rel = null; }

                                table.Rows.Add(source, target, rel);
                                //Console.Write(count++);
                            }
                        }
                        skip += batchSize;
                    }
                    while (workItemRefs.Count() == batchSize);
                }
                else
                {
                    Console.WriteLine("No work items were returned from query.");
                }

                using (var bulkCopy = new SqlBulkCopy(ConnectionString))
                {
                    // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
                    foreach (DataColumn col in table.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.DestinationTableName = DestinationTableWorkItemLink;
                    bulkCopy.WriteToServer(table);
                }
            }
        }
    }
}

App.config: (donot change this name which is default name)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="ConnectionString" value="Server = ; Database = ; User ID =; Password ="/>
    <add key="uri" value="https://microsoftit.visualstudio.com"/>
    <add key="personalAccessToken" value=""/>
    <add key="project" value="OneITVSO"/>
    <add key ="DestinationTableWorkItem" value="WorkItems"/>
    <add key ="DestinationTableWorkItemLink" value="WorkItemLink"/>
    <add key ="areapath" value="OneITVSO\..."/>
  </appSettings>
</configuration>

WorkItems Table:

CREATE TABLE [WorkItems](
     [WorkItemType] varchar NULL,
     [Title] varchar NULL,
     [TeamProject] varchar NULL,
     [Tags] varchar NULL,
     [State] varchar NULL,
     [Reason] varchar NULL,
     [IterationPath] varchar NULL,
     [Id] varchar NULL,
     [History] varchar NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] varchar NULL,
     [ChangedDate] [datetime] NULL,
     [ChangedBy] varchar NULL,
     [AssignedTo] varchar NULL,
     [AreaPath] varchar NULL,
     [TargetDate] [datetime] NULL,
     [StoryPoints] varchar NULL,
     [StartDate] [datetime] NULL,
     [RemainingWork] varchar NULL,
     [OriginalEstimate] varchar NULL,
     [FinishDate] [datetime] NULL,
     [Effort] varchar NULL,
     [DueDate] [datetime] NULL,
     [CompletedWork] varchar NULL,
     [ValueArea] varchar NULL,
     [TimeCriticality] varchar NULL,
     [StackRank] varchar NULL,
     [Severity] varchar NULL,
     [Risk] varchar NULL,
     [ReviewedBy] varchar NULL,
     [ResolvedReason] varchar NULL,
     [ResolvedDate] [datetime] NULL,
     [ResolvedBy] varchar NULL,
     [Rating] varchar NULL,
     [Priority] varchar NULL,
     [Issue] varchar NULL,
     [ClosedDate] [datetime] NULL,
     [ClosedBy] varchar NULL,
     [BusinessValue] varchar NULL,
     [Activity] varchar NULL,
     [ActivatedDate] [datetime] NULL,
     [ActivatedBy] varchar NULL,
     [AcceptanceCriteria] varchar NULL,
     [Requestor] varchar NULL,
     [PlannedStartDate] [datetime] NULL,
     [BusinessOwner] varchar NULL
 )                                                          

WorkItemLink Table:

CREATE TABLE [WorkItemLink](
     [Source] varchar NULL,
     [Target] varchar NULL,
     [Rel] nvarchar NULL
 ) 

VSO.WorkItems Table:

CREATE TABLE [dbo].[WorkItems](
	[WorkItemType] [varchar](max) NULL,
	[Title] [varchar](max) NULL,
	[TeamProject] [varchar](max) NULL,
	[Tags] [varchar](max) NULL,
	[State] [varchar](max) NULL,
	[Reason] [varchar](max) NULL,
	[IterationPath] [varchar](max) NULL,
	[Id] [varchar](max) NULL,
	[History] [varchar](max) NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedBy] [varchar](max) NULL,
	[ChangedDate] [datetime] NULL,
	[ChangedBy] [varchar](max) NULL,
	[AssignedTo] [varchar](max) NULL,
	[AreaPath] [varchar](max) NULL,
	[TargetDate] [datetime] NULL,
	[StoryPoints] [varchar](max) NULL,
	[StartDate] [datetime] NULL,
	[RemainingWork] [varchar](max) NULL,
	[OriginalEstimate] [varchar](max) NULL,
	[FinishDate] [datetime] NULL,
	[Effort] [varchar](max) NULL,
	[DueDate] [datetime] NULL,
	[CompletedWork] [varchar](max) NULL,
	[ValueArea] [varchar](max) NULL,
	[TimeCriticality] [varchar](max) NULL,
	[StackRank] [varchar](max) NULL,
	[Severity] [varchar](max) NULL,
	[Risk] [varchar](max) NULL,
	[ReviewedBy] [varchar](max) NULL,
	[ResolvedReason] [varchar](max) NULL,
	[ResolvedDate] [datetime] NULL,
	[ResolvedBy] [varchar](max) NULL,
	[Rating] [varchar](max) NULL,
	[Priority] [varchar](max) NULL,
	[Issue] [varchar](max) NULL,
	[ClosedDate] [datetime] NULL,
	[ClosedBy] [varchar](max) NULL,
	[BusinessValue] [varchar](max) NULL,
	[Activity] [varchar](max) NULL,
	[ActivatedDate] [datetime] NULL,
	[ActivatedBy] [varchar](max) NULL,
	[AcceptanceCriteria] [varchar](max) NULL,
	[Requestor] [varchar](max) NULL,
	[PlannedStartDate] [datetime] NULL,
	[BusinessOwner] [varchar](max) NULL,
	[Parent] [int] NULL
) 

Merge statmenet Load into above Table:

merge VSO.workitems as d
using (
select w.*,convert(int,l.Parent) Parent from WorkItems w left join (
select  Target id,Source Parent from WorkItemLink 
 where rel in('System.LinkTypes.Hierarchy-Forward') ) l
 on l.id=w.Id
) as s
on d.id=s.id
when not matched then insert 
([WorkItemType], [Title], [TeamProject], [Tags], [State], [Reason], [IterationPath], [Id], [History], [CreatedDate], [CreatedBy], [ChangedDate], [ChangedBy], [AssignedTo], [AreaPath], [TargetDate], [StoryPoints], [StartDate], [RemainingWork], [OriginalEstimate], [FinishDate], [Effort], [DueDate], [CompletedWork], [ValueArea], [TimeCriticality], [StackRank], [Severity], [Risk], [ReviewedBy], [ResolvedReason], [ResolvedDate], [ResolvedBy], [Rating], [Priority], [Issue], [ClosedDate], [ClosedBy], [BusinessValue], [Activity], [ActivatedDate], [ActivatedBy], [AcceptanceCriteria], [Requestor], [PlannedStartDate], [BusinessOwner],[Parent])
values
([WorkItemType], [Title], [TeamProject], [Tags], [State], [Reason], [IterationPath], [Id], [History], [CreatedDate], [CreatedBy], [ChangedDate], [ChangedBy], [AssignedTo], [AreaPath], [TargetDate], [StoryPoints], [StartDate], [RemainingWork], [OriginalEstimate], [FinishDate], [Effort], [DueDate], [CompletedWork], [ValueArea], [TimeCriticality], [StackRank], [Severity], [Risk], [ReviewedBy], [ResolvedReason], [ResolvedDate], [ResolvedBy], [Rating], [Priority], [Issue], [ClosedDate], [ClosedBy], [BusinessValue], [Activity], [ActivatedDate], [ActivatedBy], [AcceptanceCriteria], [Requestor], [PlannedStartDate], [BusinessOwner],[Parent])
when matched then update set 
d.WorkItemType=s.WorkItemType
,d.Title=s.Title
,d.TeamProject=s.TeamProject
,d.Tags=s.Tags
,d.State=s.State
,d.Reason=s.Reason
,d.IterationPath=s.IterationPath
,d.History=s.History
,d.CreatedDate=s.CreatedDate
,d.CreatedBy=s.CreatedBy
,d.ChangedDate=s.ChangedDate
,d.ChangedBy=s.ChangedBy
,d.AssignedTo=s.AssignedTo
,d.AreaPath=s.AreaPath
,d.TargetDate=s.TargetDate
,d.StoryPoints=s.StoryPoints
,d.StartDate=s.StartDate
,d.RemainingWork=s.RemainingWork
,d.OriginalEstimate=s.OriginalEstimate
,d.FinishDate=s.FinishDate
,d.Effort=s.Effort
,d.DueDate=s.DueDate
,d.CompletedWork=s.CompletedWork
,d.ValueArea=s.ValueArea
,d.TimeCriticality=s.TimeCriticality
,d.StackRank=s.StackRank
,d.Severity=s.Severity
,d.Risk=s.Risk
,d.ReviewedBy=s.ReviewedBy
,d.ResolvedReason=s.ResolvedReason
,d.ResolvedDate=s.ResolvedDate
,d.ResolvedBy=s.ResolvedBy
,d.Rating=s.Rating
,d.Priority=s.Priority
,d.Issue=s.Issue
,d.ClosedDate=s.ClosedDate
,d.ClosedBy=s.ClosedBy
,d.BusinessValue=s.BusinessValue
,d.Activity=s.Activity
,d.ActivatedDate=s.ActivatedDate
,d.ActivatedBy=s.ActivatedBy
,d.AcceptanceCriteria=s.AcceptanceCriteria
,d.Requestor=s.Requestor
,d.PlannedStartDate=s.PlannedStartDate
,d.BusinessOwner=s.BusinessOwner,
d.Parent=s.Parent;
Advertisements
Featured

“Run As” the tools with different user

Hey DAXers,

Might be Irrelevant to my posts but I am troubled with this at one point of time to “Run AS” tools or IDE’s.

If you are running on client machines with different logins and want to run SSMS or Visual Studio on your login. This case happens when you might be the only one who have access to resources. Below are the commands that you can run to get the IDE run with your alias name.

For SQL Server: runas /netonly /user:domain\YourLoginUserID “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe”

For Visual Studio:
runas /netonly /user:domain\YourLoginUserID “C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\devenv.exe”

Mapping with Date Table based on date column

In my earlier post, I have given you the date table for which you can relate with our data sets we load into Power BI. Please refer below URL if you need Date table:

Adding Date Table to Power BI

We can create Date key using below calculated column using which we can map DateKey in Date Table to below column. We can now use all the date table columns in our report like month-year,fiscal year,current year functionalities.

Finally, Relationship should look like below which forms One To many with Date Table:

DateKey = YEAR([date])&RIGHT(“0″&MONTH(‘Date'[date]),2)&RIGHT(“0″&DAY(‘Date'[date]),2)

Sort Month year column in Date Table

Hey Daxer,

Date table that we created earlier from the post here will need some tweaks to address few scenarios. (Please Visit the link to get the Date table).

To apply Sort on the power BI visuals based on Month Year column in Date table which we have imported earlier, create a calculated column as below:

Month Year Sort = SWITCH([Year],2000,0,2002,12,2003,24,2004,36,2005,48,2006,60,2007,72,2008,84,2009,96,2010,108,2011,120,2012,132,2013,144,2014,166,2015, 288, 2016, 300,2017,312 ,2018, 324, 2019, 336,2020, 348) + [Month]

Now click on Month year column and go to modeling section (on the home line), under which select Sort by Column option and then click on “Month Year Sort” Column which we have created.

Confused… refer below screenshot…

DAX trouble with Previous month…

Hey DAXers!!!

If you dont want the situation (boring by the way…) you could copy the last dax of this post.

Now that relative filtering is available for date in power bi this will be easy but earlier we need dax to calculate. May be in some cases while writing previous month calculated value in dax, you need it too…

I came across situation one day that all my Power BI reports begin to show blank values for the previous month. Then I came to know that I had messed up my dax beginner tag days.

I wrote below Dax query for current month, to pin the value of current month metric to dashboard. Create a calculated Column for below:

DefaultCurrentMonth = IF(AND(MONTH(TODAY())=MONTH(Date[Date]),YEAR(TODAY())=YEAR(Date[Date])),”yes”,”no”)

The same way, after few days, one of the client asked Previous month so I wrote just by subtracting -1 for month. so when we enter January month will be 1 and 1-1=0 which caused to break. Now to solve,
Create a calculated Column for below: :

Default Previous Month = IF(MONTH(TODAY())<>1,IF(AND(MONTH(TODAY())-1=MONTH(Date[Date]),YEAR(TODAY())=YEAR(Date[Date])),”Yes”,”No”),if(AND(MONTH(Date[Date])=12,YEAR(TODAY())-1=YEAR(Date[Date])),”Yes”,”No”))

Wondered ever why Power BI Date operations is a pain behind the Pocket…

Hey DAXers!!!

In Power BI, there are lot of ways that we implement Date table in our day-to-day activities but the easy way of doing is having it in our database table or just sql table or a CSV…

Instead of we working hard for the Date operations below script will work for us:

https://drive.google.com/file/d/1nX6RyRBltdsYBI2Q5fhYq2YzfYngZvXI/view?usp=sharing

Download the File and import to Power BI as CSV file which will act as date dimension or Date table which has built with most possible combinations of date. In upcoming posts, will add missing features of Date table which would help in solving Date issues.

No one can explain you better than Yourself:

Thanks for joining me!

Good company in a journey makes the way seem shorter. — Izaak Walton

               This is my first post in the blog so i start with my childhood view to the cognizant view that i have gained….

  I still remember at age of 10 or so I asked my Grand Father staring at sky while sleeping beside to him on bed outside the home “What if sky fall on us???” then he replied “sky is strongly adhered to wall on the other side, it won’t fall”. U know I am very dumb during my childhood whatever they say I believe, because one say to make others believe right. In contrast, now with my cognizance, do you know what would I say “before it falls we go and sleep inside the home” see how cinematic I am.


              As I grew, I have gone through financial set back in family then I came to know the real world of living. The soft and dumb sole has gone through tough times around the people. Never thought of troubling family for what I am going through. I had two good friends with whom I usually hangout for escaping from the sadness in me. The people around me started to respect not because I have done great thing for them but for studying for myself. The more I am good in studies the more I got the respect.


                In the process I became a geek not having any contacts with the world. People then again started to disrespect me. I have enough education but not the words to communicate with others. The more we involve into something the more we get but not what world wanted. I became a reserved person to others, If they ask something about subject I can give a lecture but what to speak other than that. People used to say I am mum in front of me. It hurts, really It hurts.


               Then I again tried to change myself before it get worse. So geek turned to normal again by not involving too much into studies but just rough sketch on concepts, the Geek inside  didn’t leave me. Even today the geek thing doesn’t leave me, which I admire of myself and the other rough side I am still learning (communication) is balancing the geeky. whatsoever, I tried myself to be better.


                There is no challenge in the world until we try for some thing. We Loose, We Win. I have lot of failures than success, never counted though. I have done mistakes, in my failures, even in my successes. I almost reached dead end for my decisions but handled it good enough for what I am to consider now.


               Whatever you are facing is what world expects from you. If you feel “Why am I the only one facing all the bad things, then consider that you are the only one who can face it”.