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:
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)
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 Columns | Used Columns in code |
| Microsoft.VSTS.Common.AcceptanceCriteria | System.WorkItemType |
| Microsoft.VSTS.CodeReview.AcceptedBy | System.Title |
| Microsoft.VSTS.CodeReview.AcceptedDate | System.TeamProject |
| Microsoft.VSTS.Common.ActivatedBy | System.Tags |
| Microsoft.VSTS.Common.ActivatedDate | System.State |
| Microsoft.VSTS.Common.Activity | System.RevisedDate |
| Microsoft.VSTS.Feedback.ApplicationLaunchInstructions | System.Rev |
| Microsoft.VSTS.Feedback.ApplicationStartInformation | System.RelatedLinkCount |
| Microsoft.VSTS.Feedback.ApplicationType | System.Reason |
| System.AreaId | System.IterationPath |
| System.AreaPath | System.IterationId |
| System.AssignedTo | System.Id |
| Microsoft.VSTS.CodeReview.Context | System.History |
| Microsoft.VSTS.CodeReview.ContextCode | System.Description |
| Microsoft.VSTS.CodeReview.ContextOwner | System.CreatedDate |
| Microsoft.VSTS.CodeReview.ContextType | System.CreatedBy |
| System.AttachedFileCount | System.CommentCount |
| System.AuthorizedAs | System.ChangedDate |
| System.AuthorizedDate | System.ChangedBy |
| Microsoft.VSTS.TCM.AutomatedTestId | System.AssignedTo |
| Microsoft.VSTS.TCM.AutomatedTestName | System.AreaPath |
| Microsoft.VSTS.TCM.AutomatedTestStorage | System.AreaId |
| Microsoft.VSTS.TCM.AutomatedTestType | Microsoft.VSTS.Scheduling.TargetDate |
| Microsoft.VSTS.TCM.AutomationStatus | Microsoft.VSTS.Scheduling.StoryPoints |
| System.BoardColumn | Microsoft.VSTS.Scheduling.StartDate |
| System.BoardColumnDone | Microsoft.VSTS.Scheduling.RemainingWork |
| System.BoardLane | Microsoft.VSTS.Scheduling.OriginalEstimate |
| Microsoft.Custom.Status.BriefComments | Microsoft.VSTS.Scheduling.FinishDate |
| Microsoft.Custom.BusinessOwner | Microsoft.VSTS.Scheduling.Effort |
| Microsoft.Custom.BusinessSponsor | Microsoft.VSTS.Scheduling.DueDate |
| Microsoft.VSTS.Common.BusinessValue | Microsoft.VSTS.Scheduling.CompletedWork |
| System.ChangedBy | Microsoft.VSTS.Common.ValueArea |
| System.ChangedDate | Microsoft.VSTS.Common.TimeCriticality |
| Microsoft.VSTS.Common.ClosedBy | Microsoft.VSTS.Common.StackRank |
| Microsoft.VSTS.Common.ClosedDate | Microsoft.VSTS.Common.Severity |
| Microsoft.VSTS.CodeReview.ClosedStatus | Microsoft.VSTS.Common.Risk |
| Microsoft.VSTS.CodeReview.ClosedStatusCode | Microsoft.VSTS.Common.ReviewedBy |
| Microsoft.VSTS.CodeReview.ClosingComment | Microsoft.VSTS.Common.ResolvedReason |
| System.CommentCount | Microsoft.VSTS.Common.ResolvedDate |
| Microsoft.VSTS.Scheduling.CompletedWork | Microsoft.VSTS.Common.ResolvedBy |
| Microsoft.Custom.CostOfDelay | Microsoft.VSTS.Common.Rating |
| System.CreatedBy | Microsoft.VSTS.Common.Priority |
| System.CreatedDate | Microsoft.VSTS.Common.Issue |
| Microsoft.Custom.BoardColumnChangeDate | Microsoft.VSTS.Common.ClosedDate |
| Microsoft.Custom.DemandSourceType | Microsoft.VSTS.Common.ClosedBy |
| System.Description | Microsoft.VSTS.Common.BusinessValue |
| Microsoft.VSTS.Scheduling.DueDate | Microsoft.VSTS.Common.Activity |
| Microsoft.VSTS.Scheduling.Effort | Microsoft.VSTS.Common.ActivatedDate |
| Security.EGRCId | Microsoft.VSTS.Common.ActivatedBy |
| Security.ExceptionStatus | Microsoft.VSTS.Common.AcceptanceCriteria |
| Microsoft.Custom.ExecutiveOverride | Microsoft.Custom.Requestor |
| System.ExternalLinkCount | Microsoft.Custom.PlannedStartDate |
| Microsoft.VSTS.Scheduling.FinishDate | Microsoft.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 |
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;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”
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…
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”))
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.
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”.
DAXer 