When I work with clients and show them how Visual Studio Team System Database Edition can impact their SQL Server development process most of them are very impressed. I've helped several clients get going with these tools. One common concern, when you get past the Northwinds-like databases into large and complex databases is the startup time for loading Visual Studio solutions that include database projects.
There are some ways to significantly tool improve performance. First, if you are using Visual Studio 2005 Team Edition for Database Professionals, make sure you install SR1 and the Post-SR1 roll-up releases.
If you are using Visual Studio 2008, these fixes aren't yet available. However one specific fix can be easily performed manually with great improvements on the startup time. When I first read Gert's blog posting on database compatibility level I didn't realize that it would impact project startup performance. However it does!!!! On my test case I was able to cut the startup time almost in half (~8 minutes down to <4.5 minutes). Note that this was a large database with thousands of objects - it did a good job proving the performance improvement.
A couple of final common sense hints... Try to clean up your projects. The warnings and errors slow things down as well. Also, decouple your database projects. That is, if you can realistically have a solution with a single database project it will obviously startup and operate faster than one with a dozen database projects.
Over the past few weeks I've been presenting a series of Technical Briefings on Visual Studio 2008 Team System. At the tail of the briefing I demo and discussed what we officially know about next version of Visual Studio Team System, code named Rosario. I could only show and talk about these futures based on what was officially documented from Microsoft and what I could surmise from the publicly available VPC.
If you want to see the specifications for proposed functionality, the following specifications are available from the Rosario Specifications site on MSDN:
Although some of these were originally posted late last year, some were posted as recently as a month ago. I hope Microsoft continues to show us where they are going. It helps us provide the feedback they need and helps us plan for the future.
And on that note, this is the future... There is no released product for Rosario, no announced release date and no licensing for the CTP VPC. There are some great features coming up but not enough to diminish what is available today. VSTS is already a great product and when it becomes available Rosario will only make things better!
A final P.S. - If you're waiting on the content posting to the "SkyDrive" from the tech briefings I'm afraid you'll need to wait a little longer. Tara and I are still playing catch-up from the time on the road!
I've been demonstrating the capabilities of Visual Studio Team System recently in a series of tech briefings. In addition to what is available today, I've been showing some of what the future might hold using the November 2007 Community Technology Preview (CTP) of code name "Rosario".
Unfortunately, about a week ago the Visual Studio preview time-bombed and my demos became far less compelling. Actually, the CTP wasn't really scheduled to be obsolete yet and legally it can be extended for continued investigation or, in my case, demonstrations. I did the time honored trick of setting the clock of the Virtual PC's host back to a time before the expiration and managed to keep the demos alive.
At some point we can expect Microsoft to release another CTP without this problem. In the mean time, if you have the CTP and want to keep playing, Jeff Beehler offered me this little trick... You can turn off the synchronization of the VPC to the host machine by modifying the *.vmc (in this case Nov2007RosarioCTP.vmc).
To do so, edit your VMC and add the following within the <preferences><integration><microsoft> node:
<components>
<host_time_sync>
<enabled type="boolean">false</enabled>
</host_time_sync>
</components>
A few side notes: First, the release date on the Rosario CTP download page has been updated so this trick might only be needed for people who have already downloaded the image and don't want to re-download the 4.8GB image. Since I haven't downloaded the newer image I can't confirm this assumption.
Second, TFS doesn't like going back in time. If you are going to set the clock back on the VPC to allow it to keep working, don't set it back to a time before the last activity in the VPC TFS.
Finally, this trick isn't to get around licensing issues. Since this is a prerelease, the trick is only to keep us up and running until Microsoft gets a new version for us to play with.
Enjoy!
As I indicated in my first post of the year, I have given up New Year's Resolutions, but I do have intents for this year... In specific, getting back into community involvement and speaking.
Last week through an unexpected shuffle I ended up presenting on VSTS Technology Briefings this week and next week. These are free events sponsored by Microsoft and Notion Solutions and cover the breadth in VSTS 2008 and beyond in a demo-rich full day presentation. My collegue and fellow MVP, Mike Azocar, did the first two in briefings in Florida. I did one yesterday in Charlotte, NC and have three more scheduled in the southern US over the next week.
If you're in the area and want the latest on Team System 2008 and beyond, check out one of the following:
Thanks to Doug Turn for blogging easy access to the links and an idea of the schedule. Note that the actual schedule was modified slightly to include Visual Studio 2008 Team System - Database Edition.
And if you're near Little Rock instead, I'll be there on speaking on VSTS Database Edition and Unit Testing with Visual Studio 2008 Professional. That is, at the Little Rock Tech Expo 2008!
Hope to see you around! Enjoy!
As I work with different clients, helping them understand how Team Build and Team Edition for Database Professionals can help, a frequent request I get is "How does SQL Server **** Services work with it?" (With **** being 'Integration', 'Reporting', etc.). My general answer is "These already have Visual Studio integration so you don't need special tools for version control and work item integration." However, if you've been using Team System you already know that Version Control and work items are not the entire story.
As I've described in previous posts (parts 1, 2 and 3), Team Build can be used to very effectively create and execute change scripts against a target database as part of you application build process. In fact, you can also automatically generate test data and execute unit tests against the resulting database.
So how does this tie into other SQL Services such as SQL Server Integration Services? The basic answer is 'not well'. To "Build" the other SQL Server services you generally need to 'EXEC' into Visual Studio (i.e., DEVENV) to cause the 'compilation' of the to occur. If you want more details on this process, check out Buck Hodges' blog.
As described in the title of this post, I am not even trying to 'Build' the SSIS packages. SQL Server can access SSIS packages (i.e., *.dtsx) directly from the file system and that is how my client was using SSIS. However, they did want to verify that the packages would still work after changes occurred to any of the related SQL Server databases or even the packages themselves!
Building an SSIS Validation Unit Test
We looked into a few possible ways to exercise or validate SSIS packages as part of the build process. The clear answer in our case was to build a unit test which called on the SSIS object model. This would allow us to either validate the structure of the SSIS packages against the target data sources or actually execute them.
Building a unit test to validate SSIS starts with a standard test project in Visual Studio; I use MSTest with Visual Studio Team System but nUnit or similar unit test frameworks should work as well. Your unit test project needs to reference the SQL assemblies: Microsoft.SQLServer.DTSRuntimeWrapper and Microsoft.SQLServer.ManagedDTS. Of course, you will need to have the appropriate SQL Server components installed on both your development environment and your build environments for this to work.
Once you've created the test project, you'll need two classes to support SSIS validation. First you will need a unit test class. Although this is obvious, it may not be obvious that you may only need a single unit test class, regardless of how many SSIS packages need validation. Second, you need a support class to sink the events from the SSIS engine.
SSIS Event Support Class
The SSIS Event Handling Class will provide an interface between the unit test and the SSIS validation or execution being performed by SQL Server. Although you could force fit this back into the unit test class, I chose separate it to make it easier to understand.
The following snippet demonstrates a minimal implementation of the SSIS Event Handling class; I've included a few comments in areas that you're likely to expand on:
using Microsoft.SqlServer.Dts.Runtime;
// Additional “using” statements omitted for brevity
// This class receives events from SSIS, logs them to the “console”
// and stores them in a list for consumption by the unit test.
namespace SSISTestFramework
{ // “DefaultEvents” base class is from the SSIS Runtime
internal class SsisEventSupport : DefaultEvents
{
private List<string> errors = new List<string>();
public List<string> ErrorCollection
{
get { return errors; }
}
public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError)
{
Console.Write(" *** {0} ", description);
errors.Add(description);
return base.OnError(source, errorCode, subComponent, description, helpFile, helpContext, idofInterfaceWithError);
}
}
}
You can use this block as a starter to build your own class. You may want to override some of the other events from by the DefaultEvents class to gather more information about what is happening during SSIS validation and execution.
SSIS Unit Test Class
Of course, the event handling class is nothing without something to wire it to the SSIS engine. The unit test class will configure SQL Server Integration Services engine to reference the SSIS package, validate it and report the results through the event handling class.
As before, a code-snippet is worth a thousand words so here is a minimalist implementation:
using Microsoft.SqlServer.Dts.Runtime;
// Other 'using' statements omitted for brevity
namespace SSISTestFramework
{
[TestClass]
public class SsisValidation
{
public SsisValidation() { }
// Other 'standard' unit test stuff such
// as test context omitted for brevity
// You may want to databind the package file to easily
// validate different packages with a single test.
[TestMethod]
public void ValidatePackage()
{
string packageFile = @"C:\SomePath\SomePackage.dtsx";
bool validateOnly = true;
// Note: the WriteLine is valuable for getting
// status on test runs with multiple packages.
Console.WriteLine("Validating \"{0}\"...", packageFile);
List<string> failures = ValidateExecutePackage(packageFile, validateOnly); // Verify no errors were passed back from SSIS.
Assert.AreEqual(0, failures.Count,
packageName + " failed SSIS Validation.");
Console.WriteLine("... Completed validation");
}
// This support method simplifies call SSIS
private static List<string> ValidateExecutePackage(string packageName, bool validateOnly)
{
// "Wire up" the SsisEventSupport object.
IDTSEvents events = new SsisEventSupport();
try
{
// The SSIS (*.dtsx) file is an XML document. We
// load it into memory to pass off to the
// SSIS object model.
XmlDocument doc = new XmlDocument();
doc.Load(packageName);
Package pkg = new Package();
pkg.LoadFromXML(doc, events);
// NOTE: It is possible to reference the 'pkg' object
// to substitute connection information for testing
// purposes. Leave null for existing connections.
Connections cons = pkg.Connections;
// You can execute or simply validate the structure based
// on the validateOnly flag
if (validateOnly)
pkg.Validate(cons, null, events, null);
else
pkg.Execute(cons, null, events, null, null);
}
catch (System.IO.FileNotFoundException)
{
Assert.Fail("Failed to access package: " + packageName);
}
// Return the error list from SsisEventSupport.
return ((SsisEventSupport)events).ErrorCollection;
}
}
}
Some people are good at following through on New Years Resolutions - others are not. The only "New Years Resolution" that I've ever managed to keep was to "never again make a New Years Resolution".
On January 1st I received an email notification from Microsoft that my MVP status had been renewed. It probably shouldn't be too surprising - I think I've helped more people across the United States understand how to successfully adopt my current technology tool of choice, Visual Studio Team System, than I have historically. But 2007 year was different for me. I spent far more time in corporate settings educating the corporate development teams than in the "community."
Since joining Notion Solutions, I have spent more time flying around the country helping people with technology. As a result I've had to give up my leadership over the Dallas .NET Users Group and have had less time to focus on speaking engagements, user groups, publications and other community activities. And frankly, I miss it...
I'm not going to break the one good New Years Resolution that I have managed to keep (i.e., No more New Years Resolutions), but I do plan to be more active in the community this year. Let's call it a "goal" to regain some community involvement in 2008 - starting with the Little Rock Tech Expo 2008, articles for Paul Hacker's TFS Times and the Notion Solutions newsletters, and participation with the Dallas VSTS Users Group. Hopefully I'll manage to maintain support for my corporate users while reaching back into the community!
Happy 2008!
I don't usually like to pass on what others have blogged, but this release of the "Team Foundation Power Tools" is pretty slick, including:
- Find in Source Control
- "Quick Label"
- Open In Windows Explorer (i.e., open Windows Explorer using a right-click in the Source Control Explorer window)
- Team Build notification (desktop tray)
- TFPT command line enhancements (e.g., DestroyWI and DestroyWITD)
- TFS 2008 Support - including an updated "Best Practices Analyzer" and "Process Template Editor"
The caveat is that this is for Visual Studio 2008... so if you needed another excuse to upgrade, here it is! :)
Ed Hintz blogged more details on the release which can be downloaded from here.
Happy Holidays!
If you're in the Dallas/Fort Worth area and are an avid Visual Studio Team System User, you'll be interested in a new users group. I presented at the Dallas VSTS Users Group inaugural meeting last night. It was a small group (12) of interested, engaged and experienced VSTS users, admins and consultants. Perhaps more interesting that the topic were all of the side discussions we had - everything from system architecture to licensing to assorted tips and tricks.
Again, if you're in the area, look us up at www.dallasvsts.com. If you not from the area - consider moving ;)
I usually try to avoid simply passing on what others have already announced. This is one of my exceptions. If you haven't already heard, Visual Studio 2008 has released and is available through MSDN downloads. I'm downloading Team Suite it as I type. I haven't seen TFS 2008 posted yet but it is supposed to be live today as well!
There are numerous good posts regarding this but I want to point specifically to Jeff Beehler and his Visual Studio 2008 ships post. This one includes a list of the key updates that make up Visual Studio 2008 Team System.
Happy Downloading!!!!
I've been working with many different companies on Application Lifecycle Management with Team System and, more recently, doing a lot of work with quality assurance. I've also been teaching SQE's Visual Studio Team System tester class. In all cases I receive common questions about whether you can change the included Microsoft Word template or if you can use another format such as Excel. The answer to both questions is 'Yes', but it isn't simply clicking a button.
Here is some background to help you understand how Manual Tests are stored which then leads to how you can adapt them...
What is an MHT file?
Anyone who has created a manual test in Visual Studio will recognize the icon and the editor. The file created appears to be a standard Microsoft Word document. However it uses a less known files extension: mht. For those of you not familiar with it, this is not some bizarre proprietary Microsoft Word-only format. In fact, it is probably better known in its relationship to web browsers such as Internet Explorer. An MHT file is an archived web page; unlike a standard HTML file, all of the content required to render the page is embedded within the MHTML document Itself.
|
So why does this matter in terms of testing? This little fact reveals how the manual test runner in Visual Studio operates: the portion of the screen that displays the manual test content is basically just a web browser.
This means that most things that can be displayed in a web browser can be displayed in the Visual Studio manual test window. It also opens the door for us to create our own MHT files with other tools - such as Excel. |
 |
Just add Properties...
So an MHT file is basically just a web page, right? Right! And I can create an MHT file with just about any Microsoft Office application, right? Right! So to create my own MHT template I just need to save it as an MHT and I'm done, right? Well, almost...
|
There is still a some magic information that is required to convert an MHT into a Manual Test. In specific, you need to add the fact that it is a manual test and an unique identifier for that specific test. If your MHT editor is Microsoft Office, you can find this in the document properties. For Microsoft Word 2007, accessing document properties is as easy as 1.. 2.. 3.. First you access click the Microsoft Office button in the corner, then select Prepare and finally Properties. If you are using Office 2003 (Word or Excel) it is even easier - just select "Properties" under the File menu.
The properties that allow Manual Tests to operate are all custom properties. You can view them in an existing document by selecting the Custom tab on the document properties window.
The two custom properties we are interested in are:
TestType - This property must contain the value "Manual Test".
TestID - This property must contain a GUID value that is unique across the solution. In fact, by it's definition, a GUID should be globally unique. If you have multiple tests with the same GUID you could confuse Visual Studio!
If you don't already know about GUIDs, let me point you to a utility built into Visual Studio that allows generation of GUIDs. To access it, simply pull down the "Tools" menu and access "Create GUID". If you use this method to generate a GUID, you can directly copy it using the 'Copy' button. Use the radio button marked '' to make sure you are selecting the correct format.
One additional note: the additional Manual Test properties, such as Associated Work Items and Priority, are also stored as custom document properties. |
 |
Summary steps for creating an Excel 2003-based Manual Test...
Since many people have existing Microsoft Excel-based test cases that they'd like to convert for use with Visual Studio 2005 Team Edition for Software Testers (or in the very near future, Visual Studio 2008 Test Edition), I've summarized the above information into the following steps converting spreadsheets into Manual Tests.
- Open or create your Excel spreadsheet using Microsoft Excel.
- Access the document properties as described above.
- Select the "Custom" tab.
- Create a new property named "TestType" (no space), with a type "Text" and the value "Manual Test" (including a space). Click the "Add" button to add it.
- Create another new property named "TestID" of type text.
- Create a new GUID; you can use your favorite tool or Visual Studio as described above. Paste it in as the value for your "TestID" property. Note that the GUID should be in 'Registry format' without the curly braces (e.g., "9C352DB0-BCE1-4D8C-854E-D890BDE19573"). Remember to click the "Add" button.
- Click OK to exit the properties dialog.
- Save the spreadsheet as a different file (i.e., use the "Save As" feature from the menus). Make sure to select "Single File Web Page (*.mht, *.mhtml)" as the format as shown to the right.
- "Add" the new test case to your test project. From within Visual Studio with a test project already and visible in Solution Explorer, right-click on the destination project and select Add > Existing Item... This is also shown in a capture to the right.
- Browse to and select the MHT file that you just saved.
|
 |
What about Excel 2007?
Unfortunately the Excel MHT rendering engine for 2007 has been modified. I'm sure it is better in many ways, but unfortunately it strips off the required document properties. You can still achieve the desired results, but it take a little more work. And unfortunately once you do the conversion, you won't be able to use Excel to modify your Manual Test spreadsheet as doing so will strip out the required custom properties.
If you still want to go ahead with it, perform the steps above with the exception of 2-7, Once you've saved it as an MHT, you can open it using a text editor. If you are using Visual Studio, you can right-click on the file, select "Open With..." and then choose the Source Code editor, the XML Editor or even the HTML editor. Once it is open, scroll to the bottom of the file and locate the xml tag using the Microsoft Office schema. You need to modify that block to include a "CustomDocumentProperties" tag with the required document properties. The following XML snippet shows a sample with the TestType and TestID properties added.
<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
<o:MainFile HRef=3D"../SampleExcelTest.htm"/>
<o:File HRef=3D"stylesheet.css"/>
<o:File HRef=3D"tabstrip.htm"/>
<o:File HRef=3D"sheet001.htm"/>
<o:File HRef=3D"sheet002.htm"/>
<o:File HRef=3D"sheet003.htm"/>
<o:File HRef=3D"filelist.xml"/>
<o:CustomDocumentProperties>
<o:TestType dt:dt=3D"string">Manual Test</o:TestType>
<o:TestID dt:dt=3D"string">18caa06a-0a91-4070-ab62-2123769277d4</o:TestID>
</o:CustomDocumentProperties>
</xml>
A slightly better alternative would be to Publish the individual worksheet you need instead of the entire workbook. When you do this, the resulting MHT file can be modified in Word 2007. This includes adding the custom properties through the user interface, as described in the first section of this post, or manually as described in this section. If you do in manually, there will not be an existing xml node as shown in the above sample. Instead, you will need to add the <xml... tag, all of the boldfaced custom property information as shown above, and the closing xml tag, as shown below:
<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
<o:CustomDocumentProperties>
<o:TestType dt:dt=3D"string">Manual Test</o:TestType>
<o:TestID dt:dt=3D"string">18caa06a-0a91-4070-ab62-2123769277d4</o:TestID>
</o:CustomDocumentProperties>
</xml>
Hey, My Spreadsheet opens in Word!
If you were using Excel 2007 and only published the single worksheet, this is the best case. But if you are still in Excel 2003 and want to continue using Excel as your editor, there is hope! When you right-click on a file in Visual Studio to open it with an alternative editor, you have the ability to "Set as Default". With a little luck, you will have the "2003 Microsoft Office Component" in your list of options. This editor is capable of opening an MHT file with Excel if it was generated in Excel or with Word if it was generated in Word.
This concludes this post for the moment. However I do hope to publish a follow-up... If you noticed my list of steps and thought "Gee - that could be scripted" you are right. I am going to do some cleanup and post an approach for mass migration of existing Excel spreadsheets into Manual Tests. I also plan to file an enhancement request with to Office to to see if we can get the custom properties back!
Cheers!
More Posts
Next page »