search

Seddryck / NBi

NBi is a testing framework (add-on to NUnit) for Business Intelligence and Data Access. The main goal of this framework is to let users create tests with a declarative approach based on an Xml syntax. By the means of NBi, you don't need to develop C# or Java code to specify your tests! Either, you don't need Visual Studio or Eclipse to compile your test suite. Just create an Xml file and let the framework interpret it and play your tests. The framework is designed as an add-on of NUnit but with the possibility to port it easily to other testing frameworks.
http://www.nbi.io
Apache License 2.0
106 stars 37 forks source link

Define and execute an etl based on an ispac #332

Open Chresten888 opened 6 years ago

Chresten888 commented 6 years ago

Hi I have created a test, which calls a SSIS package in the setup of the test and his works fine (see below). I would like to have the path in the config file and have a reference to the path in the nbit file. The same with the arguments. I could not figure out how to do this, could you please help me with an example? Thanks in advance:-)

<setup>
   <tasks parallel="false">
      <exe-run
         name="DtExec.exe"
         path="C:\Program Files (x86)\\Binn\"
         arguments="/Project C:\ETLExample.ispac /Package ETLExample.dtsx 
/SET \Package.Variables[$Project::CILCatalog];'D5CIL_ETLExamplePOC'"
     timeout-milliseconds="10000"
      />
   </tasks>
</setup>
Seddryck commented 6 years ago

It's not possible to define attributes of exe-run as something to get from settings or as a variable and I'm not planning to add it.

Could you explain why you don't use etl-run?

Chresten888 commented 6 years ago

Hi Seddryck

Yes ofcourse, we do not use the etl run, because with etl run we have the following three options. 1) Execute a DTSX file 2) Execute it on a sql server 3) Execute it from SSISDB

We cannot execute the Dtsx file, since we use project parameters. We cannot execute from a SSISDB, because of kerberos security, that only allows us one connection. Since we develop on a local machine, we need one connection to the SSIS package and then the SSIS package also needs a connection towards a server with our tables. This is a connection to much. Kerberos can be reconfigured allowing more connection/jump but SSIS does not support this, so limit is one jump. You can say that we could move all our databases to the same server as the SSISDB, but this is a compromise we do not like:-)

The reason we would like the variables to be in a global variable and in the config file. Is because I am imagining that our testsuite, testing the SSIS package will have alot of calls in the setup with different data, testing various scenarious. This means that we will have the same input towards the ssis package for each testcase, so if we need to change a input we would need to do it in all places instead of doing it in a variable.

We have a setup, where we develop locally. Once development reach a stage where we think it works, we merge it together using TFS with other changes to the system. Then TFS executes the testcases and here we will need to change the paths etc. using powershell. In this step it would be nice to only change in the config file and not the nbit file.

It would be really nice to have the opportunity to create variables in the NBIT file. Then it would be nice if it was possible to make a reference from the variable to the config file, so the variable could be set in the config file. It would also be nice if etl-run supported ispac files.

Best regards Chresten

2017-12-16 0:07 GMT+01:00 Cédric L. Charlier notifications@github.com:

It's not possible to define attributes of exe-run as something to get from settings or as a variable and I'm not planning to add it.

Could you explain why you don't use etl-run?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Seddryck/NBi/issues/332#issuecomment-352134088, or mute the thread https://github.com/notifications/unsubscribe-auth/AgYyLliiBDi9yAJ4yy6YxzcAW2cyVcMGks5tAvusgaJpZM4RDh_J .

-- Med venlig hilsen Chresten Lei Sandager Nørremøllevej 9 6400 Sønderborg Mobil: 25465979 Email: clsandager@gmail.com

Seddryck commented 6 years ago

Hi @Chresten888 Your scenario is interesting and it's true that changing the path on each environnement will be a nightmare and that NBi should support a better way to do this.

I wasn't aware that there is a feature to run a package from an .ispac with DTEXEC. Unfortunately, I didn't find any equivalent in the C# dll.

Variables have been designed to support dynamic evaluation of a parameter. I don't think they are suited to avoid repetition all over a test-suite. I'd recommend to use defaults or references for this.

I could propose a variante of etl-run accepting an .ispac file and starting one of its package with DTEXEC. It's technically possible to look for the path of DTEXEC in the registry (Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\...\SSIS\Setup\DTSPath). It's already possible to define a reference with an ETL (apparently missing in documentation but available through https://github.com/Seddryck/NBi/issues/277 ). I should also ensure that it's possible to forward the reference to the config file.

Chresten888 commented 6 years ago

Hi Seddryck

Thanks for your fast replies - I am abit amazed:-) I will definitely look into defaults and references, for handling the repetition issue.

The variant with elt-run accepting an ispac file, is that something you will look into? because if a reference with an ETL is already possible, then I am set:-)

I just went for Christmas holidays today, so I will be looking more into this in the new year. Thanks for your response and help - I hope you will have a nice Christmas and a happy New Year!

2017-12-18 23:26 GMT+01:00 Cédric L. Charlier notifications@github.com:

Hi @Chresten888 https://github.com/chresten888 Your scenario is interesting and it's true that changing the path on each environnement will be a nightmare and that NBi should support a better way to do this.

I wasn't aware that there is a feature to run a package from an .ispac with DTEXEC. Unfortunately, I didn't find any equivalent in the C# dll.

Variables http://www.nbi.io/docs/variable-define/ have been designed to support dynamic evaluation of a parameter. I don't think they are suited to avoid repetition all over a test-suite. I'd recommend to use defaults or references http://www.nbi.io/docs/config-defaults-references/ for this.

I could propose a variante of etl-run accepting an .ispac file and starting one of its package with DTEXEC. It's technically possible to look for the path of DTEXEC in the registry (Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server...\SSIS\Setup\DTSPath). It's already possible to define a reference with an ETL (apparently missing in documentation but available through #277 https://github.com/Seddryck/NBi/issues/277 ). I should also ensure that it's possible to forward the reference to the config file.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Seddryck/NBi/issues/332#issuecomment-352578078, or mute the thread https://github.com/notifications/unsubscribe-auth/AgYyLkyu3UAc-O5WwUqfPGhPjDJnxaCIks5tBuamgaJpZM4RDh_J .

-- Med venlig hilsen Chresten Lei Sandager Nørremøllevej 9 6400 Sønderborg Mobil: 25465979 Email: clsandager@gmail.com

Seddryck commented 6 years ago

Hi @Chresten888

Yes, I'll need to add something. References and defaults don't support many elements and etl is one of them but exe-run is not available. At the moment there is no solution for your case and I'll try to add one.

Chresten888 commented 6 years ago

Hi

Ok - that sounds real good, that was alot more than I expected, so thank you very much.

2017-12-19 11:42 GMT+01:00 Cédric L. Charlier notifications@github.com:

Hi @Chresten888 https://github.com/chresten888

Yes, I'll need to add something. References and defaults don't support many elements and etl is one of them but exe-run is not available. At the moment there is no solution for your case and I'll try to add one.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Seddryck/NBi/issues/332#issuecomment-352706606, or mute the thread https://github.com/notifications/unsubscribe-auth/AgYyLiYYOaStqRQQq_uFKwMFnTe7gaK9ks5tB5MmgaJpZM4RDh_J .

-- Med venlig hilsen Chresten Lei Sandager Nørremøllevej 9 6400 Sønderborg Mobil: 25465979 Email: clsandager@gmail.com