64-bit SQL Server 2005 SSIS and UNC paths, Part 1

"You can't get there from here."

Part 1

As part of a recent client project, I had to use SSIS to create six pipe-delimited flat files for consumption by a third-party report writer. I created a template container with all the tasks necessary for the conversion. The template included variables to hold the path and file name for the destination files, and I used an expression in the connection string for each destination Connection Manager to reference the variables.

System background:

• The SQL Server data mart resides on a remote server.
• Access to the file system on the server is not allowed.
• I do SSIS development on a virtual server with only the client tools installed.

During testing, I used a local drive path on the development machine (E:\data) and the package executed in debug mode with no problem. The final destination for the files was on a third machine, so I had to use UNC paths for access. After I changed the variables to UNC paths, I got the following error when executing in debug mode:

Error: 2009-08-31 15:20:49.96 Code: 0xC020200E Source: <task name> <destination adapter> [346] Description: Cannot open the datafile

I could open the Connection Manager for the files defined by UNC pathnames inside of the BIDS designer, but the execution of the package failed because it couldn't open the file.

Hmmmm. Very suspicious.

I checked the SQL Server version on the remote server. Sure enough, it was the 64-bit version. I set the Run64BitRuntime property for the project to False and the package executed successfully from the BIDS designer.

Looks like UNC paths on flat files are another point of conflict between 64-bit and 32-bit SQL Server. Since the BIDS designer is a 32-bit application, I could open the files from inside the designer. Executing the package, however, invokes the 64-bit executable, unless BIDS is forced to use the 32-bit executable with the above setting.

Of course, then I had to try to schedule the thing….(to be continued)