Thursday, December 6, 2012

SSIS to QlikView task execution

QlikView is a BI visualization tool that has been in my tool belt for some time, and I use it as part of comprehensive BI solutions. SQLServer Integration Services is another important piece of the solution, and, in addition to the obvious ETL and data store functions, I tend to use it to orchestrate the actions of the system. Therefore, it is necessary to call QlikView functionality from within SSIS packages.
In QlikView version 11, the way to do this is through EDX (Event Driven Execution). The nice thing about this mechanism is that the API is exposed via SOAP over HTTP. It becomes easy to build a little .Net app to call the services associated to internal QlikView tasks.
If you are not a .Net developer, QlikTech’s Arthur Lee created a very nice article and .Net console application that you can use. Check out the article.

If you didn't recognize it right away, one of the benefits of having the API as a service is that you don't have to deal with the hassle of trying to execute a command-line call or batch file on a remote server. It's not as simple as running the thing because you'd be running in the calling box, not on the remote server.

It sounds very silly but I wanted to remind you about something that can save someone a lot of aggravation. When setting up your EDX triggers from within QlikView make sure there are no spaces in between words in the task names as well as no spaces at the end of the task names. Yeah… ask me how I know that.

Happy ee-tee-elling.

Tuesday, December 4, 2012

SSIS, I love you but I hate you so much

It got me again, during development I used a mapped drive throughout my project and everything works perfectly. To see those green boxes fill your screen relaxes you like nothing else. I mean, not really but it feels good like nothing else. Ok, not true, but back to the point...
Deployed the package, run it through a SQL Agent job and kaboooom. Error. Spit out the gum, straighten the back, pull the chair into the desk a little more...
After hours of agony I remembered that you should never use mapped drives with SSIS/SQL jobs, and pretty much anything else.
After using UNC paths to network shares everything is back to green.

Take away: D O    N O T    U S E    M A P P E D    D R I V E S    I N    S S I S. They don't mix well, just like oil and water...