SSIS Unit Test Automation in C# does not work for SQL Execute Task



I've installed the ssis unit test and it working fine from UI and also tried to automate it using the example given here and it's producing the accurate result. But it fails every time when there is SQL Execute Task in out dtx package And for other tasks it's working fine and Assert count returning the expected value.

But if we've SQL Execute Task in our .dtx package then automation does not work and it calculates the wrong assert count .

Can you please look into this issue.

PS : Currently I'm using SSIS 2012 and SSIS Unit 2012.



farhanali82 wrote Nov 27, 2014 at 3:05 PM

Yeah, same is the case with me. I tried the sample Product.dtsx package along with product.csv file to load. When that .ssisunit file is executed from UI, it gives positive results, however, when executed through .NET Test project (by writing test method), it fails tests (Product, Create table, Truncate table).

The common thing is "Execute SQL task", which .ssisunit seems do not execute successfully through .net project. I tried creating new .dtsx package having just 2 simple tasks 1)Execute-SQL-Task 2) Data-Flow-Task.... Found same problem again, where Execute-SQL-Task did not passed test result. Please note that I did asserts with different ways (SQLCommand / VariableCommand), but both failed.

I'd request @jhonwelch to check and rectify the issue as soon as practicable. Appreciate hardwork by him for this opensource framework.

wrote Nov 27, 2014 at 3:06 PM

Shakir wrote Nov 27, 2014 at 3:11 PM

Jhon please help, same issue with me

johnwelch wrote Dec 1, 2014 at 4:57 PM

Unfortunately, I don't think this is an issue in the ssisUnit code. It sounds like a marshaling problem with the Execute SQL task when it crosses app domain boundaries, which I encountered when I was first building ssisUnit. It causes an exception inside the SSIS components which prevents the test from completing, I reported it to Microsoft quite some time ago, but I don't think they are going to address it.

I'd guess you are using MSTest to drive your test harness. You might want to try NUnit, as I don't think it exhibits the same problem (but honestly I haven't tested that recently). Also, it seems to go away if you don't run the unit tests in debug mode.

If you are running into a different issue, or I'm not understanding the problem, then I'd ask that you attach a sample package, unit test, and test code that illustrates the problem to this issue so that I can reproduce and troubleshoot it.

hafizmsuleman wrote Dec 2, 2014 at 2:36 PM

Hi John,

Thanks for your reply. Yes I'm using MS Unit Test Project with visual studio 2012 and having this issue but later when I tired visual studio 2013 it works fine for me. Also , I tried in N-Unit Project and it was successful.

Hafiz Suleman