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;
}
}
}