Using DbFit with SSIS

[KenCollier]

I am doing data warehouse development in SQL Server 2005 and need the
ability to test SSIS packages using DbFit. Has anyone tackled this
problem?

As a reference point I have had prior success using MbUnit to test
SSIS packages. This approach uses the Microsoft.SqlServer.Dts.Runtime
namespace (note: SSIS was formerly called DTS). That class library
includes objects such as DTSExecResult, which represent result sets
from the execution of an SSIS package. This testing approach works
fine, but can be challenging for data warehouse developers who don't
know anything about object-oriented programming.

I really love DbFit and would like to use it on a current project for
testing SSIS, stored procs, DDL scripts, etc. Any suggestions?

[Gojko Adzic]

Hi Ken,

sorry, i have no idea what SSIS is. My primary weapon of choice when
it comes to databases is oracle. having said that, i'm interested in
helping you connect dbfit to that, whatever it is, if it makes sense
to run in a tabular way. can you send me a couple of examples of what
you need?

gojko

[KenCollier]

Thanks Gojko - SSIS (SQL Server Integration Services) is MSFT's ETL
(extraction, transformation, load) tool. Data warehouse developers use
ETL to pull data from source systems; clean it up; transform it into
the proper form for the warehouse; and load it into the warehouse.
Oracle data warehouse developers typically use Informatica for ETL,
but SSIS is built directy into SQL Server.

As a developer using SSIS you create a package to perform certain data
handling task(s). Ideally each SSIS package performs a single cohesive
task, and is loosely coupled with other packages. Inside each SSIS
package is typically some prepackaged data handling functions along
with some custom SQL code. We build SSIS packages in Visual Studio
using a visual editor. The end result is a file with a .dtsx extention
(SSIS was formerly Data Transformation Services or DTS).

These files are generally bundled in a Visual Studio solution and
executed as a scheduled job in production. However, they can be
executed individually from the command line like this:

dtexec.exe /F ssisPackage.dtsx.

I want to use DbFit to unit test each package separately. Here's how
the process should look (I think):
1. Setup: Insert test data into the source table(s)
2. Execute: Run the the SSIS package
3. Test: Verify that the resulting data in the target table is
complete, correct, etc.
4. Teardown: Delete test data from both source and target tables

The part I can't figure out how to do in DbFit is the Execute step. I
think the other steps are pretty straight-forward insert, select, and
remove queries.

Does that clarify?

Thank you, Ken

> > testing SSIS, stored procs, DDL scripts, etc. Any suggestions?- Hide quoted text -
>
> - Show quoted text -

[Gojko Adzic]

> Thanks Gojko - SSIS (SQL Server Integration Services) is MSFT's ETL
> (extraction, transformation, load) tool. Data warehouse developers use
> ETL to pull data from source systems; clean it up; transform it into
> the proper form for

> The part I can't figure out how to do in DbFit is the Execute step. I
> think the other steps are pretty straight-forward insert, select, and
> remove queries.
>

in this case, i suggest you implement just the ETL execution step as a
different fixture. you can do it in two ways:

1 - create a subclass of sqlservertest where you add a method to
execute the ssis package if this can be done in process using the
current transaction set up by dbfit; then you don't need to worry
about cleanup as it will do it for you. instead of dbfit.sqlservertest
start the test by using your fixture name

2 - use dbfit in standalone mode, with connection set up through
DatabaseEnvironment. then implement a fixture to execute the ssis
package and get connection details from
DbEnvironmentFactory.getDefaultEnvironment().getConnection(). Invoke
your fixture where you want to execute the ETL package.

gojko

[Alexander Karmanov]

Hi Ken,
Would it be possible to run dtexec with xp_cmdshell?

Thanks,
Alexander

MSN, Yahoo Messenger: alexander...@yahoo.ca
Skype: alexander_karmanov

[KenCollier]

Clearly I need to dive deeper into the code. It sounds like option #1
is the best approach architecturally - do you agree?

If I tackle this, do I need to download the source code for DbFit
separately and muck around in there?

Here is my current C# utility method for executing an SSIS package. I
suspect I would add something similar to the subclass that you
describe. Does that sound about right?

public DTSExecResult RunThePackage(string customerId)
{
string pkgLocation;
Package pkg;
Application app;

pkgLocation = SSISPackageFolder + "\\ssisMaster.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);

Variables vars = pkg.Variables;
vars["varCustomerId"].Value = customerId;

m_DtsPkgResults = pkg.Execute();
return m_DtsPkgResults;
}

Thanks for your help on this. I'm new to DbFit but reasonably familiar
with Fit/FitNesse. I assume the source code revision is pretty
straight forward.

Ken

[KenCollier]

This seems like a potential alternative. If my experiment with Gojko's
suggestion proves too complicated, I'll give this a try. Thank you.

On Mar 26, 8:22 am, "Alexander Karmanov" <alexander_karma...@yahoo.ca>
wrote:

> Hi Ken,
> Would it be possible to run dtexec with xp_cmdshell?
>
> Thanks,
> Alexander
>

> MSN, Yahoo Messenger: alexander_karma...@yahoo.ca

> > - Show quoted text -- Hide quoted text -

[Jan]

Hi Ken,

I use dbfit to test other ETL Tools like Oracle Warehouse Builder or
Pentaho Dataintegrator (Kettle).

My approach is to use the CommandLineFixture and Dbfit in FlowMode
http://fit.c2.com/wiki.cgi?CommandLineFixture

Using the command line fixture you can start a command and check the
output for specific words (like SSIS load successful or load failure
etc.)

Regards,
Jan

[KenCollier]

Hi Jan,

I like this approach, but can't seem to get it working. I compiled the
CommandLineFixture.java (which produces 3 .class files) and created
a .jar file container for the resulting files.

In my Setup I have:

!|dbfit.SqlServerTest|
!|Connect|localhost|user|password|master|
!path C:\Sandbox\adb_warehouse\test\CommandLineFixture
\CommandLineFixture.jar

and I'm trying to use the CommandLineFixture alongside a DbFit Query
like this:

|com.objectmentor.fixtures.CommandLineFixture|
|Title|Run Package|
|Command|dtexec /F "pathName\packageName.dtsx"|

|Query|SELECT COUNT(*)AS CNT FROM dbo.tableName|
|CNT|
|16|

In this approach DbFit doesn't recognize CommandLineFixture. What am I
missing?

Thanks for the help. Ken

On Mar 31, 9:27 am, Jan <m...@jan-ischebeck.de> wrote:
> Hi Ken,
>
> I use dbfit to test other ETL Tools like Oracle Warehouse Builder or
> Pentaho Dataintegrator (Kettle).
>

> My approach is to use the  CommandLineFixture and Dbfit in FlowModehttp://fit.c2.com/wiki.cgi?CommandLineFixture

[Gojko Adzic]

path doesn't work in setup, it needs to be in the parent page (suite
or root). this is a fitnesse constraint, not dbfit.

gojko

[KenCollier]

Okay - I've moved the path up to the root page like:

!path dotnet2\*.dll

!path C:\Sandbox\adb_warehouse\test\CommandLineFixture
\CommandLineFixture.jar

and I also tried it like:

!path dotnet2\*.dll; C:\Sandbox\adb_warehouse\test
\CommandLineFixture\CommandLineFixture.jar

The latter approach threw an exception even though PATH_SEPARATOR is
defined as a semi-colon:

System.ApplicationException: Type 'dbfit.SqlServerTest' could not
be found in assemblies.

The first approach has the same affect as including the path in the
setup. DbFit still doesn't recognize CommandLineFixture. Any other
suggestions?

Thanks, Ken

[Gojko Adzic]

Ken,

it looks like you are using the .net version of dbfit (!path ... dll).
that will use the .net fit test runner, which will not be able to
execute java fixtures (command line fixture is java). i think that the
easiest thing to do is to write your own .net fixture which will call
ssis packages to execute and then include that in the !path).

gojko

[KenCollier]

Oops - my mistake.

So, I have created a simple .NET fit.Fixture called
warehouse.etlTest.ExecuteSsisFixture

It is a class that takes a package location, executes it, and returns
a result (see code below).

I've compiled this into ExecuteSsisFixture.dll and placed the .dll
into the DBFit\dotnet2 directory. I have !path dotnet2\*.dll in the
root page of my test suite. However, when I try:

|warehouse.etlTest.ExecuteSsisFixture|
|packageLocation|
|"C:\Sandbox\mySamplePackage.dtsx"|

DbFit doesn't recognize the fixture. It puts a little question mark
after the fixture name. What am I doing wrong?

(On a side-note - How can I tell DbFit/Fitnesse to ignore a word that
is in camel case but is not a separate test or suite?)

Thanks, Ken

-------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace warehouse.etlTest
{
public class ExecuteSsisFixture: fit.Fixture
{
public string packageLocation;

public DTSExecResult RunThePackage()
{
Package pkg;
Application app;

app = new Application();
pkg = app.LoadPackage(packageLocation, null);

return pkg.Execute();

[Marisa Seal]

Use an exclamation mark like so:

!|warehouse.etlTest.ExecuteSsisFixture|
|packageLocation|
|"C:\Sandbox\mySamplePackage.dtsx"|

The ! before a table tells FitNesse to ignore CamelCase words because
it's a test or comment table.

In verbiage or comments that you have on the page, you can use the
syntax !-MyCamelCaseWord-! which will cause FitNesse to ignore the
fact that it's a camel case word and doesn't have a corresponding wiki
page.

Marisa

[KenCollier]

Thanks Marisa, that gets rid of the ? but my test is throwing the
following exception:

System.IO.FileLoadException: Could not load file or assembly
'fit, Version=1.1.0.40850

even though fit.dll and my ExecuteSsisFixture.dll are both in the
dotnet2 directory. Any suggestions?

Ken

> > > gojko- Hide quoted text -

[Marisa Seal]

Hmm...what is the version of fit.dll that is in your dotnet2
directory?

The reason I ask is because I see you've already got the !path to
dotnet2 declared (from a previous post so I presume you've still got
it lol). Therefore, the only thing I can come up with that may be the
problem is that the version of fit that you referenced in the fixture
is 1.1.0.40850 but the fit.dll you have in the dotnet2 folder is
different. If that is the case, update your fixture project to
reference the version that's in your dotnet2 folder, OR overwrite the
version in your dotnet2 with version 1.1.0.40850 Either way you're
just making sure that you're using the same version in both places.

BTW, I think that they're up to version 1.7 by now so you may want to
get updated versions of fit and fitlibrary when you have time.

Hopefully that helps but if not, hopefully someone "out there" can
think of other sources of the problem. :-)

Marisa

[KenCollier]

Thanks Marisa - You were essentially right. The fit.dll that my
fixture referenced didn't match the one in DbFit/dotnet2.

After a few more gyrations my ExecuteSsisFixture works great now.
Here's a summary of my journey for the benefit of other SQL Server
data warehouse developers:

Alexander Karmanov recommended using DbFit's Execute command with
xp_cmdshell 'dtexec...' There are two problems with this approach:
1. xp_cmdshell cannot handle relative paths to the .dtsx file which
makes your tests very non-portable.
2. Since MSFT is moving toward managed CLR code for SQL Server,
there is some speculation that xp_cmdshell may be deprecated in the
future.

Jan recommended using Bob Martin's CommandLineFixture to launch dtexec
directly. I had the following problems with that (as witnessed by the
threads above):
1. CommandLineFixture is written in Java and DbFit for SQLServer is
in dotNET.
2. I compiled CommandLineFixture.java into a .dll using Visual
Studio's V# compiler, but it didn't work well with the dotNET version
of fit.dll. Not sure why.
3. I started porting the java code to Csharp, but realized that this
was serious overkill for what I was trying to accomplish.

In the end I followed Gojko's advice and just created a simple
ExecuteSsisFixture using the code below. I decided to base it on the
ColumnFixture so that I could easily check the return value from
dtexec.

Thanks to everyone for your assistance and ideas.

Cheers, Ken


-------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace warehouse.etlTest
{

public class ExecuteSsisFixture: fit.ColumnFixture
{
public string packageLocation = null; //File path to .dtsx
file
public int executionResult //0 = "Success"
{ //1 = "Failed"
get //3 = "Cancelled by
user"
{ //4 = "Unable to
locate file"
return runThePackage(); //5 = "Unable to load
file"
} //6 = "Internal error
occured"
}

public int runThePackage()

{
Package pkg;
Application app;

app = new Application();
pkg = app.LoadPackage(packageLocation, null);

return(Convert.ToInt32(pkg.Execute()));

> > > - Show quoted text -- Hide quoted text -

[Marisa Seal]

You're welcome - and thanks for sharing your fixture code and your
experiences with the various approaches.

I'm just starting to work with SSIS and knowing how to initiate a
package via a fit fixture will be a great help when I start my next
project. :-)

Marisa

[Rema S]

Hi Ken,

Do you know if I can still use DbFit if my SSIS package writes to a flat file instead of a DB?

Warm Regards,

Rema S