Integration Services - Powerful and painful

In my opinion, one of the more idiotic things about SQL Server 2005 is Integration Services. Not the product itself, which is actually quite powerful, and since I would not touch DTS with a ten-foot pole picking it up was relatively easy for me. It is fun to work with, didn't crash on me a whole lot (unlike Visual Studio 2005 which is like watching Houdini, since it disappears randomly) and was fairly intuitive once I got the basics down.

What stinks is the methods of execution of these packages in a production environment.

Two main problems for me are package security and remote execution.

1. Password Encryption: What's with all of the options for package security? User Keys, Certificates, Package Passwords. How about abstracting this stuff right out of the package itself and into another tool, like a code obfuscator. It's wonderful to have, but sometimes, like a door, you don't always need a lock on it. Why do I have to type in a package password for EVERY PACKAGE everytime I hit Build? That's like typing in a password for every file when I type DIR in DOS. Also, what's with passing the /DECRYPT password in every time I have to run from a command-line? Can't we stick this in an encrypted config file somewhere?

2. Remote Execution - This is where I got burned. SQL Agent is the recommended means of executing packages on the SQL Server from a remote PC. So how do you pass in passwords and other parameters to the command line? I ended up creating two command-line tasks with sqlcmd, one to edit the job task step sp_update_job_step and another to run the job sp_start_job. Of course, when you run sp_start_job it immediately comes back. This could cause a problem for jobs that have to wait for others to run before starting, so you need to grab another custom script that checks every 10 seconds to see if it's running.

Other things that bug me about SSIS - not knocking the developers who put tons of effort into this - but just stuff that bugs me.

- The 5-10 second delay executing packages from command line.
- Clicking the play button in Visual Studio doesn't play the package selected. It could do many things, depending on what mood it's in.
- The script editor. Sorta reminds me of Excel VBA but without the ability to tab back and forth between the package and the script.
- Passing parameters from a command line. %8 %9 %10 %11 just doesn't cut it for me.
- No encrypted parameters in config files.

Deployment question: SQLDB - SSIS on different boxes with SQL Agent for scheduling - MSDN Forums: "SQL Agent is my preferred scheduler, and maybe yours too, but many organisations have their own standards. Many houses use IBM/Tivoli for example, and SQLAgent is not required for SSIS. If you would prefer to use it, then yes you are stuck. I think buyng SQL Server for SQLAgent is an overkill, but if you look at that SQL Instance in the same way as you look at MSDE, then it is no big deal. There are plenty of proucts our there that install DB engines. I saw a report that said Informix (I think that was the one) was one of the most widely deployed DBs, and that is because CA shoved it under the covers of ArcServe backup, probably for the scheduling :) "

Yes, my client does prefer to use a Unix-based scheduler, and apparently this means we're stuck using SQL Agent, called from another scheduler.