Oren,

Are you using package configurations yet?

I had similar issues when deploying my packages to production. I solved them by enabling package configurations and putting then connection string into an xml config file, <package name>.dtsConfig.

When testing locally, the dtsConfig file points to my local installation of SQL Server.

After I am done testing, I double-click a batch file with a Robocopy command which copies the package to a separate Production folder while excluding the dtsConfig file. The separate Production folder has its own dtsConfig which points to the production SQL Server.

Now in this folder I can double-click the package, select the production dtsConfig file, and run it using DTSExecUI. It runs outside VS 2005, so you don't get the pretty green, yellow and red boxes, but you only need those while testing, right? :) Plus, DTSExecUI probably uses less memory than when it is running in debug mode in VS 2005.

Anyways, I've used Brail on several Monorail projects, so I hope that helps you out.

• Brandon

Yes, I had the same issues when dealing with SSIS. The config mgmt is quite a PITA, and I never managed to get it to the point where it was very seamless.

BIDS doesn't cache db connections -- it just applies either (1) the hardcoded connection info in the data sources or db conn managers or (2) loads the conn info from a variable/config file. Your best bet is to export all conn managers (if you use File Conns, export them as well) to the config file, and modify the conn string there. In order for this to work, though, BIDS (or dtexec.exe) will need to be able to find your config file at runtime, or else it uses the default conn string.

Also, if you are exporting conn strings to a config file (or some other store) and you have "Don't Save Sensitive" set on the package, then the password will not be exported -- you'll need to add it manually after the conn string is exported.

By the way, SSIS is pretty extensible, so you can always roll your own components/tasks (and even provide nice designers for them)! :) (hey, you did say you wanted to write code didn't you? :)).

I usually used dtexec.exe to run packages that were nontrivial since it runs much faster than BIDS (less VS overhead, obviously).

I think MS took the right step with SSIS (the fact that it's a .NET library with extension points is the biggest plus, in my opinion), but it being v1.0, there're quite a few unpleasant quirks with it.

In this specific case, if you were to roll your own impl, then you'd still need to dynamically set the conn string, and that info would need to be stored somewhere (i.e. config file). In other words, you'd be doing the same thing as SSIS is doing for you.

Mind you, I agree that SSIS is a bit funky, esp when you initially start working with it, but I did find that it solves simple cases pretty well once you get the "hang of it". You're just paying your dues! :)