Data warehouses; the backbone of BI and Marketing Analysis. I recall one client who requested a small data warehouse for internal BI purposes.  They wanted to include Microsoft Team Foundation Server (TFS) data as part of the warehouse.  I needed to figure out a simple efficient way to quickly automate importing this data.

 

During my investigation, multiple online sources suggested writing either C# code, or PowerShell scripts to access the TFS API.  Admittedly, I never used C# before and neither solution looked easy to implement. But in my research, I learned about the TFPT command line utility and had a ‘Eureka!’ moment. The TFPT.exe utility is part of TFS Power Tools add-on; a tool that can be used to access the API from the command line – could it be that easy?!

 

One must be able to run a command line from an SSIS package.  If that can be done, then said developer should be able to string the two pieces together; thus having a simple process task that can execute TFPT to hit the API and pull the data needed.

 

I started out with a .WIQ file; an XML file with all instructions necessary to query the TFS server. It held information about the TFS server, including a specific query to pull selected data from said server.   Alternatively, I could’ve used a saved query on the TFS server.

 

Let’s walk step by step through the process.

 

Prerequisites: 

  • CDM.EXE installed on the server: [Note: this comes standard with any version of Windows Server]
  • Microsoft Team Foundation Server Power Tools:  Fortunately, there is a free add-on to TFS that  you can find here.

 

Take note where TFPT.exe is placed on the server when the add-on is installed.  You’ll need TFPT.exe in the Working directory property below.

 

The Key Components:

So what exactly is TFPT.exe? Well, TFPT.exe is a simple program used to call the TFS API.  It will be used to submit the WIQ query mentioned above, and request back any data in an XML file. You may also wonder what the heck a ‘WIQ query’ is. Technically, a .WIQ is a Work Item Query file: an XML format query file with elements defining the TFS server, and with a very SQL like query on the API.

 

Steps:

  1. In the SSIS package, create an execute process task.
  2. Within the execute process task, update the following 3 properties from initial creation:  (Note:  Use of [ ] brackets is to frame text you could change for your solution.  Do not include the [ or ] characters).
    • Working Directory: Path where the TFPT.exe file is installed. With a standard installation it is installed in one place, so this should be your working directory: C:\Program Files (x86)\Microsoft Team Foundation Server 2012
    • Command Line Executable: This should be the path on the server for CMD.exe on the server.  Presumably c:\windows\system32\cmd.exe
    • Arguments: Send the command line script to CMD.exe.  When using CMD.EXE, you need to start with /C.
      • Then the command line calls TFPT.
      • Then you instruct TFPT, first that you are querying, then with the various switches, defining the output format, where to find the API, the path and name of the WIQ file, and the destination path and name for the output file.

 When the above steps are finished, it should look like the following:

“  /C TFPT query /format:xml /collection:[TFS API path here] /WIQFile:[WIQ file path and name]  > [destination file path and name].  “

In contrast, if I used a saved query in TFS, it would’ve been formatted like this: [Note: The entire path may be adjusted as needed]:

“ /C TFPT query /format:xml “[team name]\Shared Queries\[Query Name]” /collection:[TFS API path here] > [destination file path and name] “   

Here’s what the Properties window should look like:

TFS data in SSIS

 

So there you have it – a simple, easy to adjust solution to get the data needed to finish up the package and import data! If you found this solution helpful, let us know. I’d love feedback from other developers who tried it out!

 

By: Jason Torla | Consultant, Solutions and Development