I've been dabbling with SQL 2005 quite a lot these days, and two things really impress me : SSIS and Reporting Services.

However, it seems that it might be a bit difficult to actually deploy a SSIS.

I'm missing something with calling a web service in SSIS... It seems that even when you change the connection manager setting for the HTTP Connection manager to a new value (say we deploy to a live environment), it still reads the WSDL file you provided and calls the wrong webservice (Ethereal is the coolest network tracing tool ever btw.).

And Deployment!

There doesn't seem to be a way (enlighten me if you know a way to do this) to specify a username and a password to your HTTP connection manager at any time but design time - i.e. it's saved with your SSIS package.

Now comes the problem : by default SSIS encrypts sensitive data like usernames and passwords in the xml file. By default the encryption type is set to "Encrypt With User Key", which basically means that the file is encrypted with your username token - no other user can run your package.

Set this to "Encrypt Sensitive With Password", deploy and run it - it works! Now try to schedule the SSIS package with a SQL Agent Job - it fails. Try to run it manually with dtsexec: it fails, stating that it can't decrypt the data even though you've specified the password for the package (Side Track : No useful information comes out of running a SSIS package manually. If you want to know what actually went wrong, use dtsexec).

One final try : "SQL Storage" for the package, which leaves in unencrypted and depends on the datebase security to secure sensitive information. It seems that there is no actual way to deploy a package to SQL Storage (unenecrypted) from Visual Studio (again, enlighten me if you know how). Instead, open SQL Server Management Studio and find the "Save a copy" option under the file menu - there you can select a SQL server to deploy to.

Otherwise than the deployment issues, great work done by Microsoft on SSIS and Reporting Services!