SQL Server Performance Forum – Threads Archive
Calling a DTS package from within a SP
Hi,I have a DTS called dt_VTSImportTextFiles. It is supposed to read two text files and import to two tables. When I run it from within DTS design, it works properly and I have those data in my tables. When I run the following command from Query Analyzer: exec master.dbo.xp_cmdshell ‘dtsrun /S 100.90.80.70 /E /N dt_VTSImportTextFiles’ which, assume 100.90.80.70 is my server’s IP address. I get the following answer: DTSRun: Loading…
DTSRun: Executing…
DTSRun OnStart: Copy Data from STS to [DBTEST].[dbo].[STS] Step
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: Copy Data from STS to [DBTEST].[dbo].[STS] Step, 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: Copy Data from STS to [DBTEST].[dbo].[STS] Step
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. What’s wrong with my command line? Where is the DTSFFile.hlp? Is it containing some information about my problem? What should I do to be able to call the DTS from within a stored procedure?
I appreciate all your replies.
Thanks,
Farhad
Error opening datafile: The system cannot find the path specified.
You should specify valid path for the DTS package to work. The method to call DTS from SP is either xp_cmdshell with dtsrun, or the sp_OAmethod family to do it using automation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Satya, But how can I address my DTS package? Before its name? And where is it loacted that I give the correct path? Could you please give me an example? Thanks,
Farhad
quote:Originally posted by satya
Error opening datafile: The system cannot find the path specified.
You should specify valid path for the DTS package to work. The method to call DTS from SP is either xp_cmdshell with dtsrun, or the sp_OAmethod family to do it using automation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
You should specify valid path for the DTS package to work. The method to call DTS from SP is either xp_cmdshell with dtsrun, or the sp_OAmethod family to do it using automation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Also when you do the exec cmdshell, are you running this from your Local Computer or the Server where the DTS is? It could be a reason of not having required access on Server executing any DTS on the server from a remote computer will fail unless you right click on the DTS from EM and go Run. So to find out exactly where your problem is go to the Physical Server Computer and login as Admin and execute the package. Also enable DTS package logging by performing the following :
– On the Package menu, click Properties to display the DTS Package Properties dialog box.
– Save package logs to SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Yes, You are right. I run the DTS package from my local computer and it works. Then I noticed that the owner of DTS package is my name, on the server, which I have no right on that server. So, when I saved the DTS on server and changed the owner, it worked! Is it the way that I have to do? I have to move the DTS from my local computer to the remote server then ask someone on that server save the DTS under another name and this way change the ownership of DTS. It seems an ugly way. Isn’t a better way? Thanks,
Farhad
quote:Originally posted by satya
Also when you do the exec cmdshell, are you running this from your Local Computer or the Server where the DTS is?
It could be a reason of not having required access on Server executing any DTS on the server from a remote computer will fail unless you right click on the DTS from EM and go Run.
So to find out exactly where your problem is go to the Physical Server Computer and login as Admin and execute the package.
Also enable DTS package logging by performing the following :
– On the Package menu, click Properties to display the DTS Package Properties dialog box.
– Save package logs to SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
– On the Package menu, click Properties to display the DTS Package Properties dialog box.
– Save package logs to SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I suggest you to refer to books online and choose topic DTS to know more about this information, and for reference review thishttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 KBA to schedule DTS as a job and other concerns.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi again,<br /><br />I was unable to solve the problem. But I did a lot of envastigation and now I hope I provide more information to get help from all you guys.<br /><br />There is one server which the SQL2000 is installed on it. And there is another server which contains a directory with some text files.<br /><br />I have a DTS package which is supposed to read a text file from the second server and import into a table in SQL2000.<br /><br />When I login on first server and MAP to the second server, so I can see the text file, I am able to run the DTS package from Eterprise Manager and it works perfectly. <br /><br />Keeping same situation, if I run the following command from Query Analyser to execute the DTS package, I get error. <br /><font color="red">exec master.dbo.xp_cmdshell ‘dtsrun /S 100.90.80.70 /E /N dts_ImportTextFile'</font id="red"><br />100.90.80.70 is my server’s IP address. <br /><br />It can not find the text file on the second server while I can see the MAPped drive and the text file. It seems that when I connect to SQLServer using Query Analyser and it shells to command prompt, it is not able to see the path to the second server. While when I go to command prompt, I can switch to the MAPped drive and view/read the text file.<br /><br />Where is the problem? Do you think this is not a SQL issue but is a Windows issue? <br /><br />Waiting for your replies.<br />Poor DBA [V][<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />
In the DTS package ensure you’re mentioning the file path using UNC such as \servernamesharepathfilename etc. Ensure the SQLAgent service has required privilege to access these files on the second server, more or less this related to the windows issues on the terms of privileges to the logins. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Satya, I tried UNC approach and it works. As long as I am logged in to the second server, the DTS package works perfectly. Thank you. But I don’t undrestand why when I am using drive mapping (i.e., S: drive) and I run the DTS from within the DTS Designer it works but I am not able to run the DTS from Query Analyser (As I mentioned in my previous message). FYI, The DTS first truncates a table and then imports text file into that table. Phase 1, which is table truncatation is down perfectly but phase 2 gets the path not found error. Regards,
Farhad
quote:Originally posted by satya
In the DTS package ensure you’re mentioning the file path using UNC such as \servernamesharepathfilename etc.
Ensure the SQLAgent service has required privilege to access these files on the second server, more or less this related to the windows issues on the terms of privileges to the logins.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What kind of authentication used by SQL?
Is you login is a part of SYSADMIN group?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi, Thank you Satya. I could solve the problem. I asked server’s administrator to login on the server itself and then map to the path. Then lock the monitor and keyboard and leave the server in that situation. I am able to call the stored procedure which calls the DTS package from any workstation and it works perfectly. The problem now is that if they boot the server, then someone must login the server (to do mapping) and lock the monitor. If they forget to do login, I’ll lose the mapping and DTS package will not work. How is it possible to automate the mapping in windows 2000? I mean, whenever server boots, automatically map to the path or login and map then lock the keyboard and monitor? I appreciate all you guys in advance,
Farhad
Glad you could find some resolution to the issue, also refer to my comments in this threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3804 about using drive letters and UNC. I don’t think there is any utility/chance to map the drive when the server boots, until unless a login is allowed to logon to the server and you can set the ‘reconnect at logon’ during a drive mapping. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi,
I have a DTS called cbu_importa. When I run the following command from Query Analyzer: execute master.dbo.xp_cmdshell ‘dtsrun /S SERVERN_UNO /E /N cbu_importa’ I get this answer: DTSRun: Loading…
DTSRun: Executing…
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217887 (80040E21)
Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0)
Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: Crear tabla [CBU].[dbo].[lista] Paso
DTSRun OnError: Crear tabla [CBU].[dbo].[lista] Paso, Error = -2147217887 (80040E21) Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0)
Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0 DTSRun OnFinish: Crear tabla [CBU].[dbo].[lista] Paso
DTSRun OnStart: Copiar datos de lista a [CBU].[dbo].[lista] Paso
DTSRun OnError: Copiar datos de lista a [CBU].[dbo].[lista] Paso, Error = -2147217887 (80040E21) Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0)
Error string: Errors occurred
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: Copiar datos de lista a [CBU].[dbo].[lista] Paso
DTSRun: Package execution complete. Can it be a security/permission problem? Thanks in advance. gonsa
WHat is the privilege of account used to execute DTS?
Enable DTS package logging in order to see the steps execution. Error logging via the Logging tab of the DTS Package Properties. This logging captures all DTS activities at a physical level, reporting runtime errors, # of rows updated, and tasks started and completed.
Making use of the "On Failure" workflow within your package. This technique is best for capturing "Logical" errors. For example, you could use ActiveX to force an "On Failure" event if a table’s data didn’t meet a certain business condition.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>