Running DTS from another computer in command line
-
So let's try this again, hopefully I'll remember to copy my post incase it decides not to go through again.
I have this .bat file which is running a few command line files to download some CSVs (php) manipulate their data and spit out a new csv (DTS) and then upload the new csv (php). When I was running this one one server it was fairly easy to make my DTS call. I derived this from DTSRunUI:
E:\clickability_test>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSRun" /S "(local)" /N "CMPublish_to_Lyris" /V "{E61B77AE-C92A-4B1C-91A1-5B05F8C7C3AF}" /W "0" /E
It worked wonderfully. However I was recently told I need to convert it to a 2 tier server system, where the PHP runs on one server and the SQL Server is on another server. The PHP server has access to the folder that contains DTSrun, and the SQL Server computer has access to the folder that has all the CSV's downloaded during the PHP script. The DTS runs perfectly when I remote into the SQL Server and run it from there (for some reason EM is installed on the same computer as the SQL Server, but it's not on the PHP server). However when I try to call it from my php server, I get this:
C:\clickability_test>\\sd\Binn\DTSRun /S "(local)" /N "CMPublish_to_Lyris" /V "{E61B77AE-C92A-4B1C-91A1-5B05F8C7C3AF}" /W "0" /E
DTSRun: Cannot create COM Server to load and execute DTS Package. Error -2147221164 (80040154): Class not registered
I even tried putting "sd" in place of (local) in suspision that when DTSRun is called from another computer, it checks the calling computer as (local). However this still doesn't work. Any one ever run into this before? Long story short, I'm trying to run a DTS package through command line on a different server.
-
To execute a windows process on another server you'll need to use a tool like PSEXEC from Sysinternals.
--------------------
Colt 45 - the original point and click interface
-
Actually, I decided to run the DTS package by using the xp_cmdshell command on a Stored Procedure, which I can then use PHP to call from the other server. The DTS executes properly in terms of data transformation, but as soon as it tried to modify or write to files on the other server, it has issues. It doesn't have these issues when I run it in the EM or from the command line. Basically I have an activex script that appends the rows effected to a log file on the other server, which is accessed using a UNC like this: \\othercomputer\exports\logs\2007_5_17.log. Again, this part works perfectly when I run the DTS package from EM or from the command line with EM, but I get permission errors when I run it from a sproc. Here's a little more detail:
This is from sproc:
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 38
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 38
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
This is from command line:
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 608 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 608
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
Any ideas?
-
What account does the SQL Services run under? When you execute a DTS package manually it will use your login credentials. Executing from a stored procedure/schedule job/etc... it will run with the credentials of the service.
Just make sure your SQL services are running under an account that has privelages to the UNC path you're trying to access.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply