SQL Server Home
A SQL Agent SSIS jobstep accepts, but does not use, a configuration file UNC path
6/8/2009 10:27:37 AM
User(s) can reproduce this bug
When creating a SQL Agent step using the SSIS subsystem, adding a configuration entry with an UNC path is successful. Since adding an invalid path is impossible and raises an error, the user is lead to believe that path validation is occuring at configuration time and so a correct (and accepted) file UNC is a valid configuration.
At run time, however, behavior is far from expected. You must manually change the /REPORTING E parm to a V(erbose) to see the confusing runtime issues.
First, the job step runs and completes if the configuration settings in the original package are usable, but data may be coming from unexpected (e.g., develpment environment) sources without warning. Closer inspection of the resulting logs reveals the following:
1) DTExec ignores the UNC path entry, but it's presence in the configuration causes the attempt to use either the default server config path OR the config path used in the develpment environment if configured.
2) Even though the configured UNC file is not used AND the attempted config file is not present, the package raises no warnings or errors and continues to run using it's original configuration when deployed from BIDS.
SQL Server 2005 SP3
Tools (SSMS, Agent, Profiler, etc.)
Win2003 Standard Server (SP2)
Operating System Language
Steps to Reproduce
using SSMS, create a SQL agent jobstep using the SSIS step type using a valid package in any location.
On the configurations tab, click Add. The dialog presents only local path selections from the server.
Paste in an incorrect UNC path and click OK. This raises an error indicating the file location could not be resolved.
Paste in a valid UNC path and it is accepted, indicating to the user the file path WILL BE USED.
On the command line tab, change to read /REPORTING V to log config file usage.
Save and run the package.
The job step output logs clearly indicate via Info and Warning entries that the file that is attempted to be used is NOT the UNC path, but either the default package location on the server or a config path on the development machine used to build and deploy the package.
The preferable result would be to simply use UNC paths successfully. (however, I've seen such a request denied in the past)
The next best reslut would be for an UNC path to be refused during configuration, even if it is valid. (since it won't be used anyway)
The minimal expected result would be to throw an ERROR for any unavailable configured config files so the user is not left wondering WHERE the settings are comming from.
to post a comment.
Please enter a comment.
on 9/4/2009 at 10:52 AM
Thanks again for reporting this issue. This was fixed in our latest sources.
on 6/15/2009 at 1:16 PM
on 6/12/2009 at 11:30 AM
Hi - what version of SQL Server are you using ? This problem should be fix in CU for SQL 2005 sp2
to post a workaround.
Please enter a workaround.
© 2013 Microsoft