November 18, 2011 at 8:50 am
I have several SQL Server Agent jobs that execute SSIS packages via an Operating system (CmdExec) step. The command executed by the step references the full path location of a command file. i.e. dtexec /f "c:\ssis\packages\package_name.dtsx". I have to change the location of these command files to another drive. i.e. w:\ssis\packages\package_name.dtsx. Since I have to touch all these jobs anyway, I'd like to create a system environment variable %SSISPRIMARYDIR% which is set to W:\SSIS and use it in the command line instead of the hard coding the full path of the files in the job step. So the command would be
dtexec /f %SSISPRIMARYDIR%\packages\package_name.dtsx. I've not found anything online yet that tells me this can not be done. But I haven't found anything that tells me it can be done either.
I'm testing the use of the system environment variable in a job step in this manner.
Define new system environment variable %SSISPRIMARYDIR% and set it to W:\SSIS.
Changed value of an existing system environment variable %SSISCONFIG% to W:\SSIS\Configurations\EDWDEV.dtsConfig.
Restart the server.
Create a .cmd file that contains the following 4 lines of code
echo %SSISCONFIG% > %SSISPRIMARYDIR%\SSIS_sys_env_var_config.txt
echo %SSISPRIMARYDIR% > %SSISPRIMARYDIR%\SSIS_sys_env_var_primarydir.txt
cd %SSISPRIMARYDIR%
dir %SSISPRIMARYDIR% > %SSISPRIMARYDIR%\SSIS_sys_env_dir.txt
Create SQL Server Agent job Nancy_Test that executes a single step. The step is named Step1 and is type Operating System (CmdExec). Step1 is executed by the SQL Server Agent Service Account which is a user that was setup specifically to execute SQL Server Agent jobs.
First test: Set the command in Step1 to W:\SSIS\echo_ssis_sys_env_vars.cmd and execute job. The job executes successfully and the output .txt files are created in W:\SSIS and contain the exected results.
Second test: Set the command in Step1 to %SSISPRIMARYDIR%\echo_ssis_sys_env_vars.cmd. The job fails with a reason "The system cannot find the file specified".
So my question is can a system environment variable be used in a SQL Server Agent job step command line? If so, can any of you help me understand what am I missing in my setup?
Thanks,
Nancy
April 2, 2012 at 9:48 am
Apologies that I have nothing to add.. but did you ever find a solution to this? I've been trying to do something similar with backup directories but never quite solved it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply