Etl

Version 1.6 introduces tests for Etl.
Important note:
  • Currently (will be changed in a near future) NBi supports testing Etl runs on the same server than the test. It means that it’s not possible to run your test on your own computer or on your build server and try to execute remotely an SSIS package.
  • Only SSIS packages can be executed (Support for others Etl is not planned)

Define the System-under-test (or the setup element)

For this you’ll need to define your tested package in the system-under-test and specify its name in the corresponding xml attribute (the extension dtsx is not needed):
<system-under-test>
  <execution>
    <etl name="myPackage" >
    </etl>
  </execution>
</system-under-test>


To be testable a package could be hosted ondifferent ways: on SQL Server, on a SSIS Catalog or on a file.

Hosted on a file folder

To define an Etl, available on a file folder, you must define its path in the corresponding xml attribute named path. Note that this path is relative to your test-suite and should end by a backslash.
<etl path="relative-folder\" name="myPackage"/>

You can also provide an absolute path starting by the letter of a drive.
<etl path="C:\absolute-folder\" name="myPackage"/>

Hosted on SQL Server

If you want to specify that your package is hosted on a SQL Server, then specify the xml attribute named server with the name of your SQL Server. The attribute path is relative to the root of your SSIS Server and should start and end by a slash.

<etl server="." path="/SSIS/" name="myPackage"/>

Some packages could be encrypted, in this case it's needed to provide the password. It can be done using the xml attribute name password.

<etl 
    server="." 
    path="/SSIS/" 
    name="myPackage"
    password="p@ssw0rd"
/>


If you want to run your package in the context of a specific user, you must specify the xml attributes username and password.

<etl 
    server="." 
    path="/SSIS/" 
    name="myPackage"
    username="myusername"
    password="p@ssw0rd"
/>

Hosted on SQL Server (SSIS Catalog)

You can also access the Ssis Catalog provided with SQL Server version 2012 and above using the xml attributes named catalog, folder and project to respectivelly define the name of this Ssis Catalog, the name of the folder and the name of your Ssis project.

<etl
    server="localhost"
    catalog="SSISDB"
    folder="Folder"
    project="MyProject"
    name="package.dtsx"
 />

Since version 1.9, for the packages stored on a SQL Server in the SSIS Catalog, it's possible to overwrite the default timeout of 30 seconds. To achieve this, just specify an xml attribute named timeout and its value in milli-seconds. Note that the way the package is started is slightly different to achieve this feature, use this only when needed. In the sample here under, the timeout is set to one minute.

<etl
    server="localhost"
    catalog="SSISDB"
    folder="Folder"
    project="MyProject"
    name="package.dtsx"
    timeout="60000"
 />

Parameters and variables

Parameters (Since SQL Server 2012)

An SSIS package is usually developed with parameters for variables (such as connection strings or folders, file names, …) that you’d like to define at runtime. NBi supports to define these package and project (since version 1.9) parameters in your test definition by providing an xml element named “parameter” and available as a child of the xml element etl.

You’ll need to define the name (xml attribute) and the value (xml text) of the parameter that you want to supply to the etl at runtime.

<etl path="Etl\" name="Sample.dtsx" password="p@ssw0rd" timeout="10000">
    <parameter name="DestinationPath">C:\toto-timeout.txt</parameter>
    <parameter name="TopRows">4000</parameter>
</etl>

Variables (not available for SSIS Catalog)

Note that it will only work if you define your package as a DTS package (so not a Catalog package). This means that you should configure it as Hosted on a file folder or Hosted on SQL Server but not as Hosted on SQL Server (SSIS Catalog).

The usage and the syntax is exactly the same than for package parameters. Meaning that you don't need to adapt your test if you transform your variable into a parameter. Note, that unfortunately, for the moment, you must use the full name of the variable (including the namespace, usually User::), but it will be eventually fixed in a next release.

<etl path="Etl\" name="Sample.dtsx" password="p@ssw0rd" timeout="10000">
    <parameter name="User::DestinationPath">C:\toto-timeout.txt</parameter>
    <parameter name="User::TopRows">4000</parameter>
</etl>

Define tests

Currently, it’s possible to define three kinds of tests for an Etl: validate that a run is successful, validate the execution timespan for a package and, finally, validate the side effects (consequences) of a run. Note that last kind of test is fundamentally different. Indeed, the system-under-test is not the Etl but the object on which you want to observe a change after the run of your Etl. This kind of test is detailed on the page Test side effects of an Etl

Last edited Jan 24, 2015 at 2:03 PM by Seddryck, version 7

Comments

No comments yet.