Somebody asked me yesterday if I'd been updating this recently and of course I haven't as I'm a slacker so I thought I start the process off again by posting about one of my fave subjects and that's that's the continued battle 32bit / 64bit mixed environments.
I'm currently working on a real life SQL Server 2008 project with all the bells and whistles and everything so we have VS 2008 in the mix as well. Now let me set the stage....
Dev server = 64 bit
SQL Server = 64 bit
Source Database = doesn't matter (I think)
Now I'm going through a firewall so I've had to setup an alias in the configuration manager so I can specify the port of the instance of SQL server that is my source database. In there you have 32 bit and 64 bit configurations so because I'm setting everything up to make sure I can talk with the server I've gone through management studio.
SQL Server Management Studio connects fine as I'm guessing it's a 32bit process and I've set up an alias in the 32bit bucket. I can also create a SSIS package that connects to my destination server, is able to browse the source data, and will execute from the dtexecui application. But what can't I do, well for starters I can't debug as I get an error telling me that it cannot connect to the source database.
Now I create an alias in the 64bit configuration bucket and now I can debug. So what else can I do? Well next thing is to set the project property Run64bitRuntime to false and try to debug with the 64bit alias deleted. Guess what, it runs.
So the moral of the story is don't expect everything to work nicely for you out of the box all of time even if you're in a single technology environment.