Daniel Edward Stafford

One thing that is seriously lacking in the area of automation is the ability to automate tests for SSIS (SQL Server Integration Services) packages. Thankfully, there finally exists a framework to be able to make unit tests for these, and it is known as ssisUnit This is actually quite an interesting framework as it runs tests using a declarative structure (XML) rather than the traditional unit testing code, and is actually quite easy to change. Each file can have multiple tests for a single paackage or multiple packages, and each test can have multiple asserts, each determining 1 or more conditions. The types of commands are as follows:

• Directory Command: Takes 2 arguments (directory paths) and performs some operation on them (FileCount, Create, Move, Delete, or Exists)
• Variable Command: Takes a variable from the SSIS package and gets or sets the value
• File Command: Takes the file name for the source path or target path and performs and operation on it (LineCount, Copy, Move, Delete, or Exists)
• Process Command: Takes the path to an executable and also command line arguments for the process and executes the process
• SQL Command: Takes the name of the connection to use from the connection list defined at the top, a SQL command to execute

And here is a small example coming from the ssisUnit codeplex site which gets the tableCount variable from the package and verifies that the value of the variable is 71:

<?xml version="1.0" encoding="utf-8" ?>

<TestSuite xmlns="http://tempuri.org/SsisUnit.xsd">

  <ConnectionList>

		<Connection name="localhost.AdventureWorks" connection="Data Source=localhost;Initial 
		Catalog=AdventureWorks;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
		connectionType="ConnectionString"/>

	</ConnectionList>

  <PackageList>

		<Package name="ssisUnitExample" packagePath="C:\Projects\SSISUnit\SSIS2005\SSIS2005\
		ssisUnitExample.dtsx" storageType="FileSystem"/>

	</PackageList>

  <TestSuiteSetup>

  </TestSuiteSetup>

  <Setup>

  </Setup>

  <Tests>

		<Test name="Get Table Count" package="ssisUnitExample" 
		task="{1E207BEA-834E-46BE-8D2B-642C626DB589}">

			<Assert name="Verify Table Count" expectedResult="71" testBefore="false">

				<VariableCommand operation="Get" name="tableCount" value=""/>

			</Assert>

		</Test>

	</Tests>

  <Teardown>

  </Teardown>

  <TestSuiteTeardown>

  </TestSuiteTeardown>

</TestSuite>