February 18, 2004 at 8:21 am
I use sql server 2K.
I have a dts package stored in DTS - local package, that has to be run as a part of a job ..essentially as a job step. how can i do this ?
i tried using
DTSrun /S MSATEN /U user /P password /N MDLandrec_Excel_Totals_Import but this failed.
any help is appreciated.
February 18, 2004 at 8:51 am
I setup a .cmd file with the following command...
February 18, 2004 at 8:51 am
I setup a .cmd file with the following command...
February 18, 2004 at 8:52 am
I setup a .cmd file and executed the following...
February 18, 2004 at 8:53 am
I setup a .cmd file and executed the following... start dtsrun.exe 2> dtsrunlog.txt /S SHODBS09 /E /N Batch DTSRUN Execute Process This Worked
February 18, 2004 at 8:59 am
Acually I do not want to run it from windows or dos prompt.
is there anyway i can do it as a SQL server agent job-job step, since this has to run as a part of a SQL server agent job.
February 18, 2004 at 10:42 am
I've used the dtsrunui utility to generate the dtsrun command, then copied it into a new job step.
Check this SQLDTS.com article for brief instructions on how to use dtsrunui. I would just add that after you click the "Generate" button, you copy the genrated dtsrun command and paste it into a new step in your job. You may want to remove the package and version GUIDs from the command and leave the package name.
Hope this helps.
Greg
Greg
February 18, 2004 at 11:05 am
Right click on the package name in Local Packages and select Schedule Package. The schedule details you select don't matter. Click ok. Now go to the scheduled jobs under SQL Server Agent. There should be a job with the same name as the DTS package. Go to the Steps tab of the job's properties. The step will be of type Operating System Command. The command will be DTSRun /~ plus the package's GUID. Copy that command. Now you can add a step to the scheduled job that you really want to run. Make it of type Operating System Command and paste in the command that you copied from the other job. Delete or disable that job so it doesn't run.
February 18, 2004 at 12:14 pm
Probably what Greg Charles and diogenes0412 have suggested will generate the same kind of code. I did try one method - to paste the code as suggested from the scheduled package job to my job, but it errored out at that step. The package job also errors out , but if I run 'execute package' from the actual package the job does complete successfully. This job is trying to import a excel file. Since the job has been genrated automatically , the job should not have any errors. I am really perplexed. I would appreciate any help.
February 18, 2004 at 12:21 pm
It could be a permissions issue now. The scheduled job will run as the SQL Server Agent startup account, whereas the original DTS package is run under your own credentials. You could also have a problem if the package is trying to import the Excel file from a mapped drive instead of a unc, and the SQL Server Agent account might not have the appropriate permissions to access the file anyway.
February 18, 2004 at 12:31 pm
Well, I am running this job as administrator, so I might have the permission. While viewing the job history, it tell me that it failed since it did not find the excel file in c:\.......\XXX.xls . But the file is there, and the package can process the same file while it is not running as a job. If I could look at the parameters that the DTSRUN is executing, it might make some sense. Looking at a bunch of what looks like ASCII code makes it hard to interpret. Could it be that it is looking for a file with quotes or something ? How do I parse the long DTSRUN string ?
February 18, 2004 at 2:20 pm
Just made it work ! The reason for the error was because my file pointed to a location in my hard drive and the server was looking at its own hard drive when running the package as a job. When I ran this package locally using execute package, it was a client process and could locate the hard drive.
I changed the location of the excel file to a network drive it works well . Thanks for all your input, folks.
February 19, 2004 at 6:59 am
I would never call the dts by the guid you might pull the wrong version should you change the DTS in any fashion.
February 19, 2004 at 7:21 am
Do you have any suggestion of how i can implement this package as a job step without using GUID, and the package being a local package ?? Any thoughts would be appreciated.
February 19, 2004 at 7:29 am
In a job step select "Operating System (cmdexec)"
In the step information use
DTSRUN /S<servername> /N"<job name>" /E
<servername> is the name of the server, <job name> is the name of the job, /E is integrated security (uses the sql server agent account to exec the dts replace /E with /U<username> /P<password> if not using integrated)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply