My DTS package contains Environment Variable assigned on local server. When
manually run the DTS, it works fine. But when I schedule this DTS as SQL
server job and run as job, I get error: "Environment Variable not found."
Are there any recommendations to solve this ptoblem?
Thanks in advance,

-George

Is it a system variable? When scheduled you execute under the profile of the
SQL Server Agent service account, so a variable in your personal profile is
no good.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Mike Torry" <MikeTorry (AT) discussions (DOT) microsoft.com> wrote

Darren,

It is system variable. SQL Agent account has 'sa' authority.
Still not work. Thanks.


"Darren Green" wrote:

Maybe it is to do with the service account for the SQL Agent. I don't know
if there are any limitations, but why not write a job that dumps the
variables to a file so you can examine them. Sample job bewlo. All it does
is run SET and uses the step output file to catch the data. Create and run
this job and then review the step output file -
C:\Temp\DebugEnvironmentVariables.txt.

-- Script generated on 11/02/2005 08:07

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'DebugEnvironmentVariables')
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 ''DebugEnvironmentVariables'' 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'DebugEnvironmentVariables'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'DebugEnvironmentVariables', @owner_login_name = N'sa',
@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'DebugEnvironmentVariables', @command = N'SET',
@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'C:\Temp\DebugEnvironmentVariables.txt', @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 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:



"Mike Torry" <MikeTorry (AT) discussions (DOT) microsoft.com> wrote