Wednesday, November 14, 2007

Precompile, Recompile, Decompile

As I mentioned in a post yesterday (or the day before... it's been a long week already) I've been spending a lot of time lately in the Script Task in SSIS. Well, I have the task logic implemented and tested, so now it's time to copy that task into all of the packages that need it.[1] So I now have 20 or so packages with the same script in their OnPreExecute event handler, and everything runs great.

Until you look at the package output. During testing I generally pipe DTEXEC's standard output to a text file and use that as my "to do list", and with these updated packages I have a 1500+ line file filled with this warning, over and over:
Warning: 2007-11-14 13:53:07.67
Code: 0x00000003
Source: Script Task Name Here
Description: Precompiled script failed to load. Attempting to recompile.
For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).
End Warning

Not a bad warning, all things considered, and it even has a URL - cool!

Of course, the information in the KB article in question doesn't apply to my scenario, as I'm on a 32-bit machine and am fully patched with SP2 + hotfixes. Hmmm... Time to do some searching...

Well, I'm not alone: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2030858&SiteID=17

And people have been having this problem with Script tasks in packages created from template packages: http://bobp1339.blogspot.com/2007_09_01_archive.html

A-ha!

I copied and pasted each of these script tasks from the package in which I performed the initial development and testing. Not quite a template package in this case, but it's close. So...
For Each Package in AffectedProjects.Packages
Package.Open
Package.EventHandlers.Select
Right-click(ScriptTask)
KeyPress(e)
Ctrl-Tab
Alt-S
Wait(5)
Alt-F4
Ctrl-S
Ctrl-F4
Next

The good news: it works! The warnings are gone!

The bad news: that was MScript - the Matthew Script Language, which performs horribly and does not scale, because it relies on my poor typing skills.

Maybe some time I'll find time to figure out how to automate this in the SSIS .NET API and add it to PacMan. Maybe some time I'll find time to sleep too...

[1] Yes, I know about how custom SSIS components have a much better reuse story, but for now it's more important for me to keep my deployment as simple as possible, but thank you for caring.

6 comments:

Unknown said...

Matthew,

You may check CozyRoc Script Task Plus. One of the enhancements you will see there is the ability to extract SSIS script and then reference the script from other packages. The script code becomes separate file entity, which can be stored in your SCC and maintained independently from your packages. If you later modify the script, all other packages, which reference the script will automatically start using the newer version. There is one more cool feature implemented in the CozyRoc Script Task Plus and it is the ability to implement your own scripts UI. You may check http://www.cozyroc.com/scripts for sample scripts with UI.

Matthew Roche said...

Thanks, Ivan. I have looked at the CozyRoc components (and if you look, I've even blogged about them ;-) but I don't think that this is the solution I'm looking for here. I could easily build a custom component that does what I need (and yet may if I have to) but I want to keep my deployment story as simple as possible. Adding any component to the deployment is going to immediately make it more than just an "xcopy deployment" and I'm trying to avoid that as long as I can.

Unknown said...

Thanks for blogging about it Matthew. I thought it was worth mentioning because the problem you're dealing with is resolved in elegant way by the Script Task Plus. Sometimes I'm puzzled if you "the advanced SSIS guys" cannot realize what is the value, what is the point then? Isn't the goal to save time, hassle and be more efficient? This solution is in fact built for people like you. We can both agree, the copy-and-paste is the worst reusability of all. The only reason I could think of not using the CozyRoc solution is the additional cost, which is small. You get much better reusability and script code tracking independent from the package. You also avoid the hassle of building a custom component, dealing with the SSIS internals and resolving deployment issues. After you install SSIS+, your SSIS scripts can be in fact "xcopy deployed". And on top of everything else you can build your own UI for your SSIS scripts, right inside the script. What could be better?

Matthew Roche said...

With all due respect Ivan, I think that I know my own requirements a little better than most people. I don't want to downplay the value of your components - I see real value in what you're doing. But:

1) There is no hassle *for me* in building a custom SSIS component. In this context I could have it done in 2-3 minutes if this was the right direction to move in.

2) My SSIS solution is not going to be deployed only once. It's going to be deployed to an ever-increasing number of servers at an ever-increasing number of sites over time. This means that *any* custom component will kill the "xcopy deployment" story not only once, but on an ongoing basis. This is what I'm attempting to avoid. The annoyance I'm experiencing today during development is a minor thing - the additional complexity for ongoing deployment is the real cost that I need to avoid.

So... I appreciate both your comments and the features that your components deliver, but I am also very confident that this component is not the correct solution for my scenario today.

Unknown said...

Matthew, I'm sorry if my comments have been inappropriate for your scenario. I'm still interested to find out more about your solution being built entirely with standard SSIS components if it is not top-secret ;)

Matthew Roche said...

The solution is essentially a "standard" data warehouse solution, drawing in data from many disparate sources for comprehensive analysis in the warehouse - it's the data sources that make it interesting, not the technology.

Currently 100% of the data sources I'm using are based in relational databases (SQL Server, Oracle, etc.) so between the power of the source database and the built-in components in SSIS, there has been very little that I could not easily do "out of the box." There are one or two places where I'm using the Script Component for custom data flow processing, and now this current need for the Script task, but for 99% of what I need, the SSIS platform delivers.

I'll try to avoid the pitfall of making broad generalizations, but from what I see at my training events, users groups, seminars and online communities such as the MSDN Forums site, many people underestimate the power of the built-in SSIS tasks and transformations. They look to scripting or 3rd party components when the creative application of built-in components would solve the problem, sometimes more simply. I think the underlying issue here is that the built-in components (especially with the data flow transforms) are very granular building blocks, so you sometimes need to use two or three of them to solve "one problem" and it's not always obvious what will work best and what will work less well.

Please also keep in mind that I'm making an very deliberate and concerted effort to avoid any 3rd party or custom components. There are situations where my life would have been easier if I had a tool like your Script Plus Task, but based on the priorities and trade-offs in my project, it doesn't make sense. Not now, anyway...