DTS Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Error

Author

Topic

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 13:32:33

Hi...
My DTS package is not running. Its is failing as soon as it starts to copy the data.

Error Message is as below:-
The process could not be created for step 1 of job (reason: The system cannot find the file specified). The step failed.

Any suggestions????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 13:41:07

Is this from a job? If so, log into the database server using the same account that is used for the MSSQLSERVER service account. Run the DTS package in Enterprise Manager by executing it from the designer, not from a job.

Tara Kizer
aka tduggan

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 16:30:46

The error is from the job. I tried to delete the job and create a new one, and run it. That also fails with the same error.

But I am able to execute the DTS package successfully.

Any suggestions????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 16:33:23

The problem has to do with the account that the MSSQLSERVER service is using. Is it using the Local System Account? Is the DTS package trying to get to a remote resource? Follow the steps in my first post to duplicate what a job is doing.

Tara Kizer
aka tduggan

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 17:19:45

I am trying to replicate the issue as you have said. But I have a question in the mean time...
The server is using the default MSSQLSERVER service account. I have created the job using my Local System account. But still the job runs with the default MSSQLSERVER service account. Why is that? I have another job, and that runs with my local system account.

My local system account has SA access to the box. But still it doesnot take it. But when i change the owner of the job to an ID which does not have SA access to the box, I get an error message saying insufficient access. Any thoughts???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 17:24:14

That's just how it works. Jobs use the service accounts. This is why the recommendation is to use a domain account that has local admin privileges for it. So make that change and grant permissions to the location of the file to this account. Then test the job.

Tara Kizer
aka tduggan

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 18:25:16

Now, that is the big question... which file? The DTS job just copies data between two tables in two different databases (
Steps:-
1) Copies data From Table A1 in Database A to Table B1 in Database B
2) Updates Table B2 in Database B.
3) Updates Table A2 in Database A
4) Updates Table A3 in Database A).
Now which file is the job trying to find and is not able to. I am not using any file. Is this any system file specific to the account?

Thanks for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 18:29:13

I don't know what is meant by file if you are only copying data from table to table. Your error message indicates you are using a file somewhere in the package.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-31 : 18:52:11

It sounds to me like this isn't an error with the DTS package, but a problem with how the job is setup.

It's trying to execute a command, and it isn't finding that command or some file it needs.

If you want us to look at that, script out the job, and post the script.

CODO ERGO SUM

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 19:22:53

-- Script generated on 5/31/2006 4:58 PM
-- By: Karthik
-- Server: SQL

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Reporting')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Reporting'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Reporting'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Reporting', @owner_login_name = N'Karthik', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'dtsrun', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 0, @freq_type = 4, @active_start_date = 20060531, @active_start_time = 111200, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 20060531, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 19:28:50

Did you modify the code? The part that adds the job step does not appear to be correct.

@command needs to be a valid statement.

You might need to put the entire path to dtsrun.exe in there if it isn't finding it (which may what the error is saying). You could also put the directory in your path to prevent this.

Could you copy what you have in the job step from Enterprise Manager into here? If there is a password in the command, just put x's in there instead so that it does not go out onto the Internet.

Tara Kizer
aka tduggan

carthik_s
Starting Member

34 Posts

Posted - 2006-05-31 : 19:36:23

I did a generate SQL script and have pasted whatever the script generated. Do you think that there is something wrong in the job step?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 19:39:17

Yes. The dtsrun command appears to be wrong. It should look something like this:

dtsrun /NpackageName /SserverName /E

But it looks like this in your job step:
dtsrun

On the databas server, run this in a cmd window to see its options:
dtsrun /?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources