by Ellen
11. September 2009 22:58
"You can't get there from here."
Part 2
In Part 1 of my post on this topic, I described how to configure a package containing UNC paths on flat file destinations to execute successfully in BIDS debug mode.
At the risk of being repetitive, here's the system background:
- The SQL Server data mart resides on a remote server running 64-bit SQL Server 2005 Enterprise Edition.
- Access to the file system on the server is not allowed.
- SSIS development occurs on a virtual server with only the SQL Server client tools installed.
- The destination for the output files is on yet a third machine, only accessible to the server and the development machine via UNC paths.
Now that I had successfully executed the package in debug mode by forcing BIDS to use the 32-bit runtime to recognize the UNC paths, I was ready to try to schedule the package via a SQL Agent job.
I'm used to using the CmdExec step type in SQL Agent to force execution of a package in 32-bit mode (I have to do this any time the package includes Excel file sources, for instance). However, when I used the usual method for this particular package, I got yet another error message:
Error: 2009-08-31 16:01:55.99 Code: 0xC001401E Source: stage_reports Connection manager "Extract File Cards" Description: The file name "\\target1\Dest-Data\Cards.csv" specified in the connection was not valid.
Well, rats.
Since I wasn't able to access the file system or view the context of the server in the network, I had to find a way to work around this - i.e., schedule the package in 32-bit mode without using SQL Agent.
To do this, I wrote a batch file to execute the package using 32-bit dtexec from the context of the development machine (I used dtexecui to get the command line for the package):
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "\\vm-dev1\ReportStaging\SSIS\report_staging\stage_reports.dtsx"
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
By scheduling the batch file as a Windows task and bypassing SQL Agent entirely, I was finally able to get the UNC paths to work on the calling machine, the server and the destination machine.
Whew!
Subscribe