SSIS tSQLt test extension - is this the right approach?

[Rong Chen]

We discovered that the SSIS package won't get executed within the tSQLt test transaction. We developed the extension to circumvent the situation by replacing the rollback of the transaction with a set of stored procedures responsible for cleaning up after the test.

This extension consists of 5 new stored procedures for the tSQLt framework and 1 tSQLt stored procedure modified to work with the new procedures but keep the original behavior when used in tests for non SSIS package testing.

The use of the extension does not require any changes in the tSQLt test design.  The only requirement is to name the SSIS package test name as "test SSIS ..."

It has only been tested on an on-premise SQL Server 2016, it has not been tested on any other edition/permutation of SQL Server such as Azure.

The extension consists of:

tSQLt.Cleanup

Main proc to clean up the fake objects and revert any changes in all the test databases.

tSQLt.Private_Cleanup

Helper proc to clean up the fake objects and revert any changes in a specific test databases.

tSQLt.Private_Cleanup_SpyProcedureLog

Helper proc to clean up the fake objects and revert any changes related to SpyProcedureLog in a specific test databases.

tSQLt.Private_Cleanup_tSQLt_tempobject

Helper proc to clean up the fake objects and revert any changes related to tSQLt_tempobject in a specific test databases.

tSQLt.SSIS_Fail

Helper proc that negate all the transaction controls when test SSIS package has failures.

tSQLt.Private_RunTest (existing proc)

Modified to negate all the transaction controls when testing SSIS package

We have tested this extension successfully with SSIS packages excusion tests and non SSIS test (keeping the oiriginal behaviors), but we like to confirm if this is right approach, or if we missed any that requires clean up.

Many thanks!

[Sebastian Meine]

Rong,

From what I can tell (with the limited information you provide), this looks good. 

Did you write any actual test cases for those many new procedures?

Thanks,

Sebastian

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rong Chen]

Hi Sebastian:

Thank you for the quick response!

If you asked the test cases for these new procs, yes we tested all but one (tSQLt.Private_Cleanup_SpyProcedureLog, since we have no plan to use it at present time).  We will create tSQLt test procedures to test our SSIS packages pretty soon. 

Attached please find the sql code for the 6 procs.

Rong,

Thanks,

Sebastian

To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.

[Rong Chen]

Hi Sebastian:

We plan to use azure container to do these SSIS unit tests on the new SSIS related objects and changes. Do you think you can use the container to look at the test cases?  Hopefully we can script out the container so that you can easily build your own for this purpose.  We are trying to have everything finished in a week, and check them in to the tSQLt GitHub repository by the end of this month.  Do you think this is feasible?

Many thanks!

Rong

[Larry Blake]

Adding to Rong's comment.

At SQL Saturday 3/29 in Boston, our team will present  about creating a container and running SSIS unit tests with tSQLt.  We want to have as much "off the shelf" (Azure Container Instance, Azure DevOps, an official version of tSQLt, AdventureWorks database, etc.) as possible.  Even being able to say that we're working with you to incorporate our changes would be something.

While having the code incorporated by our presentation is a strong desire, our longer term SSIS development strategy requires us to run unit tests, and we love the clarity and organization that tSQLt provides.  How can we help you to help us?  What do we need to do to fast-track your confidence in these changes?

Thanks in advance.

[Sebastian Meine]

Larry, Rong,

I'll reach out to you directly.

Thanks,

Sebastian

[Jay Really]

Hi all

I'm most interested in this topics, would like to use tSQLt in this way as well.

What's the latest ? Happy to contribute and help test if needed.

Op woensdag 13 maart 2019 20:39:50 UTC+1 schreef Sebastian Meine:

To unsubscribe from this group and stop receiving emails from it, send an email to ts...@googlegroups.com.

[Rong Chen]

Hi Sebastian:

We have completed all the tests in repository https://github.com/tSQLt-org/tSQLt/tree/master/Tests in the “NoTransaction” mode.  Most of the test are successful, however in some tests we have encountered situations as below and we have to add the logic in the clean-up code to handle them.

1.      -  User tables/schemas are created within the test procedures

2.      -  Other user table related objects such as constraints, triggers, foreign keys, user defined data types are created within the test procedures.

 

Do you think the work to clean up the user created objects are necessary, or just nice to have, since these objects can alternatively be dropped explicitly by including the drop statements at the end of the test stored procedures.

 

Please let us know you thought and suggestions.

 

Many thanks!

Rong

[Paweł Pilch]

Hi Rong Chen,

I'm very curious if you are still developing/using this extension and if the version attached above on 19th June 2018 is the latest one? If not - would you mind sharing the latest one please?

I am thinking about using it to test my SSIS packages.

Thanks,

Pawel

[Rong Chen]

Hi Pawel:

2018 is the version we currently use for our SSIS tSQLt testing.  We have a new version in 2019 but we have not fully tested it and we currently do not use that version in our CICD process.  Please let me know if you are interested in the 2019 version.

Rong

[Paweł Pilch]

Hi Rong,

Many thanks for your answer.

Yes, I am interested in the 2019 version in that case.

I analysed and tested a little bit the 2018 version so it would be very helpful if you could let me know what the main differences are (in general) comparing to 2018.

I will also appreciate highlighting areas which are identified by you as not working well yet (if there are any spotted :) ).

I also understand that, because of the fact that SSIS tests with this extension do not run in isolation (transaction), you have a dedicated server for them to ensure isolation? Or do you have another solution for it?

Many thanks,

Pawel

[Liam Johannes]

Is there any plan on making this official? It would be great to have the capability to run with no transaction in general.