SSISDB Environment and Server as configuration parameters

[Mis1av]

Hi I want to be able to define two parameters in my nbits file, Server and Environment and pass them on to the server and environment attributes under etl-run tag. I have something like this in mind:

<settings>
   <default apply-to="everywhere">
      <parameter name="Server">localhost/parameter>
      <parameter name="Environment">MyEnvironment/parameter>
    </default>
</settings>

....

<etl-run version="SqlServer2016" server="@Server" 
    catalog="SSISDB" folder="MyFolder" 
    project="MyProject" name="MyPackage.dtsx"  
    environment="@Environment" />

Nonetheless when I try to run this code I get an error message saying it can't find Server "@server" or Environment "@Environment". So in other words it's reading these values literally instead of referring to the parameters themselves (hence it reads @Environment instead of MyEnvironment).

Is it possible to do this? If yes what's the magic word to make it retrieve the value "MyEnvironment" and pass it on to environment? If not then what's the alternative to having to change these two attributes for potentially hundreds of packages?

[Seddryck]

It's not supported at the moment but I could add this kind of feature to the next release (default and reference). I usually don't test my etls on two environnements (only on a server dedicated to automated-tests) but I understand the need for this feature.

On a side note, server is required but pointless at the moment. You cannot remotely run an etl. It means that localhost, . or 127.0.0.1 should do the job and don't require an update if the test-suite.

[Seddryck]

Preview is available at https://ci.appveyor.com/project/Seddryck/nbi/build/1.15.0-ci.91

This preview supports the usage of reference and default for all attributtes of etl and etl-run except 32-bits, version and timeout.

The usage is the same than for report (and different to your sugestion):

<settings>
  <default apply-to="system-under-test">
      <etl server="localhost" environment="EnvironmentDefault"/>
    </default>
    <reference name="MyRef2">
      <etl server="127.0.0.1" environment="Environment"/>
    </reference>
</settings>
...
<test name="No override and default" uid="0000">
  <system-under-test>
    <execution>
      <etl path="/Etl/" name="Sample"/>
    </execution>
  </system-under-test>
  <assert>
    <successful/>
  </assert>
</test>
<test name="No override and reference" uid="0000">
  <system-under-test>
    <execution>
      <etl path="/Etl/" name="Sample" server="@MyRef" environment="@MyRef"/>
    </execution>
  </system-under-test>
  <assert>
    <successful/>
  </assert>
</test>

[Mis1av]

Thanks for the quick reply Seddryck. Please let me know when the feature becomes available to download.

[Seddryck]

It's available, use the link above and go to the tab "artefacts" select the package "framework".

[Seddryck]

@Mis1av It would be great to know if it effectively works on your environnement. Thx for your feedback!

[AndreasFGermany]

Hey Seddryck, thanks for your fast implementation of this new requirement. In our project we use SSISDB as package store in SQL 2016. Is your solution also working with SSISDB? We have problems to set the path variable correctly.

[Seddryck]

Huuuum, path is not an expected attribute when you're defining a package stored in SSISDB :-)

More info here

[AndreasFGermany]

Your solution expects the path variable if i would use the ssisdb-server in a reference

[Seddryck]

Oups, will check this tonight and fix it.

[Seddryck]

I'm sorry but I can't reproduce your issue. Could you post your test and the exact error message that you have?

[Seddryck]

Available in beta: https://github.com/Seddryck/NBi/releases/tag/v1.15-beta