I’ve been dabbling with SQL 2005 a lot these days and two features impress me: SSIS and Reporting Services.

You might find it difficult to actually deploy a SSIS package.

I’m missing something with calling a web service in SSIS… 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 web service (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. The encryption type defaults to “Encrypt With User Key” which means that SSIS encrypts the file 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 database security to secure sensitive information. You cannot at present deploy a package to SQL Storage (unencrypted) 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!

Photo by Dominik Scythe on Unsplash