There are quite a few ways to perform unit testing for a SSIS package and also automate it.One such method is using the BizUnit Framework which is predominantly used for the Biz Unit testing.We can customize BizUnit to test SSIS Package as well.
We should write extension methods in BizUnit Framework to execute the SSIS package, query the database and compare the result with the expected result or even compare the output file with the expected output file.It might be a little extra effort for a SQL BI developer to do some c# coding but its an one time activity and definitely worth it.
Before going ahead let me create a simple package which would raise warnings in to system log table.
The package would have a script task like below
**Click on the Image for a clear view
**Click on the Image for a clear view
**Click on the Image for a clear view
Here using this package I am intending to check the warning message logged in the log table using the BizUnit.Alternatively you could also do a check on your output file or your destination table for the expected output.The warning message / error message related to the individual tasks can also be checked for negative testcase.
I have added the following to my existing solution
1)BizUnit.Extensions project which has the methods to compare the output with the expected output.
2)BizUnit.SsisSteps which has the methods to pass parameters and execute the SSIS packages.
3)SSIS.BizUnit.Test which has the test case xml and the test case method.
**Click on the Image for a clear view
After adding the Test project to the solution you can find the LocalTestRun.testrunconfig in the SolutionItems folder
**Click on the Image for a clear view
The SSIS package which needs to be tested and the folder where the input file/expected files (if required) should be added in the LocalTestRun.testrunconfig.
This is because while executing the Unit Testcase all the files associated with the test case will be copied to a test instance folder for execution.
My TestCase has the steps for the Unit Test execution defined in xml.Following image shows the steps in SSISPackageTest.xml
**Click on the Image for a clear view
The Test Setup is to delete the existing records in the System Log table . As you have noticed this implemented by calling the BizUnit.DatabaseDeleteStep extension .
The configurable values such as LogTable , Database connection are provided from Context in the Test method using the keyword "takeFromCtx"
Context is like an object which has all the configurable values required for the testmethod to execute .Each test method is associated with a Context where you can add new configurable values if required.
The TestExecutionStep executes the package using the extension assembly "BizUnit.SsisSteps.dll" and the method "BizUnit.SsisSteps.SsisPackageExecuterSteps"
As you could see I am comparing the message from the system log table with the expected error message in the Context.If they match then the Test case would pass.
My Test case method is shown below.
**Click on the Image for a clear view
The test methods are indicated by "[TestMethod]"
The test initialize method will be called during the start of the execution of the test methods.
The Context values respective to the test method are added with in the testmethod.The Context values which are common to all test methods are added in the test initialize.
A new instance of the BizUnit is created with the value SSISPackageTest.xml and the current context.
After creating the first Unit Test case you can view the unit test case in the TestListEditor.
**Click on the Image for a clear view
The result on execution of the test case is shown below.
**Click on the Image for a clear view
Similar to the example shown you could create several test cases for the following scenarios.
1)Testing the successful execution of the package
2)Comparing the expected warning message/error message in system log table by querying the db.
3)Comparing the expected output file with the expected output file.
4)Comparing the expected data in table by querying the db.
When the Unit test cases are moved to new environment only the Conext values should be changed in order to run the automated Test cases.
We should write extension methods in BizUnit Framework to execute the SSIS package, query the database and compare the result with the expected result or even compare the output file with the expected output file.It might be a little extra effort for a SQL BI developer to do some c# coding but its an one time activity and definitely worth it.
Before going ahead let me create a simple package which would raise warnings in to system log table.
The package would have a script task like below
**Click on the Image for a clear view
**Click on the Image for a clear view
**Click on the Image for a clear view
Here using this package I am intending to check the warning message logged in the log table using the BizUnit.Alternatively you could also do a check on your output file or your destination table for the expected output.The warning message / error message related to the individual tasks can also be checked for negative testcase.
I have added the following to my existing solution
1)BizUnit.Extensions project which has the methods to compare the output with the expected output.
2)BizUnit.SsisSteps which has the methods to pass parameters and execute the SSIS packages.
3)SSIS.BizUnit.Test which has the test case xml and the test case method.
**Click on the Image for a clear view
After adding the Test project to the solution you can find the LocalTestRun.testrunconfig in the SolutionItems folder
**Click on the Image for a clear view
The SSIS package which needs to be tested and the folder where the input file/expected files (if required) should be added in the LocalTestRun.testrunconfig.
This is because while executing the Unit Testcase all the files associated with the test case will be copied to a test instance folder for execution.
My TestCase has the steps for the Unit Test execution defined in xml.Following image shows the steps in SSISPackageTest.xml
**Click on the Image for a clear view
The Test Setup is to delete the existing records in the System Log table . As you have noticed this implemented by calling the BizUnit.DatabaseDeleteStep extension .
The configurable values such as LogTable , Database connection are provided from Context in the Test method using the keyword "takeFromCtx"
Context is like an object which has all the configurable values required for the testmethod to execute .Each test method is associated with a Context where you can add new configurable values if required.
The TestExecutionStep executes the package using the extension assembly "BizUnit.SsisSteps.dll" and the method "BizUnit.SsisSteps.SsisPackageExecuterSteps"
As you could see I am comparing the message from the system log table with the expected error message in the Context.If they match then the Test case would pass.
My Test case method is shown below.
**Click on the Image for a clear view
The test methods are indicated by "[TestMethod]"
The test initialize method will be called during the start of the execution of the test methods.
The Context values respective to the test method are added with in the testmethod.The Context values which are common to all test methods are added in the test initialize.
A new instance of the BizUnit is created with the value SSISPackageTest.xml and the current context.
After creating the first Unit Test case you can view the unit test case in the TestListEditor.
**Click on the Image for a clear view
The result on execution of the test case is shown below.
**Click on the Image for a clear view
Similar to the example shown you could create several test cases for the following scenarios.
1)Testing the successful execution of the package
2)Comparing the expected warning message/error message in system log table by querying the db.
3)Comparing the expected output file with the expected output file.
4)Comparing the expected data in table by querying the db.
When the Unit test cases are moved to new environment only the Conext values should be changed in order to run the automated Test cases.
Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information. Devops Training in Chennai
ReplyDeleteDevops Training Institute in Chennai
Thanks for sharing this information admin, it helps me to learn new things. Continue sharing more like this.
ReplyDeleteDevOps certification Chennai
DevOps Training in Chennai
Best DevOps Training in Chennai
Robotics Process Automation Training in Chennai
Angularjs Training in Chennai
AWS Certification in Chennai
Blue Prism Training in Chennai
I am obliged to you for sharing this piece of information here and updating us with your resourceful guidance. Hope this might benefit many learners. Keep sharing this gainful articles and continue updating us.
ReplyDeleteWeb Designing Course in chennai
PHP Training in Chennai
web designing courses
web development courses
PHP Training Center in Chennai
PHP Institutes in Chennai
Interesting blog, it gives lots of information to me. Thanks for sharing such a nice blog.
ReplyDeleteEducation
Technology
Brilliant ideas that you have share with us.It is really help me lot and i hope it will help others also.update more different ideas with us.
ReplyDeletevmware Training in Guindy
vmware Training in Saidapet
best vmware training in bangalore
vmware certification bangalore
Your thoughts are really different and helpful for me.i really enjoy the juice of your blog.i eagerly waiting for your next updates.
ReplyDeleteSoftware Testing Course in Anna Nagar
Best Software Testing Training Institute in Anna nagar
Software Testing Courses in T nagar
Software Testing Training Institutes in T nagar
Software Testing Training Institutes in OMR
Software Testing Courses in OMR
Great Blog!!! Thanks for sharing with us... it is more useful for us..
ReplyDeletehadoop training in bangalore
big data courses in bangalore
hadoop training institutes in bangalore
Devops Training in Bangalore
Digital Marketing Courses in Bangalore
German Language Course in Madurai
Cloud Computing Courses in Coimbatore
Embedded course in Coimbatore
You are an artist. Your website is very unique. Your content is very interesting to read. Waiting to read more.
ReplyDeleteInformatica Training in Chennai
Informatica Training Center Chennai
Informatica Training in Porur
Informatica Training in Tambaram
IoT Training in Chennai
IoT Training
Xamarin Training in Chennai
Xamarin Course in Chennai
Great Work. Amazing way of writing things. Thanks for Posting.
ReplyDeleteIonic Training in Chennai
Ionic Course in Chennai
Ionic 2 Training
Ionic 2 Course
Ionic Training
Ionic Training in Porur
Ionic Training in OMR
Ionic Training in Anna Nagar
Ionic Training in T Nagar
Your car could be stolen if you don't keep this in mind!
ReplyDeleteConsider that your car was taken! When you visit the police, they inquire about a particular "VIN lookup"
Describe a VIN decoder.
Similar to a passport, the "VIN decoder" allows you to find out the date of the car's birth and the identity of its "parent"( manufacturing plant). You can also find out:
1.The type of engine
2.Automobile model
3.The DMV and the limitations it imposes
4.The number of drivers in this vehicle
You will be able to locate the car, and keeping in mind the code ensures your safety. The code can be examined in the online database. The VIN is situated on various parts of the car to make it harder for thieves to steal, such as the first person's seat on the floor, the frame (often in trucks and SUVs), the spar, and other areas.
What happens if the VIN is harmed on purpose?
There are numerous circumstances that can result in VIN damage, but failing to have one will have unpleasant repercussions because it is illegal to intentionally harm a VIN in order to avoid going to jail or calling the police. You could receive a fine of up to 80,000 rubles and spend two years in jail. You might be stopped on the road by a teacher.
Conclusion.
The VIN decoder may help to save your car from theft. But where can you check the car reality? This is why we exist– VIN decoders!