CmdExec Job : unable to get variable content

Question

CmdExec Job : unable to get variable content

lgalland

SSC Enthusiast

Points: 199
More actions
October 23, 2018 at 6:56 am

#381839

Hello,
I have an issue with the cmdexec script below : I can't get variable content to use it like parameter in a copy statement.
1- I get a string (the file name) from 1st file Headers.csv into variable %%i. This file has only 1 line. %%i content is Final_2018-01-01_181500.csv for example (no spaces in the name)
2- I set variable filename with %%i content
3- I concatenate 2 files Headers.csv and Content.csv in a third file named %filename%
The third file is never created when I execute the SQL job. Why ?
But when I execute the same script from DOS console, it works perfectly !!!
SET file_name=
FOR /F "tokens=6 delims=;" %%i in ('type \\SRVSQL\Headers.csv') do (
SET file_name=%%i
)
copy \\SRVSQL\Headers.csv + \\SRVSQL\Content.csv \\SRVSQL\%file_name%

Can anybody help me to solve this issue ?
Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

lgalland - Tuesday, October 23, 2018 6:56 AM
Hello,
I have an issue with the cmdexec script below : I can't get variable content to use it like parameter in a copy statement.
1- I get a string (the file name) from 1st file Headers.csv into variable %%i. This file has only 1 line. %%i content is Final_2018-01-01_181500.csv for example (no spaces in the name)
2- I set variable filename with %%i content
3- I concatenate 2 files Headers.csv and Content.csv in a third file named %filename%
The third file is never created when I execute the SQL job. Why ?
But when I execute the same script from DOS console, it works perfectly !!!
SET file_name=
FOR /F "tokens=6 delims=;" %%i in ('type \\SRVSQL\Headers.csv') do (
SET file_name=%%i
)
copy \\SRVSQL\Headers.csv + \\SRVSQL\Content.csv \\SRVSQL\%file_name%

Can anybody help me to solve this issue ?
Thanks for your help.

Does the SQL Server Agent service account have the necessary permissions, access to the unc path?

Sue

lgalland SSC Enthusiast Points: 199 More actions · Answer 2

Hello Sue,
yes, the permissions are OK because when I put a string like Final.csv instead of %file_name%, I have no error. The file is well created.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

lgalland - Tuesday, October 23, 2018 8:00 AM
Hello Sue,
yes, the permissions are OK because when I put a string like Final.csv instead of %file_name%, I have no error. The file is well created.

Have you tried putting it all in a batch file and calling that?
I've seen the issue where multiple commands won't execute but that doesn't seem to be the issue in your case.

Sue

frederico_fonseca SSCoach Points: 16014 More actions · Answer 4

I may be wrong, but all attempts I made at executing a cmdexec step only the first line on the command works.

e.g. no multiple lines are possible so your sample above will only work, as far as I know, is done on a .bat file.

Also I would possibly advise you to do this with powershell - more functionality and can be typed directly on the job step (although I still prefer doing those on a script and use cmdexec to execute them)

sample powershell - assuming I got the meaning of the for /i

$file_name = (Get-Content "\\SRVSQL\headers.csv").Split(";")[5] # zero based so this gets the 6th header
Get-Content "\\SRVSQL\Headers.csv","\\SRVSQL\Content.csv"|Set-Content "\\SRVSQL\$($file_name).csv"

Sue_H SSC Guru Points: 90860 More actions · Answer 5

frederico_fonseca - Wednesday, October 24, 2018 5:11 PM
I may be wrong, but all attempts I made at executing a cmdexec step only the first line on the command works.
e.g. no multiple lines are possible so your sample above will only work, as far as I know, is done on a .bat file.
Also I would possibly advise you to do this with powershell - more functionality and can be typed directly on the job step (although I still prefer doing those on a script and use cmdexec to execute them)
sample powershell - assuming I got the meaning of the for /i
$file_name = (Get-Content "\\SRVSQL\headers.csv").Split(";")[5] # zero based so this gets the 6th header
Get-Content "\\SRVSQL\Headers.csv","\\SRVSQL\Content.csv"|Set-Content "\\SRVSQL\$($file_name).csv"

I thought it was the one line issue too - I've always had to use && between commands and on one line.
And why I use Powershell more often instead.....

Sue