July 16, 2003 at 3:56 pm
When I run DTS package directly on Enterprise manager it is running fine but when I run through Query Analyzer using this command
master..xp_cmdshell 'DTSRun /S "Grantsql2" /U "sa" /P "" /N "ASAP"'
I get following error.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 3 (3); Provider Error: 3 (3)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL
Thanks for your help
July 16, 2003 at 4:35 pm
quote:
When I run DTS package directly on Enterprise manager it is running fine
The catch here is the DTS package is actually executing from your workstation. It's not the same as running through xp_cmdshell, which runs on the server. SQL Agent would run on the server.
Do you have a mapped drive locally where the flat file sits?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 17, 2003 at 11:11 am
Thanks for your reply
I mapped the network drive and still getting an error.
Lets say that I have two sqlserver. SQL1 and SQL2.
SQL1 server is my local machine and I registered SQL2 SQL server on SQL1 sql server. I developed a DTS package on SQL2 server which is working fine if I run directily through Enterprise manager. the converted text file is getting stored on my local machine but when I run through Query Analyzer I still get an error.
On SQL2 server I mapped my local machine folder but did not help to solve the error.
On SQL1 server I also mapped SQL2 Server folder but did not help to solve the error
July 17, 2003 at 11:22 am
Keep in mind that mapped drives are only available when you're logged in and even in that case, only if you are actively running the program from the console (you're sitting in front of the server and executing it while logged in).
Mapped drives aren't present for non-interactive processes. If you refer to the UNC instead of a drive letter, however, you should be okay. For instance, instead of F:\mydir\mytextfile.txt you say \\MyComputer\MyDirShare\mytextfile.txt and things will probably work.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply