Steven Wright Blog

A Business Intelligence project ALM's

2011-10-17T17:18:00.001+01:00

Table of Contents

1		Introduction.
2		Automation process components
	2.1	The Build.
	2.2	The Deployment script.
	2.3	Automated deployment and execution.
3		Visual studio tips.
	3.1	Reducing unresolve reference errors.
	3.2	Composite Projects.
4		Resources.

1 Introduction

As I have mentioned in previous posts I have been working on a data warehouse project. One of my main roles in this project was to work on the build, deployment and development processes. Within this post, I would like to show how I, with help from my colleagues, was able to automate a build, deployment and execute the data warehouse code.

The reason for developing this automation was to try and find problems before we released to user acceptance testing (UAT). Once this automation started running it did capture errors. One error in particular, which would only appeared in release, was missing permissions. As the developers and the testing team all have different elevated rights, so that they can do their jobs, masked the problem ever existed.

2 Automation process components

2.1 The Build

To develop our data warehouse solution the project used the Microsoft development tools: Team Foundation Server (TFS) 2010 and Visual Studio (VS) 2010 / 2008. TFS 2010 was used as a source control repository and automated build platform (Team build). VS 2010 was used to develop our database code and VS 2008 - Business Intelligence Studio (BIDS) for creating the integration packages and analysis services cubes.

As we were using the above tools I was able to create, using my company's IP, a build that would drop deployable artefacts. This was then wrapped into a TFS build which would be triggered by a number of check-ins. This gave us our continuous build for the project which was helpful to find problems like: incompatible code between team members; incomplete check-ins missing files from the change set.

However, if a build was successful it didn't mean that the build was deployable. An example of this is an unresolved reference warning which shouldn’t have been a warning but an error. This happened to us, one of our builds which we tried, had several unresolved reference warnings, we tried to deploy but failed because a view referenced to a column within a table was invalid.

2.2 The Deployment script

We used a PowerShell script to manage the process of deploying, in the correct order, our databases, SSIS packages and analysis cubes. The script, which my colleagues had created, used a configuration file to list the artefacts for deploying with how and where. Also the script interaction used a text menu interface, which was created from the following location: http://mspowershell.blogspot.com/2009/02/cli-menu-in-powershell.html

This was helpful in reducing the work that we had to do for writing a release process. As we only had to write was: select menu option blah, select menu option blah etc. The script help reduce deployment errors from incorrect commands typed in or artefacts deployed in the wrong order.

2.3 Automated deployment and execution

To have the automated build, deployment and execution process I reuse the build and deployment components. The build was refactored and reused within a scheduled trigger for out of office hours. The deployment script was extended to include support being called by automation tool. The automation tool was called TFSDeployer. TFSDeployer is a service which can be configured to listen for certain build events and then run a powershell script when captured. For more details about the application, please use the following link: http://tfsdeployer.codeplex.com/.

The script, which TFSDeployer would execute, when the out of office build completed, was configured to do the following steps:

Get the TFS build details.
Copy the contents of the build drop location to a deployment area.
Deploy the data warehouse solution using the deployment script
If the deployment failed then update the TFS build to failed and the status to broken
If the deployment was successful then execute a SQL Server job; which was configured to run the ETL.
If the SQL Server job reported failure then the TFS build was set as failed and the status to broken
If the SQL job reported success then the TFS build was set as successful and the status set for ready for testing

This then gave us the ability to test our builds and as mentioned before allowed us to find many problems before we went into UAT. This also gave us a platform to perform and automate regression testing.

3 Visual studio tips

3.1 Reducing unresolve reference warnings

Here are some tips on how we managed to reduce some of our unresolved reference warnings.

Converting some of derived tables to use common table expression with column definition syntax: WITH <CTEName>(Col1, Col2, Col3) AS
Adding a server and database variables for references which were going to go to be referred to via a link server. Within the code also wrapped the reference variables within square brackets.
Include objects which were created by code for example: select into tables and indexes. With indexes we also took another approach, where possible, which was to disable and enable them.

3.2 Composite Projects

During one of our early development cycles we came across a circular reference problem. This caused a problem at deployment time. I had to deploy the two databases about three times each to get a complete deployment.

The method I used to reduce a circular reference to happen was to use composite projects. The way I chose to split databases into composite projects was to put the storage objects like tables into one project. Then created another project for the code objects like views and referenced the storage project. The idea for this was based on some information I read from: http://vsdatabaseguide.codeplex.com/

4 Resources

Power Shell Menu functions: http://mspowershell.blogspot.com/2009/02/cli-menu-in-powershell.html
TFSDeployer: http://tfsdeployer.codeplex.com/
Team Foundation Server Update script:
Run SQL Server Job:

Integrating a rules engine with integration services

2011-10-17T15:29:00.001+01:00

Introduction

As I have mentioned in several previous posts, I have been working on a data warehouse project. One of my main roles within this project was to work with our clients JRules / Java consultants to help integrate IBM JRules, a rules engine, with Microsoft SQL Server 2008 R2: Integration Services (SSIS).
The goal of the integration was to have the rules engine as part of the ETL process to help evaluate business rules. The reasons to use a rules engine within the ETL process were as follows:

To allow business people to write the business rules for the data warehouse in a language they know: English
To give the business people the responsibility for maintaining the rules to ensure that the data warehouse is up to date.
To be able to update the rules without having to deploy the entire ETL solution.

IBM JRules was used within the project because it was already in use within their organisation. The challenge was how to cross the divide between Java and Microsoft .Net.

Options to consider for integrating with the rules engine

Below is a list of options which I and some of my other colleagues thought about on how to integrate JRules and SSIS

How the rules engine will access the data.
- Receiving the data: Should the data be supplied with the request to execute the rule engine.
- Reading the data: Is the rules engine responsible for reading the data when requested to run.
Which processing method
- Batch: Process a batch of data within the rules engine.
- Row by Row: Make separate requests to the rules engine row by row.
The communication / execution method
- Using Web Services method, Restful Services method.
- Directly coding against the rules engine API libraries. For our integration we would have to have used some sort of interoperability classes like: JNBridge, JIntegra.
How to apply the results of the rule engine. Does the rule engine apply the results directly or are they returned to the ETL for processing.
Error handling
- Rules engine errors: how to record the errors from the rule engine.
- Integration errors: how to handle errors caused by a failure of the integration.

Our approach

Our approach to the integration was to use a batch process model with a SOAP Web method for triggering the rule processing. The rule processing would be responsible for reading, processing and recording the results of the rules execution. The SSIS packages would prepare the batch of data; trigger the rule processing and then process the results. Below is a diagram to illustrate the integration approach:

Web service method
The web service method, which was written by our clients JRules/Java consultants, was defined with the following parameters: the batch identifier; the name of the collection of rules for evaluation.
This method would then start running the rules processing procedures which are as follows:

start reading the data
execute the rules over each complete source row once fetched
store the results back to the database

After the completion of the rule processing the web service method would give a response indicating the success of the rules execution. The web service method was optimise to use multiple threads and to stream read the data. Also the way that the rules are implemented within JRules also helps to optimise the rule processing.

Data Integration
The way that we passed data between SSIS and JRules was to use a couple of tables, which use a name value pair structure. One table, Inbound, was used to store the prepared data for rule processing. The other table, Outbound, to store the results from rule processing which the SSIS would process.
The reasons to use a generic structure are as follows:

If the rules parameters changed then only the ETL process would need to be updated
The rule integration was easier to code
To have the integration only use two tables to help debugging

SSIS Integration
SSIS packages would be responsible for creating the batch data. Afterwards trigger the rules engine processing method and wait for the response. Once the response was received the results would be applied or/and log any errors.
The method I use to call the Web method was to write a .Net control flow component. The main reason I chose to write a component was to have easier code reuse. Also maintenance was easier as I only had to update one component and all packages would pick up the new component.
The approach I took with my custom component was to separate the SSIS integration and the actual call to the web service. Creating a code library that handled the interaction with the web service. This was then wrapped with another code library that handled the SSIS integration.
Taking this approach also allowed me to write a console application to test the interaction with the rules engine using the same calling code as the packages would use. This console application allowed us to debug any integration problems that we came across.

Integration results

Currently we are able to process a batch of 100,000 source rows within a five minute period. This is adequate for a normal day runs where we would only have to process changes for a day. However when there is a requirement to re-run the rules against entire warehouse data the process can take a little while. This is because a number of batches are required to complete the rules processing. There is an improvement currently being investigated, by another team member, to reduce the amount of data sent to the rule engine for processing. The method which is currently being looked at is as follows: Find the unique rows to send to the rules engine for processing and then apply the results back to the required rows.

Comparing Master Data Services instances

2011-09-21T13:02:00.001+01:00

As I have mentioned in previous posts, I've been working on a data warehouse project. Within the project we decided to use SQL Server 2008 R2 Master Data Services (MDS) to store all the warehouse specific reference data. Here are some reasons why MDS was used:

Allowed us to manage the reference data.
Reference data could be loaded into the warehouse like a source system
Would allow the client’s data governance team to easily update the reference data and keep the warehouse up-to-date

For active development of entities and data loads we use a sandpit instance. When the ETL was ready to use the new entities or loaded data, a cut of the sandpit instance would be promoted to the development environment. We came across a problem when we needed to identify some changes which were accidentally made on the development instance.

I came up with a method which helped to identify the changes fairly easily, below is the method I used. I will say that it’s not a perfect solution and might not work for everyone or continue to work when MDS updates have been applied.

Run the following SQL script on both instance of the MDS database servers:

   1:  CREATE DATABASE MDSCompare

2:

   3:  DECLARE @vColList AS VARCHAR(MAX)

   4:  DECLARE @vViewName AS SYSNAME

   5:  DECLARE @vSQL AS VARCHAR(MAX)

   6:  DECLARE @vEntityID AS INT

   7:  DECLARE @vModelID AS INT = (SELECT id FROM mdm.tblModel where Name = '<ModelName,Char,Master Data>')

8:

   9:  DECLARE EntityID_Cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY

  10:  FOR SELECT ID FROM mdm.tblEntity e WHERE model_id= @vModelID order by id

11:

  12:  OPEN EntityID_Cursor

13:

  14:  FETCH NEXT FROM EntityID_Cursor

  15:  INTO @vEntityID

16:

  17:  WHILE @@FETCH_STATUS = 0

  18:  BEGIN

19:

  20:      SELECT @vViewName = REPLACE(e.Name,' ','')

  21:           , @vColList  = COALESCE(@vColList + ', [' + a.name + ISNULL('_' + la.name,'') +']', '[' + a.name + ISNULL('_' + la.name,'') +']')

  22:      FROM mdm.tblAttribute a

  23:          INNER JOIN mdm.tblEntity e

  24:              ON e.id = a.entity_id

  25:          LEFT OUTER JOIN mdm.tblAttribute la

  26:              ON a.domainEntity_Id = la.entity_id

  27:              AND la.attributeType_id = 1

  28:              AND la.IsSystem = 1

  29:      WHERE a.entity_id = @vEntityID

  30:      AND a.attributeType_id <> 3

31:

  32:      SET @vSQL = 'SELECT ' + @vColList + ' INTO MDSCompare.dbo.' + @vViewName + ' FROM mdm.' + @vViewName

33:

  34:      EXEC (@vSQL)

35:

  36:      FETCH NEXT FROM EntityID_Cursor

  37:      INTO @vEntityID

38:

  39:      SELECT @vColList = null

  40:          , @vViewName = null

  41:          , @vSQL = null

42:

  43:  END

44:

  45:  CLOSE EntityID_Cursor

  46:  DEALLOCATE EntityID_Cursor

Then to test that all the entity which are required have been created use the following script:

   1:  DECLARE @vModelID AS INT = (SELECT id FROM mdm.tblModel where Name = '<ModelName,Char,Master Data>')

2:

   3:  ;WITH MDSEntity(Entityname)

   4:  AS

   5:  (

   6:      select REPLACE(e.Name,' ','')  Entityname

   7:      from mdm.tblEntity e WHERE model_id= @vModelID

   8:  )

   9:  , CompareTables (TableName)

  10:  AS

  11:  (

  12:      SELECT Table_Name

  13:      FROM MDSCompare.INFORMATION_SCHEMA.TABLES t

  14:      where t.table_schema = 'dbo'

  15:  )

  16:  SELECT *

  17:  FROM MDSEntity e

  18:  LEFT OUTER JOIN CompareTables c

  19:      ON e.Entityname = c.TableName

  20:  WHERE c.TableName IS NULL

Then use the visual studio 2010 schema compare tool against the two instances of the MDSCompare databases to highlight any structural changes which have been made to the entities.

To find data changes use the following script on the MDSCompare databases to create primary keys:

   1:  USE MDSCompare

2:

   3:  SELECT 'ALTER TABLE [' + table_schema + '].[' + table_name + '] WITH NOCHECK ADD CONSTRAINT PK_' + table_name+'_Code PRIMARY KEY CLUSTERED (Code)'

   4:  FROM INFORMATION_SCHEMA.COLUMNS

   5:  WHERE column_name = 'code'

Then use the visual studio 2010 data compare tool to highlight any data differences.

While using the above methods to do the comparisons I found some differences which I had to ignore. The reason we had to ignore them was because of the following scenario:

While loading data through the batch staging process we had to set the default name attribute of the entities to be an empty string. This is because it would not allow nulls and we did not want to use this attribute. However the development instance has the default name attribute set with nulls. I believe this was because the MDS deployment tool had converted the empty element of the deployment package, which was created because of the empty string, to null while uploading.

Choosing the right CDC tool for the job

2011-09-19T13:26:00.001+01:00

Introduction

I have, as mentioned in a previous blog, been working on a data warehouse project using CDC for extracting the source system data. I would like to share some of the experiences and evaluation criteria used for selecting a CDC tool for our project.
The reason for using a specialist tool was as follows:

Couldn't move all the source databases to SQL Server 2008
Needed the capture change data to be sent to another server instance
A consistent management experience for CDC between all our source systems. The two database vendors the tool needed to support was SQL Server and IBM AS400

Timescales

First we underestimated how long it would take to select a CDC tool. We plan for 1 month, for both evaluation criteria and running the tests, and it took us about 2/3 months. The time was taken in evaluating the tools against the criteria and gaining access to database systems from other projects.
However our development of the ETL solution was able to continue, while the CDC tool hadn’t been selected, as we used generic columns for the CDC metadata. Our requirements allowed this approach because only a selected set of metadata was needed from the CDC tool which most tools offered. The method to ensure that the changes were picked up in the right order was to use a row ID, which was a big integer identity seed, within the CDC destination table. This was able to work as most CDC tool records the changes in the same order as they are made.

Evaluation criteria

Here are some categories of criteria, which our client used, to select which CDC tool to evaluate, these are common sense categories:

Impact: How CDC would impact both source and target systems servers and databases. The Server impact was measured in how much resources it would take to use on the server: memory, CPU, etc.

The database impact was whether it made any schema changes and if it had performance implications for the application. This was important to know as there was a third-party tool where the support contract would have been invalidated if any schema changes were made to their database. To tests for schema changes I use a simple procedure which was to use VSDBCMD tool to import the schema into a schema file before installing CDC. After the install import again to another file and use VS2010 schema compare tool to compare against the schema files.

Schema changes: Does the CDC tool cope with schema changes being made on the source system. Could the tool continue to work if the schema changes were made onto columns which weren't being captured or if any new columns were added.
Speed: How quickly were changes committed to the target server. The metrics for this was by the volume of changes and speed to commit them. Also how quickly the initial synchronisation took to complete.
Management: What was the management tool, how easy was the management tool to use, how quickly CDC was recoverable from errors or disasters.

While evaluating CDC tool we also found that we had to consider some other factors. One consideration was how it impacted the operational procedures of the source systems, for example backups, recovery and deployments.
Another factor was the number of databases and tables which we wanted to capture data from. We had 20 source databases, which had about 15 tables each. Depending on the CDC tool more time would have to be spent on the development and deploying the CDC solution.

To develop or to configure that is the question? (sorry couldn't resist the Shakespeare pun)
That is the question that we found ourselves asking. As some tools required development of code to create a CDC solution. Also with the tools which required development the experience and environment had to be considered as part of the evaluation. Such as the level of privileges required on the development computer; is there any integration with source control or how to protect from loss of work; how easy is it to transfer development from one developer to another.
Another aspect of the CDC tools evaluation was how easy it was to deploy from our development environment to production. How easy was the CDC solution to deploy and was there any automation through scripting or exporting.

Observations

While evaluating we found the same tool gave a different level of change data capture experience depending on the database vendor it was configured against. For example: when configured against the AS400 the tool was able to give a full row of data. But when configured against and SQL server it was only able to give the columns which had changed. The reason for this was how the tool had implemented CDC for the SQL server. There was no requirement for the replication components for SQL server to be installed, without this component the SQL server log file only records the columns which had changed. Hence why the tool was only able to give only the change columns data and not the full row of data.
We also found that the same CDC tool behaved differently when running against different processing editions (x86 and x64). The difference was with settings and memory requirements.

Useful links

Attunity - http://www.attunity.com/index.aspx - which have arrange of CDC products
DBMoto - http://www.hitsw.com/products_services/dbmoto/dbmoto_dsheet.html
IBM InfoSphere Change Data Capture - http://www-01.ibm.com/software/data/infosphere/change-data-capture
Oracle golden gate - http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Source system data to warehouse via CDC.

2011-09-19T13:14:00.001+01:00

Introduction

I have been working on a data warehouse project with a difference. The difference is that the ETL is not doing the classic extract of source system data, instead the source system is going to send its data to the data warehouse by using change data capture (CDC). The decision for using CDC was as follows:

To have the ETL only process the data that has changed within the source system between each run of the ETL. This would help the ETL perform as it would only have to process the change data and not work out what the changes were first.
Not to have the ETL processes impact the central source system database. The CDC would be responsible for delivering the changes of the source system to another database server. Then the ETL could be run at any time within the day and wouldn't be responsible for blocking the transactional source system. As there was plans to have the ETL run at the end of the day for two time zones e.g. US and UK.

I would like to share some of the experiences, decisions and challenges that we face while trying to build a data warehouse using CDC. The first decision, which I will put into another post, was which CDC software to use. As a consequence of using CDC we faced a challenge on how to extract data from source systems where CDC couldn't be applied, One such system was Master Data Services (MDS) this was due to how the data is stored within its database. As this would mean the transforms for this source system would have to use a different approach to where CDC was used. What we decided to do was mocked CDC through use of SSIS and then stored the delta changes that we found through our dataflow process. The reason for choosing this approach was so that all the transforms had a consistent approach.

Development

Early into development we discovered that we had to define what was meant by processing intraday changes. Did this mean every row which was captured by CDC within that Day? In other words, that every change which was captured should create a history change within the data warehouse leaving the last change as current. Or did this mean capture events changes within the day? For example scanned the changes for that day and find the flags for the records that are required. For us this meant capture event changes. Importance of understanding the meaning of intraday changes had an impact on how we approached coding the transform procedures.

Another challenge that we faced was how to deal with transforms which require data from two tables. The reason why this was a challenge is because of the following scenario: An entity within the warehouse has a business rule which requires data from 2 tables from the source system before it can be evaluated. However within the source system data has been changed and captured for one table only. For example: business rule order status requires data from the OrderHeader table and the OrderLine table. As illustrated below.

The source system updates the OrderLine table and a CDC captures the changes and sends them to the ETL. Then the ETL process runs with only the OrderLine changes tries to evaluate the order status business rule but how to evaluate this when OrderHeader data has not been supplied? As illustrated below.

There are many ways this can be resolved, here are some examples:

Change the application process to create updates to the required linked tables. This might not solve the problem as the CDC software may not have committed all of the changes before the ETL process starts running, unless the CDC is part of the ETL. Also this may cause performance problems for the application team.
To design the data warehouse scheme and business rules so that the transforms don't require joins between tables to create a data warehouse. This I will highlight may not be practical as it could mean it basically becomes a copy of source systems schemas which would give you no value for reporting and analysing.
To get transform to read the missing data from the data warehouse if CDC hasn’t passed on the data. This was a good option to consider however there are few considerations to be aware of:
- Could have performance issues with the transform for some of the following reason: Using left outer joins on warehouse tables and depending on the warehouse schema could require a large number of tables; have use of function calls to IsNull or Coalesce; The size of the tables data warehouse could make query tuning very difficult.
- Adding complexity to untranslate business rules or transforms if source system data isn’t being natively stored
- Harder to read and maintain as the transform isn’t just transforming source data
- The data warehouse could become even wide or larger if the source systems native data is stored within the warehouse to help the transform. This will have an impact for querying and processing performance; also add overhead cost for storage.

We solve the problem by extending one of our client's requirements. The requirement was: keep a copy of the source system data changes for each day for a further 30 days. To meet this we configured the CDC to send all the changes to an archive database. Then have the ETL process to extract the day changes from the archive database. Rather than have CDC drop the changes to a landing database and then have the ETL copy the data again to the archive database as well as transform the data. The extension of the requirement was to keep all changes of the latest version of each primary key within the archive database. This then allowed us to write a post-extract process to extract any missing data required for any of the transforms where data wasn't present. This then allowed us to write the transforms in a manner that always assumed that all data was in the same location.

Testing

We found that we had to change the way that we prepared to test our ETL process by using datasets. Normally we would create some datasets which would mirror the source system in a particular state for testing certain scenarios for transforms and business rules. However this time we only needed to create change records for testing but the problem was how to ensure that the change records were fed in a controlled manner to ensure consistent mirrored states of source system and valid transitions were maintained. We resolved this problem by investing time to create a test harness solution. Which mimic our CDC tool of choice by inserting the test data into the archive database and then running the ETL process. One thing I would recommend is that you trial your CDC tool over your source systems to see what data changes will be actually captured. This will help to create valid test data for testing transforms and business rules within your ETL process

Configuring Excel Services & PowerPivot on multi server Topology

2010-04-09T08:16:00.001+01:00

I have been working with a couple of colleagues, James Dawson and Russell Seymour , on installing and configuring PowerPivot within a SharePoint 2010 beta 2 farm.

We used the following instructions to install PowerPivot on one of the application tier server: http://msdn.microsoft.com/en-us/library/ee210616(SQL.105).aspx.

After following the instructions we were able to verify the installation as we had a SharePoint site with PowerPivot gallery. We managed to uploaded and open a couple of Excel workbooks, with PowerPivot embedded, within the web browser. Also we were able to connect to the PowerPivot service and could see the PowerPivot data files loaded. When I tested the slicers I got greeted with the following error:

We did some research and found the following blog post: http://powerpivotgeek.com/2009/12/11/excel-services-delegation/. I implemented the changes it recommended to our Excel workbooks and tried again this time getting the following error:

However at the time of this error we had already configured the Excel Services with the Secure Store Service application identity that we had created.

The problem proved to be with the configuration of all the service applications. At the start all the services applications had their own application pools with there own domain users.

The solution in the end was to run all the services applications under the following app pool configuration: SharePoint Web Services System.

Using Powershell to understand a replication topology

2010-03-09T18:44:00.000Z

I recently had to investigated a client replication topology. I needed to understand the publications, subscribers and the articles. The problems I was facing were as follows:

The replication topology was only defined on the production infrastructure.
There was no documentation about the replication topology.
I had no way of being able re-create replication topology.
Only short amount of time.

I was able to script the replication topology from the SQL Server Management Studio. This was partially helpful as I now had a script which could be used to review the replication topology. However this end up being a fairly large file and would be time consuming to review all information.

So I deicide to write a PowerShell, SQL Server Powershell for the Invoke-Sqlcmd commandlet, that would do the following steps:

Parse the file to find the lines containing the following store procedures:
- sp_addarticle.
- sp_addsubscription.
As theses contain most useful parameters to help understand the replication topology
Pares the line to find the key parameters and their values.
Then insert the information into tables within a database.

I chose to use PowerShell because this was a perfect fit for a scripting language: manipulating a text file and doing regular expression on strings.

This help me see that articles were being repeated across publication, some of the publication had identical articles but had different subscriber.

A copy of powershell script is available here.

How To: Emailing the Sprint Burndown report

2010-01-22T21:33:00.000Z

The Scrum for Team System process template includes reports to help manage the progress of project. The onus is on the team to run - by opening them - and review them. This is the default behaviour of the Team Foundation Server which our process template built against, however as the Team Foundation Server platform uses SQL Server Reporting Services for its reporting software, the reports can actually be pushed out to the teams via e-mail.

I have mentioned how to do this for version 2.x, and for version 1.x the same steps can be applied, with the following post: How to E-mail the Sprint Burndown Chart to yourself or your team.

For version 3.0 (For which beta 2 is now available for download from the following link: http://scrumforteamsystem.com/cs/forums/4554/ShowPost.aspx ) things are a little different.

Firstly, if not already configured, the Reporting Services server will need to will need to be set up to point to an SMTP server. This can be done by using the Reporting Services configuration tool and updating the e-mail settings within this tool. You can find this tool on your TFS report server under the following: Start Menu > Programs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager

To create the subscription you need to use the Report Manager (to gain access to this you can right mouse click on the report folder within the team explorer):

Once the report manager has loaded at the root folder, for your team project report folder, with a list of reports and folders. Then click on the Sprint Burndown report to view the report. After the report has rendered select the subscriptions tab, when the page has loaded, click on New Subscriptions.

Then complete the necessary details on how and when the report should be delivered. If your team project has only one work stream then all the report parameters can bet set to use the report defaults. Otherwise set the value of the release and workstream parameter that you require and the remaining parameters can be left set to use the report defaults:

When there is only one work stream the report can work out the release and work stream and then current sprint. When there is multiple work streams by default the report will get the current release but then default to the first work stream and work out the current sprint for the work stream. To get the sprint burndown for the other work streams repeat the steps to create a new subscription and select the required release and work stream. After the release has finished the subscription for the multiple work streams, it will need to be updated to the next release and work streams.

My Adventures in Codeplex

2009-12-09T21:26:00.000Z

In October 2009 I created 2 codeplex projects which I would to tell you about:

SQL Server Reporting Services MSBuild Tasks (ssrsmsbuildtasks)

A few years ago I created some tasks for MSBuild to help deploy reports for my project, details can be found here, since then my tasks have been re-used in a few other projects within my company. However lately we have been doing a few projects which have made use of Reporting Services integrated mode with SharePoint, which meant that my tasks were unusable as they only work with a native mode report server.

So I have been updating them to include support for an integrated mode report server. Also before I added the integrated mode support I took some time to rewrite my original native mode tasks as well. The re-writing of the tasks includes some improved understanding of MSBuild by making more use of Item Groups and metadata for setting of report server properties of the report items being deployed.

I placed the tasks within codeplex as I needed a better way of sharing my tasks other than through the blog post. Also using the task is helpful in getting the your reporting project integrated with any continuous build of your project. Another way they can help is with automating unit testing of your reporting project which I have talked about how to do from a previous post.

The SQL Server Reporting Services MSBuild Tasks are available from the following URL: http://ssrsmsbuildtasks.codeplex.com/

Server Reporting Services Slide Shower (ssrsslideshower)

While working on our Scrum for Team System process templates for Team Foundation Server (http://www.scrumforteamsystem.com) I created a useful tool. The tool helps to show reports that are important to a scrum team in a sort slide show presentation, more details can be found through this previous post.

While writing the tool most of the mechanics of the tool doesn’t depend on Team Foundation Server or our Scrum template. Also I thought it would be a useful tool for people who wish to demo any reports that are within their reporting services infrastructure. So I have removed the dependences of Team Foundation Server and our Scrum Template.

I have plans to improve the tool, within my limits of coding knowledge, to allow reports parameters values or labels to be set as it renders the reports within the slide show. I also plan to get it working with a integrated report server as well. However my ssrsmsbuildtasks project has taken most of my time but over the next few week I plan to start making my changes to this project.

I placed the project on codeplex because within our Scrum for Team System V3 template for Team Foundation Server 2010, which beta 2 is now available from this link, we have dropped the inclusion of this tool and plan to release it within codeplex. This is mainly because it was a value add tool and not needed as part of the process template. Also the changes that were made to template and the reports, which now don’t have clear defaults for our report parameters which fit into the my improvements plans that I mention above.

The SQL Server Reporting Services Slide Shower is available from the following URL: http://ssrsslideshower.codeplex.com/

SfTS V3 Beta 2:Helping to see the wood for the trees

2009-12-07T21:19:00.000Z

Within any application lifetime management methodology, the reports are a useful tool to help see how well , or not , the project is progressing. Within the Scrum methodology the reports which are used for tracking progress are the burndown reports. In the Scrum for Team Systems process templates for Team Foundation Server these reports are available to help with managing the project.

With a manual run burndown report when an unexpected trend in the burndown happens, there is an option to manually add an annotation. This would help during a retrospective when talking about why the trend oddity occurred. Within our previous versions of the Scrum for Team System templates our burndown reports would only show the end of each days aggregated totals. Any unexpected totals would have to investigated to understand the underlying cause. Previously this would have involved either using Team Foundation Explorer to review the history and comments; Using Excel Pivot Services to query the data or writing a report against the warehouse. This can take some time to create and analyse the result set.

In connection with the above for the last few months I, with the help of fellow work colleague Kate Begley, have been working on writing the reports for the Scrum for Team System V3 process template (For which Beta 2 has been released and details are available from here) part of which has been adding drill-though report functionality to the reports. This helps with the investigation in explaining for example why there was an unexpected up trend in our sprint burndown seen in our report below:

To get drill-though details for the end of day click on any data point for example within the blue circle in the image above, the drill though details report will be displayed as seen below:

In this example the details report shows the work items broken down by the incomplete tasks which contribute to the sprint burndown line aggregated daily totals, complete tasks which have been completed up to that day, and the tasks that have been descoped within the selected sprint / team. The incomplete tasks are further grouped together by their states showing the subtotals for each state, as well as the grand total of incomplete tasks, which are used to make up the burndown trend.

To investigate the unexpected up trend in our sprint burndown I would need to compare the totals with the totals of the previous day. To do that I would export to excel the details report of the day concerned and the previous days details and compare these side by side to analyse the up trend, as shown below:

The above shows the reason for the up trend in this example was due to new work being added to the sprint. Please keep in mind this might not always be the case as other outcomes could of happened:

New tasks could have been added and the incomplete task could have had the work remaining total for it adjusted upward because of unexpected issues
Both the currently incomplete tasks could have had the work remaining adjusted upward because of impediments

Other reports in beta 2 which have the drill-through functionality are:

QA / Bug History Chart
Release / Product Burndown Chart by Day
Release / Product Cumulative Flow
Sprint / Sprint Burndown Chart
Sprint / Sprint Cumulative Flow

Unit Testing Report within Reporting Services: My Theory

2009-08-27T22:13:00.000+01:00

I have been working with Microsoft SQL Server Reporting Services, since its first release, within many of my projects. One of the biggest tasks, that I find with writing a report is the testing the reports and making sure the data that is displayed is correct. My method of developing and testing reports is as follows:

Write the queries, outside Reporting Services, for creating the report data set(s) with the parameters defined which will be configured within the report.
- Define the parameters that report data set would create and assign a valid value.
- Execute the queries.
- Inspect the results to make sure that they meet the expected results.
- Repeat with a new valid value or fix any issues.
After one or two or even three passes that have worked then I take the queries and put them into report data sets.
Then I define the report layout.
Then I preview the report make sure it’s still correct
Then I release to our testers for testing.

With this amount of manual testing there could be a potential for mistakes to creep past for several reasons: The test data often isn’t diverse enough to test all the logic of the queries; The report data has a lot of rows which aren’t all inspected; Run out of time as the testing was under estimated so amount of testing is reduced.

I did, at the beginning of last year (2008), look into away of being able to test the reports by an automated tool. I was looking for a way to compare the information that the report was displaying and compare it to some expected results. The need to test the report itself and not just the underlying queries were for the following reasons:

General Reasons:

The Report could be doing more data manipulation outside the SQL queries that have been unit tested.
- Embedded reporting calculations
- Embedded VB.Net code
- External class libraries code

(all of the above could have been unit tested separately but still could produce incorrect information if wired-up incorrectly)

Test visibility setting for sections of the reports.
Test any embedded report calculations
The Report could be using MDX and there isn’t an easy unit testing frame work for MDX

My Project Reasons:

All the reports used embedded SQL and MDX.
Writing reports against a 3rd party data warehouse and processes
There is no database project in which to use a unit test frame work for SQL.

Searching the internet I came across this article: Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server, which gives a guide on how to stress test reports. This is helpful to ensure that the reporting solution/architecture can handle the demands that are placed upon it. I also thought that this method of testing can be used for 2 other forms of testing:

Simple unit testing: Ensure that report can render. Placing this within a build process, that adds data into the reported database, then runs the web test to check if the report renders can help highlight if there have been changes to the database that breaks the build. This can happen if the reports are based off some application database for simple reporting and the database is using an ORM tool. (Which at least one of our projects is doing).

Build verification testing: Does the report render? Running a web test after the deployment has happened would highlight any error in the deployment of the solution or deployment of the infrastructure.

I helped to get this method of testing implemented for the above mentioned reason for an application project, where it did help to capture and highlight builds that broke the reports. However as this didn’t meet the requirements fully I was looking as this method, which uses web tests, of testing as it was only inspecting the http responses.

It wasn’t until recently that I starting looking again at unit testing reports again because of my current project as testing the reports is one of the hardest jobs for the following reason: Developing reports based upon a platform so have no control of the flow of the data. So I looked again at the beginning of this year (2009) and again didn’t found much there that would help me. So I went to the drawing board to design my own approach and remember that in June 2008 I used Reporting Services in interesting way; I wrote a report which was only create to generate an dynamic XML document for a web page to consume and bind the data to another control (more information can be found here: Could HTTP Soap / T-SQL endpoint be replaced by Reporting Services XML Reports? ). So I took a further look into the XML render options, to see how the reports that uses chart would render into XML, which my current project heavily utilises, and found that all the values of the data points within the charts were exposed. This meant that I could test the reports in the way that I intended.

My next challenge was to design the process that would render the report into XML and then perform some sort of test. The test was easy, if the report was going to produce an XML document then I should be able determine the how the report will render the XML based upon the following: Expected results based upon the known set of data within data store that report is querying; Passing known values for the reports and knowing the XML setting for the reports; So I decided to do a comparison on the report XML against a prepared XML document. The next problem was how to do the comparison as there wasn’t a tool that I could find. So I asked a few of my more .NET savvy guys as well with no joy.

Then I remembered that SQL Server Integration Services (SSIS) has an XML task that does a XML Compare that compares the element’s attributes and more importantly the values. So I set about creating a SSIS package to do the following:

Loop though a control file that contains the location of the reports to test; required parameters, values and location of the expect XML output.
Download the reports into XML format from the Report Server.
Then use the XML task to compare the results from the report server and compared against the expect results.

Based on some initial testing of this approach work for testing my reports the results were good. However I haven’t been able to fully implement my approach for the following reasons:

Need to work on creating the test data for project.
Need to work on getting the test data through the 3rd party process.
Need to work out how to deal with time as many of my reports show time differently.
Need to work out how make it part of the build/test process.

Also recently I have found that my approach is similar to a tool that is available to buy from Innosphere.

SfTS V3: The Beta 1 reports new comers

2009-08-03T21:01:00.003+01:00

As mentioned in my previous blog post, there are some new reports available in the Scrum for Team System V3 Beta 1 Template. I would like to give a high level overview of the new reports as there will be full report guidance available with the template explaining in more detail how to use the reports.
The new Diagnostic report isn’t really a new report, more of a spin off from another report. Version 2.x included a Version report that provided details about the status of the Team Foundation Server warehouse process. This covered information like:

When the process last ran
When the process finished loading data into the warehouse
How often the warehouse process would run
When the last time the cube was changed

Team Foundation Server 2010 provides more information about the Team Foundation Server warehouse process, including:

Is the warehouse blocked from running
Is the OLAP processing blocked from running
The last full process started and ended time
The last incremental process started and ended time

As there was more information available we split the report out so it was less cluttered and easier to read. The Diagnostic report is really useful for investigating reporting issues such as reports showing out of date data.
The Sprint Team Aggregate report, which is new to version 3, is like a classic Sprint burndown, meaning that it shows the burndown of the Sprint Backlog Task, however the report will separately plot each selected team within the selected Sprint. There is also the option to show an aggregated total of the selected teams. This will give a comparison between how well the teams are working.

The final, and saving the best till last, new report is the “Sprint Burndown – Tasks and Stories” report which provides a comparative view of story versus task work burndown at a Sprint or team level.

This is based upon some of the experiences from our agile coaches and other Scrum teams, about what the Sprint burndown should be focusing on: getting Product Backlog “Done”.. So this report shows how many story points have been burnt down as well as the traditional view of the Task burndown.

One of the issues that this report can show is how healthy the task burn down actually is within the sprint. The team(s) could be completing tasks but they are not helping to towards the completion of stories which can highlight quality issues and too much work in progress. Another use for this report is to highlight if the Product Backlog items have been correctly sized or Sprint Backlog Tasks have broken down correctly according to the size of the associated Product Backlog Item.

This report is also our first report that is making use of the Reporting Services 2008 enhanced charting features.

SfTS V3 Operation: Reports face lift.

2009-07-29T21:38:00.005+01:00

For the last few months I, with the help of fellow work colleague Kate Begley, have been working on the writing the reports for the Scrum for Team System V3 process template, which Beta 1 has been released and details are available from http://blogs.conchango.com/sfts/archive/2009/07/28/scrum-for-team-system-v3-beta-programme.aspx.

There are a number of changes, from a reporting aspect, that have been introduced into Team Foundation Server 2010 platform and the Scrum for Team System V3 process template like: Warehouse schema changes; only supporting Reporting Services 2008; a sophisticated multi-team support model and new QA model. As a result the reports have been completely rewritten so there is a limited number in this beta release.
However I would like to highlight some of the smaller updates that been applied to the reports to help readability / usability of the reports which are as follows:

A reporting site folder structure has been created to make browsing the reports easier

Reporting tool tips on the charts to display the unit, value, date that point on the chart is showing.

A Today strip line, with tool tip showing where Today is.

Small descriptions have been embedded into the reports so when viewing the report in Report Manager or Team Foundation Server Web access their descriptions can be seen to give a snapshot of their function.

A report footer has been added with the same embedded description and link to a report guidance (which will be coming soon).

The Portal reports can be resized within the SharePoint portal by changing the height and width report parameters to help with customising the SharePoint portal.

The reports that are still available from previous versions are as follows:

Version – Diagnostic
Bug History Chart – QA
Product Burndown Chart By Day – Release
Product Cumulative Flow – Release
Sprint Burndown Chart – Sprint
Sprint Cumulative Flow – Sprint

Alongside these additional new reports for version 3, which I will cover in another blog:

Warehouse Status - Diagnostic
Sprint Burndown - Tasks and Stories Chart – Sprint
Sprint Team Aggregate – Sprint

Reporting Services 2008 Report automatic upgrade gotcha.

2009-03-30T23:26:00.000+01:00

For the Scrum for Team System project I have been focusing on the reports; I came across an issue which was due to the way the Reporting Services 2008 "automatic upgrade" feature upgraded our reports.

The Scrum for Team System template contains over 20 reports all written for Reporting Services 2005. This was done because Team Foundation Server 2008 only worked on SQL Server 2005 and Reporting Services 2005. Then in August of 2008 Team Foundation Server 2008 Services Pack 1 was released which added support for the Team Foundation Server 2008 is to run on SQL Server 2008 platform.

With Reporting Services 2008 there is a new reporting definition language schema. However reporting services does have backward compatibility method as describe below:

The report server will validate report definition file against the included reference to the RDL namespace which specifies the version of the report definition schema that is used.

The report is automatically upgraded the first time it is viewed, but the stored report definition file remains unchanged.

So if you edit the report from the server it will still remain in old schema and not the new schema.

To get automatically upgraded report definition you will open the report in one of the authoring tools: Business Intelligence Development Studio (BIDS) or Microsoft Report Builder 2.0.

So I did some testing to see how the 2005 reports would look after the reporting services 2008 processing. I found that 2 reports didn’t render the same as did on reporting services 2005. The 2 reports had a common layout they were using a reporting services sleazy hack: The Green-Bar Matrix. The cell which controls the background colour was a lot more visible than the original as well as the text. So I rewrote the reports and were released them with version 2.2.

Then one of our template users reported that one of the other reports stopped working. The report started to give the following error:

The processing of Parent for the tablix ‘table1’ cannot be performed. The comparison failed. Please check the data type returned by the Parent.

I was able to trace the error to an option in one of the report define groups: Recursive parent, which can found under advanced; removing the option made the report worked again. I haven’t been able trace why the error happens it only seems when our data came back in a certain way which I am currently looking into.

Some practical SQL Spatial tips.

2009-03-30T23:18:00.000+01:00

I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt:

The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint().
Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid.
I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table.
The STPointN() function is 1 base which is stated in the Books On-Line
If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on the base table.
I had to create a view which needed the latitude and longitude of the starting and end points of the geom line also including the distance. The view would take about 20 seconds to finish executing. Once I created some persisted compute columns on the base table the view would finish executing under 3 seconds
You can create more than one spatial index for a spatial column.
Trying to join tables using the spatial functions isn’t great for performance.

Working With Reporting Services Multiple Value Parameters

2009-03-01T22:10:00.002Z

A couple of colleagues of mine were working on a complicated reporting services project; they had one report which had a parameter which allowed a user to select multiple values of numbers, the problem they were facing was how pass the selected values into the query.

They were looking for a way within SQL Server to split a string by a delimiter. As they were using a stored procedure to access the data and multiple value parameters, regardless of parameter type, are passed in as a string of comma separated values.

One thing which they weren’t aware of was that with a multiple value parameter, the behaviour can differ between how the data is being retrieved i.e.: stored procedure or embed sql statement.
If the data set is using direct query to gather the data then the multi value parameter can be used in the query with a IN clause like so:

Where column1 in (@pMultiValueParam)

What happens is that before the query is sent to the SQL Server the query the report server substitutes the @pMulitValueParam with a comma separated list which makes it valid in clause like so:

Where column1 in (10, 3, 4, 56)

Also it has been blogged (http://www.socha.com/blogs/john/2009/03/tfs-report-issues-with-sql-server-2008.html) that in reporting services 2008 there is a new change in the behaviour. You could see the following message when you have no values to select:

Incorrect syntax near ')'.

When there are no values to pass, Reporting Services 2008 simply removes @pMultiValueParam, so you get something like "Where column1 in ()" preventing this query from running.

The solution is to add an expression to the pMultiValueParam report parameter. The expression is evaluated in order to determine what is passed to the query.

Like so:

=IIF(Parameters! pMultiValueParam.Count > 0, Parameters!pMultiValueParam.Value, "")

How to load spatial data into SQL Server 2008 from .Net

2009-01-23T21:58:00.003Z

I have been working on a project which made use of the spatial data type geography within SQL Server 2008.

An issue that I had was how to load the geography data from a KML file into SQL Server 2008. Currently there is no out of the box tools to do this. There is a 3^rd party tool, Safe FME, which offer either their own tool or components which extend integration services. This was overkill for my issues as I only had to do it once.

So I wrote a console application which parses the KML file to extract the point data convert it to a SqlGeography type and store it in the database.

To use the SqlGeography C# type you need to add reference to the following name space: Microsoft.SqlServer.Types. This can be found in the following dll: Microsoft.SqlServer.Types.dll
Then use the following code to create the c# sqlgeography type:

   1:  // use SqlGeographyBuilder to help create the SqlGeography type

   2:  SqlGeographyBuilder geographyBuilder = new SqlGeographyBuilder();

   3:  string[] longLat;

   4:  SqlGeography geography;

5:

   6:  // gets the co-ordinates

   7:  XElement coOrdinates = element.Element(ns + "Point").Element(ns + "coordinates");

8:

   9:  // set the Spatial Reference Identifiers that will used to create the point

  10:  geographyBuilder.SetSrid(4326);

11:

  12:  // state what type of geography object that I to create

  13:  geographyBuilder.BeginGeography(OpenGisGeographyType.Point);

14:

  15:  longLat = coOrdinates.Value.Split(new char[1] {','});

16:

  17:  // add the frist figure lat long point

  18:  geographyBuilder.BeginFigure(Convert.ToDouble(longLat[1]), Convert.ToDouble(longLat[0]));

19:

  20:  // close the figure and geography class

  21:  geographyBuilder.EndFigure();

  22:  geographyBuilder.EndGeography();

23:

  24:  // get the geography builder to return the sqlgeography type

  25:  geography = geographyBuilder.ConstructedGeography;

26:

  27:  return geography;

After creating the data type I then needed to write the code send the data to the SQL server which as follows:

   1:  // set the command text

   2:  string sqlCommandText = "insert into [dbo].[Location]([Location],[CoOrdinates]) Values(@pLocation,@pCoOrdinates)";

3:

   4:  // create the command object and set which command type

   5:  SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);

   6:  sqlCommand.CommandType = CommandType.Text;

7:

   8:  // create and add the paramter for standard sql data type

   9:  sqlCommand.Parameters.Add(new SqlParameter("@pLocation", name.Value));

10:

  11:  // create and add the paramter for sql geography data type

  12:  // as I am using and system CLR type have to say what data type name the parameter is

  13:  sqlCommand.Parameters.Add(new SqlParameter("@pCoOrdinates", geography) {UdtTypeName = "Geography"});

14:

  15:  // execute the command

  16:  sqlCommand.ExecuteNonQuery();

Like the report viewer control the Microsoft.SqlServer.Types namespace is not installed with .Net. For the application to work on another computer, without having to install SQL server or their client tools, a Redistributable package would need to be installed.

The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008. This component can be installed separately from the server to allow client applications to use these types outside of the server.

A version can be found here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

Business Intelligence with SQL Server 2008 Geometry Data Type.

2009-01-19T21:42:00.000Z

I am currently working on a project which is automating a business intelligence process base upon images and image recognition. The process is as follows:

Take a photography
Put the photography through the image recognition software and record the recognition data.
Process the recognition data into meaningful business metrics
Produce a report on metrics

The part of the process, from above, that I am helping with is processing the recognition data (the hit data) into meaningful business data. This process simply takes the hit data (which is a point), then creates some square blocks, which I use to group the hits data and perform some metric operations. The metrics currently use data about the area and the density of hits within the area. To help process this data I decided to make use of the SQL Server 2008 spatial type: Geometry.

Firstly I would like to point out that I didn’t have to use the geometry type. As I am currently dealing with square areas, I can group or locate all the hits that fall within area by using the following filter clause: HitY between MinY and MaxY and HitX between MinX and MaxX. However by reading the filter alone can cause misunderstanding on what the filter is suppose to be doing. As I am using the geometry data type that filter is replaced by something which does state what the filter is doing, like so: area.STIntersects(HitPoint) = 1 with the added bonus of less code being written and read as well as less chance of a bug being created.

Another point I would to make is that by using the geometry type my code is adaptable to changes. Currently our process is working to square areas and in the future that might change. Using the standard SQL filters that would mean all the filter clauses of the data would have to be updated. Also I would need to be more sophisticated, which could take a longer development and testing cycle which could increase the risk of issues. With the use of the geometry type, only the way that the area polygon is created would need to be updated which will help in reducing the development and testing cycle which interns reduces the risk of issues.

Another benefit of having some of the data stored as the geometry spatial data type is that I was able to evaluate or debug the steps of the process far easier. This is due to the way that SQL Server Management Studio display spatial data as shown below.

Scrum for Team System Version 2.2 support for SQL Server 2008

2008-10-16T22:05:00.003+01:00

As I have mention in a previous blog entry I’ve been heavily involved in the Scrum for Team System process template since the start of version 2 and have written the majority of the reports. I have faced, and still do, some challenges while doing this. Some of them have been around how to display the data within reporting services 2005.

Now with the release of Team Foundation Server 2008 Service Pack 1, release in August, this enables Team Foundation Server 2008 to be used on a SQL Server 2008 environment. This will make some of reporting challenges I faced with reporting services 2005 disappear. This will due to major changes that been made to reporting services 2008 like Tablix control and wider Dundas chart controls.

Within this release we have made a patch which will update some of the reports for reporting services 2008. This to fix some of the reporting services 2005 Patterns and Recipes which became visible. I am currently working looking at how Scrum for Team System can make more use of the reporting services 2008 which we will release as an advance reporting pack in the future.

To find out more about the Scrum for Team System SQL Server 2008 patcher please read the following Scrum for Team System blog

New Reporting Features available in Scrum for Team System v2.2

Build Reports added to Scrum for Team System v2.2

The Scrum for Team System report slide show tool

Scrum for Team System Version 2.2 support for SQL Server 2008

The Scrum for Team System report slide show tool

2008-10-14T22:08:00.002+01:00

As I have mention in another blog posts; I’ve been heavily involved in the Scrum for Team System process template since the start of version 2 and to coincide with the release of version 2.2; I would like bring your attention to a new tool, which I help to developed, that has been added to this release.

I started creating this tool due to some feedback we got from our beta users for “TaskBoard for Team system” tool. The request was to add a demonstration mode into the application which would show things like swimming lane-view and the following reports: Sprint burndown chart; Sprint cumulative flow as these can be view within the application.

The reason for the request was create the sense of: "Look guys, this is what we are working on, and this is how we are doing", throughout the day, not only during the scrum meetings. This also happen a lot within our projects which they do something similar as well. Another reason for the tool to separate from the Task Board application was to avoid polluting what the Task Board application was suppose to do.

So a new page was added to ScrumforTeamSystem virtual folder called: ReportSlideShow. This will allow a selection of team system project(s) reports to be display by their parameters default values. However the reports don’t have to be scrum for team system only reports there is one caveat: all reports have to exist in all requested display projects.

The report that are which are display is control by configuration file. To see how to figure which reports are display please use the Report Slide User Guide Post from the Scrum for Team System site

New Reporting Features available in Scrum for Team System v2.2

Build Reports added to Scrum for Team System v2.2

The Scrum for Team System report slide show tool

Scrum for Team System Version 2.2 support for SQL Server 2008

Build Reports added to Scrum for Team System v2.2

2008-10-14T21:17:00.002+01:00

Continuing from my previous posted. I’d like to give an overview to the new engineering practices reports included in the Release 2.2.

Builds

Across a user selected time period (defaulting to the current sprint), this report shows the total builds per day broken down by status.

Build Static Analysis and Compile

This displays the number of Errors and Warnings either compile or static analysis the last N number of builds.

Build Unit Tests

Shows the total number of units test run from the last N number of builds. The total is broken down by number status of unit tests for each build.

Last Build Unit Test Results

This report displays the list of each test that was run for the last build and shows the result of that build. If the unit test failed it will also display the fail message as well. The last build is taken to be the last build within the data warehouse.

Code Coverage

This report shows the code coverage of and the total of code churn for last N number of builds. The coverage units can be selected between blocks, which is the visual studio default, lines or partial lines.

Build Code Churn

This report shows the total “Code Churn Counts” of the last N number of builds broken down by Added, Modified and Deleted lines. This report also features a drill down option which links to the Build Files reports.

Build Files

This report, which is also a drill down for the Build Code Churn report, will display the files which were in the build or between two builds. The files are grouped by the folder in which they are located within source control and display the number of Added, Modified and Deleted lines for each of the change sets. Also included is the following change set information: Check-in by, Date, Policy Override Comment.

Build Quality

This report is a based on the MSF for Agile Software Development Quality Indicators. This has been added by popular request and inspired by a blog post by Ed Blankenship: (http://blogs.infragistics.com/blogs/eblankenship/archive/2007/06/18/msf-agile-quality-indicators-report-for-conchango-scrum-process-template.aspx).

Build Duration

This shows how long the last N builds took to complete.

Build League

This will show a League table of the Top N number of people on their total number builds of Fail or Successful Builds (using either an absolute number or percentage base). This report will run for a selected period defaulting to the currently running sprint. A successful build is defined as any build which not in a state of failed. (Which is not the same as a successful build.)

There is one caveat: The way in which a build is counted against a person is if their change set is linked to a build. So some people will gain some builds fails even if their change set is actually not the reason why the build failed.

All the reports will have the option to select which build type to run against. Also where the reports allow an N number of last builds they have been limited to the following values 10,15,20,25 or 30.

These reports are not actually link to the Scrum methodology but are helpful tools in the engineering practices. There will be a separate forum for issues and question which can be found here: Engineering Practices – v2.2 Reports

New Reporting Features available in Scrum for Team System v2.2

Build Reports added to Scrum for Team System v2.2

The Scrum for Team System report slide show tool

Scrum for Team System Version 2.2 support for SQL Server 2008

New Reporting Features available in Scrum for Team System v2.2

2008-10-14T20:31:00.001+01:00

To coincide with the release of version 2.2 of Scrum for Team System, I’d like to bring your attention to the wide range of new reports included in this release. I’ve been heavily involved in the Scrum for Team System process template since the start of version 2 and have written the majority of the new reports and tweaked some of the old ones to improve performance by moving some of the querying to the TFS cube, as well as reflecting user feedback.

One such fix, which can be found in release, was to the burn down report which would show a misleading (but accurate) trend line if the task data wasn’t updated on the very first day of the sprint. (This could also be fixed by making your Scrum Master work harder, but I don’t have any control over that so I decided to tweak the report instead).

Most of the new reports in this release are focussed on engineering practices, which I will cover another blog post; however there is one new report specifically targeted for Scrum called the velocity report.
Velocity is based upon the recorded historical performance of a team, and is a fantastic tool for accurate release planning; for more details please see my colleague Simon Bennett’s blog for more details.

So onto the new reports....

New Reporting Features available in Scrum for Team System v2.2

Build Reports added to Scrum for Team System v2.2

The Scrum for Team System report slide show tool

Scrum for Team System Version 2.2 support for SQL Server 2008

Using NDepend to help guide Refactoring

2008-09-30T22:30:00.008+01:00

In my other blogs entries I mention that I have been looking into building a Team Foundation Server Data Warehouse Adapter. After I got my initial proof of concept version working, I started to extend it to get a list of available builds from TFS and then import the output of Source Monitor – a popular free code metrics tool.

Now I must state that I am not a natural .NET coder. My normal day-to-day work is based around the SQL Server technology stack. I do have some .NET coding skills but generally just enough for what is need in and around SQL Server. So I asked my colleague, Howard van Rooijen who is one of our top .NET coders, to have a look at my code and give me some hints.

This provide me with a great opportunity to learn from Howard about some more advance Design Patterns, Visual Studio tips and tricks, and how to effectively use some tools I’ve not used before to help me write better code. The first thing Howard did was install the following on my machine:

ReSharper – to help me refactor the code, fortunately we have just rolled out ReSharper licenses to all .NET Developers!
StyleCop – to help remind me of the Microsoft C# Coding Conventions
TDD.NET – to ease the pain of running my unit tests
NDepend – a code metrics tool

Howard wanted to integrate NDepend into my TFS Data Warehouse Adapter and suggested that the best way to understand the tool would be to use it to analyse my TFS Adapter code and see if it would be able to give the direction on where to focus our refactoring efforts.

NDepend comes with 82 code metrics out of the box, but it’s far more than a standard code metrics tool. It comes with a bundle of best practice design rules which are written using Code Query Language – an idea so simple and elegant that I bet the FxCop team are kicking themselves for not thinking of it first. CQL allows you to write statements like:

// Methods poorly commented

WARN IF Count > 0 IN SELECT METHODS WHERE PercentageComment < 20 AND NbLinesOfCode > 15 ORDER BY PercentageComment ASC

You can even embed CQL directly into your code via attributes. NDepend also has features like being able to compare different builds (Patrick Smacchia, NDepend’s developer demonstrated this feature on the recent .NET Framework 3.5 SP1 release), manage complexity, dependencies and monitor the health of your build process.

We started by running my code through NDepend and the initial results were that my code has high instability and not at all abstracted. I couldn’t really affect the instability as my code was just a thin wrapper around the TFS API. It also highlight that some of my old best practices that needed updating as many of them are now redundant with C# 2.0 features (generics, static classes etc). It also highlighted performance gains I could make.

Howard said that he was having some difficulties understanding what the intention of my code was, as he was unfamiliar with building Data Warehouse Adapters. He asked me if I could write some unit tests that would call the code and verify the output – so he could use the “Test with... Debugger” feature of TDD.NET to see what was going on with the code.

Once I had written some tests, Howard wrote two more: one that passed in a null value and another that passed in an empty string; these, he explained were two valid edge cases for handling bad data and to my surprise both tests failed! Once I fixed both failing tests, he asked me to run the whole test suite again and this time I was really surprised as all of my previously passing tests now failed! The issue was with two helper methods that convert string based representations of number within the Data Warehouse into valid number primitives.

NDepend pointed out lots of issues with these methods and Howard said that a simple Regular Expression could replace a lot of the parsing logic I had written, so I set about refactoring the code.

Before:

   1:  public static string CleanInt(string number)

   2:  {

3:

   4:      string result = string.Empty;

   5:      bool foundNonDigit = false;

   6:      int index = 0;

7:

   8:      if (number == string.Empty)

   9:      {

  10:          return "0";

  11:      }

12:

  13:      while (!foundNonDigit)

  14:      {

  15:          if (char.IsDigit(number[index]))

  16:          {

  17:              result = string.Format("{0}{1}", result, number[index]);

  18:              index++;

  19:          }

  20:          else

  21:          {

  22:              foundNonDigit = result.Length > 0;

  23:          }

  24:      }

  25:      return result;

  26:  }

27:

  28:  public static string CleanFloat(string number)

  29:  {

  30:      string result = string.Empty;

  31:      bool foundNonDigit = false;

  32:      bool foundDecmialPoint = false;

  33:      int index = 0;

34:

  35:      if (number == string.Empty)

  36:      {

  37:          return "0";

  38:      }

  39:      while (!foundNonDigit)

  40:      {

  41:          if (char.IsDigit(number[index]) || (number[index] == '.' && !foundDecmialPoint))

  42:          {

  43:              result = string.Format("{0}{1}", result, number[index]);

  44:              foundDecmialPoint = number[index] == '.';

  45:              index++;

  46:          }

  47:          else

  48:          {

  49:              foundNonDigit = result.Length > 0;

  50:          }

  51:      }

  52:      return result;

  53:  }

54:

After:

   1:  public static string CleanInt(string number)

   2:  {

   3:      int resultFromTryParse;

   4:      // check for empty string after cleaning string

   5:      if (number.Trim() == string.Empty)

   6:      {

   7:          return "0";

   8:      }

   9:      // if try parse works return the number in string format

  10:      if (int.TryParse(number, out resultFromTryParse))

  11:      {

  12:          return resultFromTryParse.ToString();

  13:      }

14:

  15:      // else return results of clean GetCleanNumberString

  16:      return GetCleanNumberString(number, "Integers");

  17:  }

18:

  19:  public static string CleanFloat(string number)

  20:  {

  21:      float resultFromTryParse;

  22:      // clean the number and check to see if it's empty

  23:      number = number.Trim();

  24:      if (number == string.Empty)

  25:      {

  26:          return "0";

  27:      }

  28:      // try parse the number if works the return the number

  29:      if (float.TryParse(number, out resultFromTryParse))

  30:      {

  31:          return number;

  32:      }

  33:      // returns result of Get clean number

  34:      return GetCleanNumberString(number, "Floats");

  35:  }

36:

  37:  private static string GetCleanNumberString(string number, string numberType)

  38:  {

  39:      // Create a regualr patten with name groups do a macth on the number entered

  40:      Regex regex = new Regex(@"(?<Floats>(?<Integers>\d+)+\.\d+)|(?<Integers>\d+)", RegexOptions.Singleline);

  41:      Match match = regex.Match(number);

  42:      string results = string.Empty;

43:

  44:      // if match groups are found the return the string

  45:      if (match.Groups != null)

  46:      {

  47:          results = match.Groups[numberType].Value;

  48:      }

  49:      return results;

  50:  }

51:

Now all my tests passed and NDepend was happy too.

Next NDepend highlighted some of my methods that had too many lines of code. Howard said he was having a bit of difficulty understanding what the code was doing and explained to me the notion of Programming by Intention.

Next he paired with me and went through the method reformatting the code. He said that by reformatting, reorganising and removing redundant variables you start to see patterns forming within the code, that clumps of logic naturally come together and how you can then use ReSharper’s Extract Method refactoring to extract that clump of code into a separate method. Now instead of having a mass of hard to understand code – the method simply listed a series of steps that occurred – the intention of the method had been made clear. Howard was happy because he now understood what the method was doing and NDepend was happy as the method was now shorter and a great deal less complex.

In my original proof of concept I had used partial classes as a mechanism to extend the main TFS Data Warehouse Adapter and implemented another partial class in order to integrate Source Monitor. Howard pointed out that this approach wouldn’t work if we wanted to integrate a second tool.

He said that now we had cleaned up the code and broken down the few large methods into a series of smaller methods we had surfaced the behaviour required to integrate an external code metrics tool into the Data Warehouse Adapter and that we could use ReSharper’s Extract Interface refactoring to move the definitions of this behaviour into an interface called ICodeMetricAdapter and then use this interface to implement any other 3^rd party code metrics tool.

Howard said that we had now essentially implemented the adapter pattern. Next we refactored the existing code to use the interface instead of the concrete type and we extracted the Source Monitor code into a new file called SourceMonitorAdapter which implemented the ICodeMetricAdapter interface. We added a List<ICodeMetricAdapter> to the parent object and then refactored the code to loop through this list and then call the current ICodeMetricAdapter.

We managed to get this all done in one afternoon, just as I was packing up Howard asked me “Now, what do you need to do to add an NDepend Adapter?” I created a new class called NDependAdapter and made it implement ICodeMetricAdapter. Howard showed me that I could use the Implement Members quick fix by selecting the interface and hitting ALT-ENTER. Then all I had to do was add a new instance of the NDependAdapter to the List<ICodeMetricAdapter> and the new NDependAdapter is automatically called!

Below is the before and after class diagrams of the solution:

Before:

After:

How I built a Team Foundation Server custom data warehouse adapter

2008-07-11T21:53:00.001+01:00

As mentioned in a previous blog I would like to explain some of the steps I took to get my custom data warehouse adapter to work. I am going to start from the beginning just so that I have an easy starting point. The code I show here was based upon my POC and written in C#.

1. Create a new C# Class Library project in Visual Studio. Then add references at least to the following assemblies: Microsoft.TeamFoundation.dll, Microsoft.TeamFoundation.Client.dll, Microsoft.TeamFoundation.Common.dll, Microsoft.TeamFoundation.Warehouse.dll and System.Web. As I need to connect to the build server I added Microsoft.TeamFoundation.Build.Common.dll and Microsoft.TeamFoundation.Build.Client.dll
2. Then specify the use of the IWarehouseAdapter interface.
3. Implement IWarehouseAdapter.RequestStop which only sets a stop flag to true which can then be tested by the other methods periodically.
4. Implement the IWarehouseAdapter.Initialize. This method this where I need to store objects that will need to communicate to the all systems that you adapter needs to talk to i.e.:

   1: string url;

   2:  TeamFoundationServer tfs;

   3:  _DataStore = ds;

4:

   5:  if (_DataStore == null)

   6:  {

   7:     throw new Exception("Null data store.");

   8:  }

9:

  10:  url = Microsoft.TeamFoundation.Server.TeamFoundationApplication.TfsNameUrl;

  11:  tfs = TeamFoundationServerFactory.GetServer(url);

12:

  13:  if (tfs == null)

  14:  {

  15:     throw new Exception("TF Server instance not obtained for TFS url: " + url);

  16:  }

17:

  18:  _BuildServer = (IBuildServer)tfs.GetService(typeof(IBuildServer));

19:

  20:  if (_BuildServer == null)

  21:  {

  22:     throw new Exception("Build Server instance not obtained for TFS url: " + url);

  23:  }

24:

  25:  _CommonStructureService = (ICommonStructureService)tfs.GetService(typeof(ICommonStructureService));

26:

  27:  if (_CommonStructureService == null)

  28:  {

  29:     throw new Exception("Common Structure Service instance not obtained for TFS url: " + url);

  30:  }

5. Implement the IWarehouseAdapter.MakeSchemaChanges. This method is called to see if there are schema changes that would need to be made to the data warehouse. This is recommended to be where you actually register your changes you need to make to the warehouse i.e.:

   1:  SchemaChangesResult result = SchemaChangesResult.NoChanges;

2:

   3:  //Get the current data warehouse configuration

   4:  WarehouseConfig warehouseConfig = _DataStore.GetWarehouseConfig();

5:

   6:  //As the IWarehouseAdapter.MakeSchemaChanges can be called many times in one sessions

   7:  //I needed to see if your fact was already created

   8:  Fact MyNewFact = warehouseConfig.GetFact("MyNewFact");

   9:  if (MyNewFact == null)

  10:  {

  11:      //Create My New Fact

  12:      MyNewFact = new Fact();

  13:      MyNewFact.Name = "MyNewFact";

14:

  15:        //Adding a link to the Team Project – I had to do this otherwise my save would not work

  16:      MyNewFact.DimensionUses.Add(CreateDimensionUse("Team Project", "Team Project"));

17:

  18:      //Adding a link to the Build dimension also when use the dimension I needed to

  19:        //find out the dimension key attribute was for when saving my data.

  20:      MyNewFact.DimensionUses.Add(CreateDimensionUse("Build", "Build"));

  21:      MyNewFact.Fields.Add(CreateField("measure1","int",0,"Sum"));

  22:      MyNewFact.Fields.Add(CreateField("measure2", "int",  0, "Sum"));

23:

  24:      //Setting which perspective it would show under if enterprise edition of AS was in use.

  25:      MyNewFact.PerspectiveName = "Code Churn";

  26:      MyNewFact.IncludeCountMeasure = true;

27:

  28:      if (!_StopRequest)

  29:        {

  30:             //Starting a Transaction to rollback my changes if they fail

  31:             _DataStore.BeginTransaction();

  32:            try

  33:            {

  34:                   //Adding to the data warehouse configuration xml file and saving the changes

  35:                   warehouseConfig.Facts.Add(MyNewFact);

  36:                   _DataStore.Add(warehouseConfig);

  37:                   _DataStore.CommitTransaction();

  38:                   result = SchemaChangesResult.ChangesComplete;

  39:             }

  40:             catch

  41:             {

  42:                   _DataStore.RollbackTransaction();

  43:                   throw;

  44:             }

  45:      }

  46:      else

  47:      {

  48:          result = SchemaChangesResult.StopRequested;

  49:      }

50:

  51:  }

  52:  return result;

  53:  //The functions to create a new field and dimension link

54:

  55:  private Field CreateField(string FieldName, string FieldType, short FieldLength, string FieldAggregationFunction)

  56:  {

57:

  58:      Field newField = new Field();

  59:      newField.Name = FieldName;

  60:      newField.Type = FieldType;

  61:      newField.Length = FieldLength;

  62:      newField.AggregationFunction = FieldAggregationFunction;

  63:      return newField;

64:

  65:  }

66:

  67:  private DimensionUse CreateDimensionUse(string DimensionName, string UseName)

  68:  {

69:

  70:      DimensionUse dimensionUse = new DimensionUse();

  71:      dimensionUse.DimensionName = DimensionName;

  72:      dimensionUse.UseName = UseName;

  73:      return dimensionUse;

  74:  }

6. IWarehouseAdapter.MakeDataChanges which is where all the transferring and transforming of the data takes place.

   1:  IEnumerator projectEnum = _CommonStructureService.ListAllProjects().GetEnumerator();

   2:  DataChangesResult result = DataChangesResult.NoChanges;

   3:  IEnumerator buildEnum;

   4:  IBuildDetail[] buildDetails;

   5:  ProjectInfo currentTeamProject;

   6:  DateTime buildStartSearch = LastMyNewFactPrcoessedDateTime;

   7:  DateTime buildEndSearch = DateTime.Now;

8:

   9:  while (projectEnum.MoveNext())

  10:  {

  11:      //checking to see if a stop request has happend if so then return the fact that a stop happen.

12:

  13:      if (_StopRequest

  14:      {

  15:          return DataChangesResult.StopRequested;

16:

  17:      }

18:

  19:      //Just checking the state of the project as I am not interested in deleted projects

20:

  21:      currentTeamProject = projectEnum.Current as ProjectInfo;

  22:      if (currentTeamProject.Status != ProjectState.Deleting)

  23:      {

24:

  25:          //Get a list of builds and the filter the out builds that I have already processed.

  26:          buildDetails = _BuildServer.QueryBuilds(currentTeamProject.Name);

  27:          var filterBuildList = from bd in buildDetails

  28:          where bd.StartTime >= buildStartSearch

  29:          && bd.FinishTime < buildEndSearch

  30:          select bd;

31:

  32:          //From my new list of builds save my fact details

  33:          buildEnum = filterBuildList.GetEnumerator();

34:

  35:          while (buildEnum.MoveNext())

  36:          {

37:

  38:              if (_StopRequest)

  39:              {

  40:                  return DataChangesResult.StopRequested;

  41:              }

  42:              SaveMyNewFactEntry (buildEnum.Current as IBuildDetail, currentTeamProject);

43:

  44:              //Update the last build started and date I have just covered.

  45:              LastMyNewFactPrcoessedDateTime = buildEndSearch;

  46:              result = DataChangesResult.ChangesComplete;

  47:          }

48:

  49:      }

50:

  51:  }

52:

53:

  54:  private void SaveMyNewFactEntry (IBuildDetail buildDetail, ProjectInfo currentProject)

  55:  {

  56:      Random rand = new Random();

57:

  58:      //Check to see if this build has been already added to fact table.

  59:      string buildURI = LinkingUtilities.DecodeUri(buildDetail.Uri.AbsoluteUri).ToolSpecificId;

  60:      if (_DataStore.GetFactEntry("Build Details", buildURI) != null)

  61:      {

  62:          FactEntry newFactEntry = _DataStore.CreateFactEntry("MyNewFact ");

  63:          // Had to add Tracking ID by not doing so cause an error.  The Tracking ID is used to find facts

  64:          //So need make sure that method which repeatable for the same fact entry each time.

  65:          newFactEntry.TrackingId = Guid.NewGuid().ToString("D", CultureInfo.InvariantCulture);

  66:          newFactEntry ["Team Project"] = LinkingUtilities.DecodeUri(currentProject.Uri).ToolSpecificId;

  67:          newFactEntry ["Build"] = buildURI;

  68:          newFactEntry ["measure1"] = rand.Next(0,100);

  69:          newFactEntry ["measure2"] = rand.Next(0, 100);

70:

  71:          //Again create a transaction save the entry if successful commit the changes

  72:          _DataStore.BeginTransaction();

  73:          try

  74:          {

  75:              _DataStore.SaveFactEntry(newFact, true);

  76:              _DataStore.CommitTransaction();

  77:          }

  78:          catch

  79:          {

  80:              _DataStore.RollbackTransaction();

  81:              throw;

  82:          }

83:

  84:      }

85:

  86:  }

7. I need to store the last date processed so I created a property which stores the data in the datastore property bag.

   1:  // Last changeset that was populated.

   2:  private DateTime LastMyNewFactPrcoessedDateTime

   3:  {

   4:      get

   5:      {

   6:          String LastMyNewFactPrcoessedDateTimeStr = _DataStore.GetProperty("Last MyNewFact Prcoessed DateTime");

   7:          DateTime lastMyNewFactPrcoessedDateTime = DateTime.MinValue;

   8:          if (!String.IsNullOrEmpty(LastMyNewFactPrcoessedDateTimeStr))

   9:          {

  10:              lastMyNewFactPrcoessedDateTime = DateTime.Parse(LastMyNewFactPrcoessedDateTimeStr);

  11:          }

  12:          return lastMyNewFactPrcoessedDateTime;

13:

  14:      }

15:

  16:      set

  17:      {

  18:          _DataStore.BeginTransaction();

  19:          try

  20:          {

  21:              _DataStore.SetProperty("Last MyNewFact Prcoessed DateTime", value.ToString());

  22:              _DataStore.CommitTransaction();

23:

  24:          }

  25:          catch

  26:          {

  27:              _DataStore.RollbackTransaction();

  28:              throw;

  29:          }

  30:      }

  31:  }

8. Build the adapter as a DLL.
9. Copy the build DLL into the warehouse plugins folder on the application tier. In most cases will be C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\Warehouse\bin\Plugins.
10. Reset IIS.
11. On the application tier, navigate to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run and click Invoke.
12. On the application tier, navigate to http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=GetWarehouseStatus and click Invoke. Continue doing this until the status is returned as Idle. Check the application event log, to see if an exception has been thrown.
13. Reset IIS.

If you wish to find out what your current warehouse configuration is then run the following query against your TFS warehouse:

SELECT CAST(wc.Setting AS XML) AS setting FROM dbo.[_WarehouseConfig] AS wc WHERE wc.ID = 'ConfigXML'

Building a Team Foundation Server custom data warehouse adapter

2008-07-11T21:48:00.000+01:00

I have been investigating a way of adding more metrics about our builds into the Team Foundation Server 2008 warehouse. This would then allow us to measure quality of our code over time by other metrics than the just method offered by the Team Foundation Server 2008.

So my goal was to create a new fact table and link this to build dimension. The reason for building a fact table was the metrics as I would want to do some aggregation against them; this would mean I need to treat them as measures and measures only live in a fact table. I also wanted a separate fact table so my adapter doesn’t impact the standard TFS adapters.
As my facts would be generated by builds it would mean that facts would link to the build which why I used the build dimension. Also I found out later I had to also include the Team Project dimension as well.

I managed to find some useful links that helped me write my adapter:

http://msdn.microsoft.com/en-us/library/bb130342.aspx - This section covers several topics: how to implement an adapter; how the data warehouse is created and gives example on how to create adapter. The example is only how to extend existing dimensions not how to create new objects.
http://tomfury.spaces.live.com/blog/ - This guy was trying to do the same thing as me and created a series of blog entries explaining what he was doing and his issues he was facing and gave some code examples. Also he has posted the following questions on the forums which were helpful:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=2464644&siteid=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1644099&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2518423&SiteID=1

There some has been some changes, as I understand, between 2005 and 2008. As I no longer code against TFS 2005 I can’t make any real comment about these. However I found these links helpful for coding against TFS 2008:

http://ozgrant.com/2006/06/18/how-to-list-tfs-team-projects-using-the-api/ - this to gain information about the projects that are in TFS server.

http://notsosmartbuilder.blogspot.com/2007/09/team-build-2008-api.html - this helped me to query TFS server about the builds it understands. As I can see there has been a lot of changes between the 2005 and 2008. In the 2008 API some namespaces have been made obsolete (Microsoft.TeamFoundation.Build.Proxy; also a few classes in Microsoft.TeamFoundation.Build.Common).

To see how I wrote my custom TFS data warehouse adapter please click here.

Could HTTP Soap / T-SQL endpoint be replaced by Reporting Services XML Reports?

2008-06-10T19:22:00.000+01:00

I have been working on a POC using SQL Server 2008 and Google Maps. The POC was to use Geo Spatial technology to illustrate our Geo Spatial data easily. I will talk about this in more detail in another blog post as I would like talk about a problem I faced while creating this POC.

The problem I faced was how could I get my Geo Spatial data, which was stored within SQL Server 2008 using the new geography data type, into "static" HTML?

So I thought that SQL Server HTTP Endpoints for SQL would be great thing to use to solve this problem. As all I was after was xml document to stream over HTTP. There was no business logic needed so all the .Net would be is just another layer acting as go between. Also I didn’t what to waste time on what code which wouldn’t have added any value.

So I created my stored procedure to produce an XML document that I found usable by using the For XML Path. Then I started to look at how to create a HTTP endpoint for SOAP. After working through this I was presented with this message from my SQL Server 2008: "Creating and altering SOAP endpoints will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it."

It was nice to see that Microsoft are giving people plenty or warning about breaking changes for next version of SQL server. It’s a surprise that a feature is being dropped so quick, second only to Notification Services since I have known the product, but I can understand the decision as a lot of questions on how, why & should it be used were ask by some of our SQL Server developers. So I took notice of Microsoft warning and tried to see if there was another method I could get my data into an XML document and streamed over HTTP without using .Net code and web services.

All I was doing with this data was just reading for display via another component. The data was in a structured format. Then I thought about Reporting Services, as you can write a report and get the reporting server to render to XML. So I quickly wrote a report to get the outputted xml into the format that I wanted. Then in my static html I changed my http request to point to my report server with added option to render to XML.

This approach did work and was great, as I managed to produce a page that could request data XML data and render data without any need to write .Net code. The static html page would always remain up to date. Another bonus was I had the report ready to show the data in a table from without having to re-write or reduplicating SQL code. I was also then able to extend this to accept parameters so I could order the data base upon some user data from the HTML input control.

Now this approach is not always going to work for every situation. One reason is that reporting services security will not allow anonymous access. So when using a windows security the report server would need to be on the same server. A method to overcome this would be to create some forms authentication method on the report server and get your site to create the form cookie automatically.

Another potential problem is way that Reporting Services sends the XML data other HTTP. The XML data is actually sent as an attachment file on the HTTP response. Now some applications, like the side bar gadgets, might not be allowed to, or can’t, handle http attachments.

How to E-mail the Sprint Burndown Chart to yourself or your team.

2008-02-19T18:12:00.000Z

I have been working on Scrum for Team System focusing on the reporting functionality of this tool. One of the things I looked at was to get the reports delivered to me by e-mail. I went looking to see if there was an interface via the Team Explorer interface, to which I found quickly there wasn’t. However because TFS uses Reporting Services I knew that I could use Reporting Services subscriptions functionality.

You can find this tool on your TFS server under the following: Start Menu > All Programs > Microsoft SQL Server 2005 > Configuration Tools

To create the subscription you need to use the Report Manager (to gain access to this you can right mouse click on the report folder within the team explorer):

When the report manager is loaded then the root folder will contains folders based upon the name of Team Projects. Click on the folder that contains the required reports. After the list of reports have loaded click on the Sprint Burndown report to view the report. After the report has rendered select the subscriptions tab, once the page has loaded and then click on new Subscriptions. Then filling the necessary details on how the report should be delivered and what report parameters the report should used when it runs. Finally I would recommend that for the parameters select the option to pick the defaults of the reports.

How to tell when the Team Foundation Server data warehouse was last updated.

2008-02-19T18:02:00.000Z

While I have been working on the Scrum for Team System reporting I came across a re-occurring issue. The issue was not knowing when our data warehouse last updated or when the next update was due to happen.

After some investigation, including some information from a link on one of my colleague’s blog posts, I found the answer to the issue. In the TFS data warehouse there is a table called: _WarehouseConfig. This contains some useful information like when the last data warehouse was last process time, process interval and end time of each step of the process ended.

So with this information I updated the version report for Scrum for Team System to include the following information: process interval time, next time process is due, last time the cube was processed, last time the data in data warehouse was updated.

I have attached a copy of the new report as it not exclusive to Scrum for Team System. Also this report was written for TFS 2008.

SQL Server 2005 Merge Replication not replicationing changes to the subscribers

2007-02-21T17:53:00.000Z

I have been using SQL Server 2005 merge replication within my current project. It is a centralized Merge push topology using a custom RMO application with over 350 subscribers but that is the subject for another post…

While we have been testing there have been some issues raised by our testers and one of these in particular is what I’d like to write about today.

We were seeing data being inserted by the application into the publisher database but this data was not being replicated down the to the targeted subscriber (parameterized filters were being used). Bizarrely, all the changes made at the target subscriber were replicated up to the subscriber.

The data which was not being replicated turned out to be new child data organized by a join filter within the replication publication. The application is a .net smart client which is used at both publisher and subscriber database and uses a SQL Server login account to access the database. After some investigation by me and my colleague, James Rowland-Jones, we found the issue.

Using SQL Server profiler to track all the SQL statements we traced the problem to the insert merge trigger. The trigger was not assigning the child data to the correct partition i.e. no row being inserted into MSMERGE_CURRENT_PARTITION_MAPPINGS. The problem was with the way the trigger checks for associated child records. The trigger uses a generated replication view MSMERGE_REPL_VIEW_<PUBGUID>_<ARTGUID>, which rather than just performing a select on the base table to see if the data qualifies for a partition, it additionally performs a security check using an IsPalUser function. Our application user was not in the PAL and so consequently the view would return 0 results rather than the expected data. This was certainly not what we were expecting! The PAL is for controlling access to the publication not influencing what data is ultimately replicated.

So to get the join filter to work as expected we had to add the application database user to the PAL.

MSBuild using SQL Script parameters

2006-12-20T17:50:00.000Z

I have been working with MSBuild and the Microsoft.Sdc.Tasks to deploy my current project’s databases. Some of the SQL scripts that are executed within MSBuild need parameters to be set to create the database objects, which the Microsoft.Sdc.Tasks.Sql.Execute method supports. To use parameters within MSbuild and SQL.Execute; firstly create an item group (<ItemGroup>) then create element of any name as this will become the name of the item that can be referred to in MSbuild. Within the new element an attribute has to set called “Include”, then create two other elements called “name” and “value”. Here is an example:

<name>@pTableName</name>

<value>’sys.Objects’</value>

</schemaName>

<name>@pTableId</name>

</ schemaTable >

</ItemGroup>

The name element needs to be set to the name of the parameter including the @. The value element needs to be set with the value to which the parameter has to be set to. The only gotach I have found so far is that string data needs to wrapped in the quotes.

Then to the get the MSBuild to pass the parameter into the Sql.Execute Task set a attribute on the task called Parameters and then @(<ItemName>). The Parameters will take in a list of parameters. Here is an example: <Sql.Execute Path="CreateTable.sql" ServerName="$(SQLServer)" DatabaseName="$(Database)" Parameters="@( schemaName);@( schemaTable)" />

To Collate or not to Collate

2006-12-20T17:48:00.000Z

Collation plays an important role within the database, as it sets out how the SQL Server has to manage string data within storage & queries, yet I don’t script out the collation when scripting out my databases. So why don’t I script out the collation if it so important? Its because if the collation is specified but not used properly within the database it can cause some of most common problems, like “Cannot resolve collation conflict for EQUAL TO operation” or “Cannot resolve collation conflict for UNION operation”. These errors are caused when string data within different collations are being compared or combined into the same result set; this is nothing new so why talk about collation now? Well it’s to do with the release of Visual Studio 2005 Team Edition for Database Professionals (VSTDB) something I think needed to be taken in consideration if this is being used to manage the database side of the project.

A cause for theses types of problem is when a table is created on a database with the collation scripted out and deployed on another database which has a different collation to the create script. With the VSTDB this will not occur as much because a project will be assigned a collation and the database will be created with this collation or if the database does not get recreated and has a different collation then the tables will be created with the project collation (This is the desired behavior when I saw this last and was confirmed when I spoke to the VSTDB Team in November). So I can see that VSTDB will help with reducing these types of errors.

Another cause of the error is when creating temp tables without setting the collation to the same as the database collation and the server collation is different. There are 2 ways to resolve this issue. The first is to rebuild your system tables to match the collation of the database that has been created or another way is by using the collation clause within the create temp table script(s) within the T-SQL code. These errors could be become more frequent when using the VSTDB; because as mentioned before the database or tables that are create under the VSTDB are assigned the project collation, which will have to be of a type collation as there is no server default option and this could be different to that of the server that is being deployed to.

By taking the time to put in the extra collation clause in the temp table script(s) it reduce the risk of database code failing with “cannot resolve collation” errors on any SQL server configuration. Also it reduce the temptation of people changing the VSTDB project collation to match there server collation if they can’t change their SQL server default collation and causing different behavior of SQL statements.

I can understand the reason for making the database project ask for a collation because it means that when you unit test the code on any server it will always behave in the same way. Also it means as stated before, the database can run on any server with less risk of collation errors.

Delpoying Reporting Services Reports With MSBuild

2006-12-20T17:43:00.000Z

I am currently responsible for deploying our project into the test and production environments. The project has a few reports which need to be installed on a few report servers. The deployment tool that I am using for the rest of my project is MSBuild so I tried to see if there was a way of getting MSBuild to deploy the reports. The only method that I found was to write an RS script file which deployed the reports and calls the Exec task to run the RS script.

So I started outright with RS script as I was not over joyed about writing VB.Net code as I am a C# coder. Then I remembered that MSBuild is customizable as it allows custom tasks to be written. So I stopped writing my VB.Net code went back C# and created a few tasks that are commonly needed to deploy: AddReportUser, CreateReportFolder, CreateConnectionSoruces, DeployReports and SetReportsDataSource. The task code was easy to write. I did have to change some of the auto generated code. I changed the refence.cs file by changing the ReportingService2005 method to accept a string as it called and removed all references to the setting file and app.config. I also deleted the setting file and app.config that was created by the Visual Studio.

Reporting Services Express Edition using host file redirect to connect to local database.

2006-10-11T18:34:00.000+01:00

I had a few reports for an application that needed to be deployed to number of different report servers. The report servers were Express edition of Reporting Services 2005 with the application database on the same server which helped with the connection limitation of Reporting Services 2005 Express. As the report servers were joined to a domain their computer names have to be unique, the only way application was going to get around this problem was to use host file redirect. To help with deploying the report I was to going to try to use the host idea, so I added the server name to the localhost entry and tried to run the reports. Unfortunately this would not work because it looks like the reporting services 2005 express checks the name of the computer, which it gets from the following path HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\ComputerName\ActiveComputerName, against the datasource within the connection if they don’t match then raises an error.

Report Viewer Customizing Printing for Local Report

2006-10-11T17:23:00.001+01:00

We had a requirement to allow a front page, which was another local report, to be printed before the report within the report viewer was printed. As both reports were local reports it was easy to create a print button which allowed us to meet the requirements, here is the code that we used:

   1:  if (this.printFrontingSheetCheckbox.Checked)

   2:  {

   3:   LocalReport frontsheet = front.reportViewer1.LocalReport;

   4:   Export(frontsheet, 8.27f, 11.69f, 0f, 0f, 0f, 0f);

   5:   m_currentPageIndex = 0;

   6:   Print(false);

7:

   8:   foreach (Stream s in m_streams)

   9:   {

  10:    s.Close();

  11:   }

  12:   front = null;

  13:  }

14:

  15:  LocalReport report = reportViewer.LocalReport;

  16:  Export(report, 11.69f, 8.27f, 0f, 0f, 0f, 0f);

  17:  m_currentPageIndex = 0;

18:

  19:  Print(true); foreach (Stream s in m_streams)

  20:  {

  21:   s.Close();

  22:  }

23:

  24:  private void Print(bool landscape)

  25:  {

  26:   if (m_streams == null || m_streams.Count == 0)

  27:    return;

  28:   PrintDocument printDoc = new PrintDocument();

  29:   PrinterSettings ps = new PrinterSettings();

  30:   ps.PrinterName = printDialog1.PrinterSettings.PrinterName;

  31:   ps.DefaultPageSettings.Landscape = landscape;

  32:      ps.DefaultPageSettings.PrinterSettings.DefaultPageSettings.Landscape = landscape;

  33:   printDoc.PrinterSettings = ps;

34:

  35:   if (!printDoc.PrinterSettings.IsValid)

  36:   {

  37:    string msg = String.Format("Can't find printer \"{0}\".", printDialog1.PrinterSettings.PrinterName);

  38:    MessageBox.Show(msg, "Print Error");

  39:    return;

  40:   }

  41:   printDoc.PrintPage += new PrintPageEventHandler(PrintPage);

  42:   printDoc.Print();

  43:  }

44:

  45:  /// <summary>

  46:  /// Export the given report as an EMF (Enhanced Metafile) file.

  47:  /// </summary>

  48:  private void Export(LocalReport report, float PageWidth, float PageHeight, float MarginTop, float MarginLeft, float MarginRight, float MarginBottom)

  49:  {

  50:   StringBuilder deviceInfosb = new StringBuilder();

  51:   deviceInfosb.Append("<DeviceInfo>");

  52:   deviceInfosb.Append("<OutputFormat>EMF</OutputFormat>");

  53:   deviceInfosb.Append(string.Format("<PageWidth>{0}in</PageWidth>", PageWidth));

  54:   deviceInfosb.Append(string.Format("<PageHeight>{0}in</PageHeight>", PageHeight));

  55:   deviceInfosb.Append(string.Format("<MarginTop>{0}in</MarginTop>", MarginTop));

  56:   deviceInfosb.Append(string.Format("<MarginLeft>{0}in</MarginLeft>", MarginLeft));

  57:   deviceInfosb.Append(string.Format("<MarginRight>{0}in</MarginRight>", MarginRight));

  58:   deviceInfosb.Append(string.Format("<MarginBottom>{0}in</MarginBottom>", MarginBottom));

  59:   deviceInfosb.Append(string.Format("</DeviceInfo>"));

  60:   string deviceInfo = deviceInfosb.ToString();

  61:   Microsoft.Reporting.WinForms.Warning[] warnings;

  62:   m_streams = new List<Stream>();

  63:   report.Render("Image", deviceInfo, CreateStream, out warnings);

  64:   foreach (Stream stream in m_streams)

  65:   {

  66:    stream.Position = 0;

  67:   }

  68:  }

69:

  70:  /// <summary>

  71:  /// Handler for PrintPageEvents

  72:  /// </summary>

  73:  private void PrintPage(object sender, PrintPageEventArgs ev)

  74:  {

  75:   Metafile pageImage = new Metafile(m_streams[m_currentPageIndex]);

  76:   ev.Graphics.DrawImage(pageImage, ev.PageBounds);

  77:   m_currentPageIndex++;

  78:   ev.HasMorePages = (m_currentPageIndex < m_streams.Count);

  79:  }

Then a new requirement came that we needed to get the report viewer print button to be the same as our print button. At first I didn’t think it was possible because the button was within the report viewer control, but after looking through all the properties, methods and events we found the print event, which according to the object help, fires after the user prints a report. This would help because at least we would be able to detect a print event and then print the front page at the end of printing the report. But a collogue played around with the code and found that the event gets fired first before the printing happens and setting the cancel variable to true stops the report viewer doing the printing then by calling our printing code this allowed us to meet all the requirements.

Dynamic SQL Server ordering without using dynamic SQL.

2006-09-14T18:46:00.001+01:00

For my current project I have a stored procedure that needs to sort data by 1 of 3 columns depending upon the option chosen by the user from the application. This is only the dynamic part of the stored procedure, which also happens to be very complicated, so I didn’t wish to use dynamic SQL Server. I know that within SQL server 2000 you can use a case against a variable to select which column to order the data by but the only caveat is that all the columns have to be the same data type. My problem was that not all my columns are the same data type, I managed to over come this by using the new function within SQL server 2005 call Row_Number(), which gives a row number to each row, based upon the order by used within the over function, as this always returns the same data type it can be used within the case statement for the ordering. Below is an example of code:

1:DECLARE @vOrderby AS INT

   2:  SET @vOrderby = 4

3:

   4:  SELECT TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE

   5:  FROM information_schema.columns

   6:  ORDER BY CASE @vOrderby

   7:    WHEN 1 THEN ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME)

   8:    WHEN 2 THEN ROW_NUMBER() OVER(ORDER BY COLUMN_NAME)

   9:    WHEN 3 THEN ROW_NUMBER() OVER(ORDER BY DATA_TYPE)

  10:    WHEN 4 THEN ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION)

  11:    WHEN 5 THEN ROW_NUMBER() OVER(ORDER BY IS_NULLABLE)

  12:    WHEN -1 THEN ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA DESC , TABLE_NAME DESC )

  13:    WHEN -2 THEN ROW_NUMBER() OVER(ORDER BY COLUMN_NAME DESC )

  14:    WHEN -3 THEN ROW_NUMBER() OVER(ORDER BY DATA_TYPE DESC )

  15:    WHEN -4 THEN ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION DESC )

  16:    WHEN -5 THEN ROW_NUMBER() OVER(ORDER BY IS_NULLABLE DESC)

  17:  END

I am not recommending this as a good solution, just a solution that might be useful for a small database.

Reporting Services Report Viewer Control & Page Sizes

2006-08-16T13:14:00.000+01:00

I have found that the reporting services report viewer control might not use the correct paper size to render the report. The reason for this can be found on the following link: click here. Quick summary of the solution is that if you wish the report viewer control to render A4 page size instead of Letter then within the report define the pages size of A4 in inches.

Avery Labels Printing via RDL / Reporting Services

2006-06-30T13:54:00.001+01:00

I have just managed to solve the problem of how to give an application the ability to print Avery labels with data from our database. I only had to support two label formats (L7161 and L7163) and the data requirement was static as well. So I looked at RDL / Reporting Services and the Report Viewer control.

The way I wrote the report is as follows:

Set the page size to A4 and all the margins to 0cm.
Made sure that the report was not using a Header or Footer.
Placed one list control per column of labels on a sheet
Changed the size of the list control to be the same as a single label
Then set an expression on the list visibility based on the result of the rownumber modulus by the number of columns (making sure each column looked for a unique result)
Then added the textboxes for the data making sure there was a gap between the top and bottom of the list control. Also made sure that the text boxes didn’t push the list box size out.

I did have trouble trying to line the labels up 100% also using this approach does have some draw backs like updating will become a hard task and the use of the labels will be locked for one use only.

But now I have a template it should not be long before I will write Avery label component using RDL

SQL Server 2005 Merge Replication Subscriber unexpectedly deleting during data Synchronisation.

2006-04-16T18:58:00.000+01:00

Within my current project I have been using SQL Server 2005 merge replication. While I have been testing some issues have been raised by our testers and I would like to write about another one of these in particular.

Data, which was entered at the subscriber, was being removed while synchronizing with the publisher. This can happen when the data entered isn’t part of the partition for a given subscriber. However, on this occasion we expected the data to remain in place. After some investigation by me and my colleague, James Rowland-Jones, we found the issue.

We noted that the affected tables were organised in parent child relationships at a table / schema level but they were joined child parent in the replication topology. They were joined via a join filter with a partition filter on the child record. So Customers 1 -> N Orders but partitioned On Orders with a join filter back to Customers. The topology was created in this way to help partition the data sent to the individual subscribers i.e. a subscriber would only see customers that it had created orders for.

To troubleshoot the problem we used SQL Server profiler to track all the SQL statements used in a synchronization session. We found that the issue was to due to the synchronization process and how it processed the records sent from the subscriber.

When the subscriber has data entered it doesn’t evaluate or set its current partition mapping. The mappings are worked out dynamically during the synchronization process. Only the data in MsMerge_Contents is sent to the publisher for evaluation. Whilst working this out the process does not take into account the fact that a child record at a data level was a parent with the partitioning key in the topology. During the replication process the articles were processed in foreign key relationship order so the parent data was being added first. A check was then being made to see what partition this data belonged to. As the child data had not yet been evaluated the partitioning data wasn’t available so the topology sent a delete back to the subscriber.

The way to resolve this was to enforce the processing order manually. We had to make a change to the database and replication. The change to the database was to set NOT FOR REPLICATION on all foreign keys. We also changed the topology to enforce processing order within replication. In TSQL you’d do this by setting the @processing_order parameter in sp_addmergearticle. Unfortunately in RMO this could not be set via a property or method. I had to create a T-SQL statement to call sp_changemergearticle to update the processing order after the articles were created.

However, there is a gotcha to watch out for with this approach as well. As soon as one starts setting the processing order manually you need to set it for all objects. This is because any object without an explicit number will be evaluated before those that have. So the rule is: when enforcing a processing order you will need to enforce it for all objects: Tables, Views, Procedures and Functions.

There are two basic approaches to setting up the processing order. The first is to give all objects a unique number. The second is to give all object types a unique number. We went for the latter as we thought it’d be simpler to manage overall. It would also allow us to set big bands or ranges to allow for future changes. However, we had to also make sure we processed the replication joins in a set order. Therefore within the tables we gave another higher number in the range to each level of join. Tables at the top of the replication tree were 10000 the tables they had join filters to were 20000 and so on. When we hit dependencies within objects we simply gave them another range. We hit this with functions that referenced other functions.

Once we’d set the processing order correctly and set NOT FOR REPLICATION on all the foreign keys everything worked and we were able to return the issue as fixed back to the testers - which was nice. I should also point out that we did try just changing the NOT FOR REPLICATION option only but this didn’t work for us. We had to make both changes for this to work.

Installing SQL Server 2005 Express with Remote Connections.

2006-03-23T09:12:00.001Z

I have managed to set-up a SQL Server 2005 Express instance, on a windows-XP machine, with remote connections enable. The actual installation of the SQL Server 2005 express instance was straightforward, but enabling the remote connections was problematic.
I found that I was not the only person. In the end I found these steps worked for me:

Open up the SQL Server Surface Area Configuration Manager

Click on Surface Area configurations for services and connections.
Click the remote Connections entry on the tree, select local and remote connections (TCP/IP) . Click OK.

Open the SQL Server Configuration Manager

Select SQL Server 2005 Network Configuration/Protocols for the instance.
Right click TCP/IP, select properties.
Click the IP Addresses tab, change the Enable flag on the IP addresses to YES.
In the IPAll section, copy the port value of the TCP Dynamic Ports to the TCP Port.
Clear the TCP Dynamic Port value.
Click Okay.

Open up Control Panel/Firewall.

Click Exceptions tab.
Click add port button.
Enter the TCP port number specified in the IPAll/TCP Port section above.

Restart the SQL Server Service instance.
Restart the SQL Server Browser Service.

SSIS Creating Synchronous Transform Component

2006-03-14T15:44:00.001Z

Do you need to write a custom component to transform input data, and disregard this input data, instead, replacing it?
That's was what I needed to accomplish with my component. It takes data from the input buffer and performs a lookup via an external API. If there is a match, send the result data down a "match" output otherwise send the input data down a "unmatch" output. The key here is that the "shape" of the output was changing. In other words, the metadata of the output was very different from that of the input.I also want my component to be synchronous (meaning synchronous, I mean that the component takes a row, processes it and then immediately sends it down an output) and not asynchronous (meaning that it reads all the data and then processes the data in an internal buffer). Now from what I was reading and seeing this was what I thought SSIS meant by synchronous and asynchronous components, but I was wrong!

An asynchronous component can eitherRead all the data in the buffer and cache it. Then after reading all the records process them and then send the data out or
It can read a row of the input; process that data in place without caching it and then send it down the output.

So, I wrote my component to be an asynchronous component but behave like a synchronous component. To do this I create my component outputs setting one to be a synchronous by setting SynchronousInputID of the "unmatch" output to equal the InputId of the input, and for the "matched" output setting InputId=0. The PrimeOutput() method simply needs to assign the output buffer to a private variable. Then, in ProcessInput(), read the row, process it using the external API, and then add the row straight into the output buffer. If you would like to see more, here is the post that I placed on the forum that helped to solve my probelm http://forums.microsoft.com/MSDN/showpost.aspx?postid=295247&siteid=1&message_id=138175&notification_id=138175

SSIS File handle leak in For Each Loop

2006-03-02T16:40:00.001Z

I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time.

Update: I put this on the Microsoft forums and got a intresting answer back See Here;
There seems to be a memory leak with foreach loop which should be fixed in sp1.

SSIS Connection Object and Expressions

2006-02-22T17:48:00.001Z

I have been writing a custom source adapter that uses a file connection within the connection manager. If I hard-code a specific file then the component works. However if I use a file connection that has an expression defined which updates the connection, for example when you have a for-each loop looping over a set files. The file connection doesn’t seem to re-evaluate expression each time you access the file connection via the code.

SSIS Variable Deadlock

2006-02-09T14:46:00.001Z

I have noticed a problem with the script task within SSIS. The problem occurs when a script task encounters an error and the SSIS on-error event also has a script task that uses a variable that is being locked by the script task that raised the error. The problem seems to be that the SSIS on-error event fires and runs before the script task that raised the error has finished and released its lock on the variable. I have even tred to overcome this problem by using a try/catch block to release the variable locks but this also failed to resolve the problem.

SSIS Changes Database Settings

2006-02-09T14:45:00.001Z

I have a SSIS package with a simple data flow task, which takes data from a flat file and loads it to an OLE-DB destination (which is configured to use Native OLE-DB\Native Client). As I ran the package via the command-line I noticed that SSIS made changes to the database options. One of the most important options that were being changed was the database recovery model which was modified to run under Bulk-Logged. This changing of the recovery model could cause problems for the back-up and recovery procedure if you are using incremental transaction log back-up as Bulk-Logged will not allow this to run. I can understand why SSIS makes this change because Bulk-Logged does not write any data to the transaction log for Bulk operations. For this reason it does not allow incremental log back-ups and always recommends that you do a back-up of the database when you change back to Simple or Full mode.

SSIS XML Task / Source Adapter

2006-01-20T17:08:00.001Z

I have been working with SSIS using the XML Task and XML Source Adapter. I was getting an error say that an element is was not defined. When I check the XSD, which was using import / include schemas, I found the element was difined but not in the main XSD schema the adapter was pointing to but another schema which was being imported. The probelm was with the schema were the element was defined not being invaild for the XML Source Adapter, that was the real probelm, as soon as I corrected the imported schema the error was resolved.

The probelm with the imported XSD schema there was some DTD define at the being of the file and XML Source Adapter does not support a mixture of DTD and XSD.

SSIS Execute SQL Task With VARCHAR(MAX)

2006-01-20T15:52:00.001Z

While I was using the SSIS Execute SQL Task to return a single row result I getting the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "RestoreCmd": "The type of the value being assigned to variable "User::RestoreCmd" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object!."
This was happening becuase I was trying to assgin a varchar(max) result column to SSIS string variable.
There are two ways to solve this problem; one is to change the SSIS variable to object and convert each time it used to a string; the second way to solve this is to cast result column to max size limit.

SSIS 2005 and Database Snapshots

2006-01-20T13:31:00.003Z

I have been looking into the feasibility of using database snapshots as method of rolling back an SSIS package that fails and this is what I found:
Creating a database snapshot was not a real problem, there is one issue that you need to be aware of. For each data file within the database that the snapshot is being based on there will have to be a sparse file created for it. I have created a little SQL script that automates the creation of the database snapshot.

Rolling back a database to its initial state after a package has errorred is a problem. The method to revert a database back to its initial state with database snapshots is a restore operation. The restore is a full restore, which of course means there can't be any connections while the restore is happening and this can't be done on-line because it might have to take out the primary data file. This is a problem because SSIS connection manager will create connections to the database when the package starts and doesn't release the connections until the package has completed its run.

A SQL script that automates the creation of the database snapshot.

   1:  DECLARE @vDBName AS VARCHAR(255) DECLARE @vFiles AS VARCHAR(MAX)DECLARE @vSQLCmd AS VARCHAR(MAX) SELECT @vDBName = DB_NAME(), @vFiles = ''

   2:  SELECT @vFiles = @vFiles + '(Name = ' + Name +', filename='''+ LEFT(physical_name,LEN(physical_name) - CHARINDEX('.',REVERSE(physical_name))) + '_PrePublish.ss''), 'FROM sys.database_filesWHERE TYPE = 0

   3:  SET @vSQLCmd = 'CREATE DATABASE ' + @vDBName +'_PrePublish ON ' + LEFT(@vFiles,len(@vFiles)-2) + ' AS SNAPSHOT OF ' + @vDBName

   4:  EXEC(@vSQLCmd);

Reporting Services Report Viewer using SQL Authentication

2005-02-11T13:24:00.001Z

I have written some code, which will create a report and deploys it to a report server. However the connection string for the report has to use SQL Authentication. My code which builds the reports uses the same approach that Microsoft does to SQL Authentication within report, which is to not store the UserID and Password, so what would happen now when the report is displayed in the report viewer is that a prompt would appear asking for a username and password. This behavior had to be suppressed, because I did not wish the users to know or need to type in a userid or a password, so I tried to resolved this by looking for a way to get the connection string stored on the server when deploying the report, like you can when you use SQL Server Business Intelligence Development Studio, with no joy. I did find a solution with the Report Viewer control, there is method to send the data source credentials to the server without the user having to type them in. Below is an example:

   1:  rvReporting.ServerReport.ReportServerUrl = new Uri("Http://localhost/reportServer");

   2:  rvReporting.PromptAreaCollapsed = true;

   3:  rvReporting.ShowProgress = true;

   4:  rvReporting.ServerReport.ReportPath ="DemoReport";

   5:  Microsoft.Reporting.WinForms.DataSourceCredentials dataSourceCredentials = new Microsoft.Reporting.WinForms.DataSourceCredentials();

   6:  dataSourceCredentials.Name = "ReportDataSource";

   7:  SqlConnectionStringBuilder str = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["Concord"].ConnectionString);

   8:  dataSourceCredentials.UserId = str.UserID;

   9:  dataSourceCredentials.Password = str.Password;

  10:  rvReporting.ServerReport.SetDataSourceCredentials(new Microsoft.Reporting.WinForms.DataSourceCredentials[1] { dataSourceCredentials }); rvReporting.RefreshReport();

Createing threads for a windows form

2004-12-10T18:26:00.001Z

I am wrining a program that has lenghtly process which si casing my UI to freeze. I now how to the solve the probelm which is create thread and let it do the processing. Now this new ground for me so I went looking for some explames on how to this and I found this link CodeGuru: Maintaining a Responsive UI, which I found easy to follow, that explains about UI thread and some of the probelms that can happen.

DTS Custom Tasks written .Net

2004-12-06T17:13:00.002Z

After writing some custom task in VB6 for a client thought it would be better if they were in .Net. So after some research finding that it can be done I start re-writing them for .Net. I started running into some issues:

Could not register custom task within the DTS client. This because Regasm does not expose the DLL entry point for DllRegisterServer, to solve this I had to write some extra code that will register the task in DTS.
The task then could not be use within DTS. This was because the default create interface was incorrect by the fact there was read-only function which dts does not like, I had disable the creation of default interface and write my own version.
The global variables not working after the .Net custom component set the values. This was some quirk between .Net and DTS as soon as try setting the value through the objects properties The activex script task and other tasks could not access the global variable. I mange to solve this deleting the Global variable and recreating it.

The main problem that can't be resolve is when running the these custom task there is a possible error that could happen (more so on a mutli-processor server) is the Virtual Runtime Error. The runtime error I was gettting was trying to call a function form object that no longer exist; this happens because between .Net and DTS the ownership of the object is losted and things get clear when they should not.

Reporting Services 2000

2004-11-03T09:44:00.001Z

I have been using reporting services for a while now and I've found it to be a useful tool for application development. The reason for this I am working on web application that had some summary data which need to be display with the option to allow detail data to be seen. Also there was a requirement to exporting the data to spreadsheet, generate some charts displays to make application look sexy and give users quick and easy status reports to read. The application is using Object Model approach so instead writing code and creating pages. I sold reporting service to the client and I manage to give all the requirements need with in matter of days. It was easy to use, Easy to install and easy to manage as well.

Multithreading .Net C#

2004-11-02T10:21:00.001Z

Here is a list of articles that makes threading sound easy to do:

Multithreading Part I: Multithreading and Multitasking

Multithreading Part 2: Understanding the System.Threading.Thread Class

Multithreading Part 3: Thread Synchronization

Multithreading Part 4: The ThreadPool, Timer Classes and Asynchronous Programming Discussed

The future role of a DBA in Middle to Small Companys

2004-07-12T13:45:00.001+01:00

This is a link about the futre role of a DBA. Also people thoughts about the article are at the end of the piece.

Developing Web Parts for SharePoint Portal Server 2003 in .NET

2004-07-01T16:20:00.002+01:00

Developing Web Parts for SharePoint Portal Server 2003 in .NET

Steven Wright Blog

A Business Intelligence project ALM's

3.1 Reducing unresolve reference warnings

Integrating a rules engine with integration services

Introduction

Options to consider for integrating with the rules engine

Our approach

Integration results

Comparing Master Data Services instances

Choosing the right CDC tool for the job

Introduction

Timescales

Evaluation criteria

Observations

Useful links

Source system data to warehouse via CDC.

Introduction

Development

Testing

Related posts.

Configuring Excel Services & PowerPivot on multi server Topology

Using Powershell to understand a replication topology

How To: Emailing the Sprint Burndown report

My Adventures in Codeplex

SQL Server Reporting Services MSBuild Tasks (ssrsmsbuildtasks)

Server Reporting Services Slide Shower (ssrsslideshower)

SfTS V3 Beta 2:Helping to see the wood for the trees

Unit Testing Report within Reporting Services: My Theory

SfTS V3: The Beta 1 reports new comers

SfTS V3 Operation: Reports face lift.

Reporting Services 2008 Report automatic upgrade gotcha.

Some practical SQL Spatial tips.

Working With Reporting Services Multiple Value Parameters

How to load spatial data into SQL Server 2008 from .Net

Business Intelligence with SQL Server 2008 Geometry Data Type.

Scrum for Team System Version 2.2 support for SQL Server 2008

The Scrum for Team System report slide show tool

Build Reports added to Scrum for Team System v2.2

New Reporting Features available in Scrum for Team System v2.2

Using NDepend to help guide Refactoring

How I built a Team Foundation Server custom data warehouse adapter

Building a Team Foundation Server custom data warehouse adapter

Could HTTP Soap / T-SQL endpoint be replaced by Reporting Services XML Reports?

How to E-mail the Sprint Burndown Chart to yourself or your team.

How to tell when the Team Foundation Server data warehouse was last updated.

SQL Server 2005 Merge Replication not replicationing changes to the subscribers

MSBuild using SQL Script parameters

To Collate or not to Collate

Delpoying Reporting Services Reports With MSBuild

Reporting Services Express Edition using host file redirect to connect to local database.

Report Viewer Customizing Printing for Local Report

Dynamic SQL Server ordering without using dynamic SQL.

Reporting Services Report Viewer Control & Page Sizes

Avery Labels Printing via RDL / Reporting Services

SQL Server 2005 Merge Replication Subscriber unexpectedly deleting during data Synchronisation.

Installing SQL Server 2005 Express with Remote Connections.

SSIS Creating Synchronous Transform Component

SSIS File handle leak in For Each Loop

SSIS Connection Object and Expressions

SSIS Variable Deadlock

SSIS Changes Database Settings

SSIS XML Task / Source Adapter

SSIS Execute SQL Task With VARCHAR(MAX)

SSIS 2005 and Database Snapshots

Reporting Services Report Viewer using SQL Authentication

Createing threads for a windows form

DTS Custom Tasks written .Net

Reporting Services 2000

Multithreading .Net C#

The future role of a DBA in Middle to Small Companys

Developing Web Parts for SharePoint Portal Server 2003 in .NET