Kirk Haselden

Script task bug, nit and trick

In beta 2 and IDW 11 there is a bug in the script task that causes some odd behavior after so many iterations inside a for loop. Some of you have already experienced this. Essentially, the script task stops with an error like:

Error: There was an error while compiling the script: -2146226146

What's happening under the hood is the script task relies on a product called VSA or Visual Studio for Applications. The VSA engine uses a COM object and the shell and some other components are managed. When the script task gets put into a tight loop, the COM object gets created repeatedly but not released because managed code doesn't free the object quick enough and a system resource gets starved. (Garbage collection)

That's the bug. In IDW 13 there is a fix for this and you will no longer have to deal with the issue.

However, there's a nit that's worth mentioning with the script task. It's pretty fast once it compiles the code, but if the script task is in a tight loop, say as in a ForLoop container, the compiling step takes a substantial amount of time slices and slows down the ForLoop. Normally, you wouldn't notice this, like say, if you had a script task in the package outside a loop etc. However, it's noticeably slow in a ForLoop.

That's the nit. In IDW 13 there is a fix for this and you will be able to speed up the script task substantially.

Sergei, our wunderkind developer who wrote the task has added a property called PreCompile to the script task. If you set that property to true, it will precompile the code and save it as part of the package. The advantage is that the script task will run, in some scenarios, 10 times faster. Or, at least a tight loop with a script task in it as I've attached here. This package running on my machine took ~98 seconds to run. With precompile turned on, it took ~10 seconds.

The disadvantage is that it make the package larger. The attached package is roughly 73K, so I wouldn't recommend using this feature if you don't need to speed up a tight loop with a script task in it.

That's the trick. When you get IDW 13, give this a try. It's amazing how much faster it runs.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden