February 3, 2009 at 9:28 am
I have an execute process task in my dtsx package which runs a bat file which renames a text file with the current date.
This process runs successfully and the bat file updates the >>logfile but fails to rename the text file. If I run the bat file outside of the package it renames the file and updates the >>logfile
The bat file is:
++++++++++++++++++++++++++++++++++++++++++++++++++++
@call set log-file=\\myservername\shared\Receipts\backuplog.txt
@ECHO ++++++++++++ %date%++++++++++++++
:: set variable today as system date
set today=%date%
:: set variable today1 as system date without the /
set today1=%today:/=%
::set daymonth variable by truncating variable today1 as ddmm
set daymonth=%today1:~0,4%
:: set year variable by truncating variable today1 as yy
set year=%today1:~6,2%
:: rename file Yddmmyy1 as Ydaymonthyear1.txt
cd \\myservername\shared\Receipts
ren Yddmmyy1.txt Y%daymonth%%year%1.txt
::log action
@ECHO *********************************************************** >>%log-file%
@CALL Date /T >>%log-file%
@CALL Time /T >>%log-file%
@echo rename.bat has run. If it exists Yddmmyy1.txt File renamed as Y%daymonth%%year%1.txt >>%log-file%
++++++++++++++++++++++++++++++++++++++++++++++++++++++
If i use a scheduled task to run the bat file this also renames the txt file ok .
Can anyone advise me why it won't rename the file when called by an execute process task in a dtsx package?
February 3, 2009 at 10:02 am
If you are executing the dtsx package as a sql job, make sure that the credentials under which the job is running have modify permissions on the folder in which your files are located. You may need to create a "Credential" in the SQL Server instance that is running the job, and set that credential as a proxy for the job.
If it's not being run by a sql job, I too would like to know what is the root of the problem.
February 3, 2009 at 10:21 am
I haven't got as far as running it as an sql job yet . This is still being debugged in BIDS. It runs with full admin rights.
I have discovered if I use a drive mapping file path instead of a URL for the executable path in the execute process task it works
(i.e. k:\Receipts\rename.bat
instead of
\\myservername\shared\Receipts\rename.bat)
But ........
I now get the open file security warning and have to click on "run " before it will execute.
Any advice on how to get rid of this warning would be appreciated.
February 4, 2009 at 8:48 am
ok I searched other forums and found a work around for How to tell the Execute Process Task not to open the Security Warning dialog :
Instead of using the Execute Process Task to call a batch file, use it to call C:\Windows\System32\cmd.exe and then point it to the batch file as part of the command line arguments.
For example, The Execute Process Task would use the following as the executable:
\\myservername\shared\Receipts\rename.bat
The new Execute Process Task uses the following:
Executable: C:\Windows\System32\cmd.exe
Arguments: /C \\myservername\shared\Receipts\rename.bat
It works- no open file security warning !
However it then fails to rename the file again so back to the original problem. How to get the cmd.exe to execute in the correct location and therefore rename the file.
🙂
I found the working directory property in the execute process task has to be set to the location of the file.
The final version Execute Process Task uses the following:
Executable: C:\Windows\System32\cmd.exe
Arguments: /C \\myservername\shared\Receipts\rename.bat
WorkingDirectory: k:\Receipts\rename.bat
NB It doesnt rename the file if the WorkingDirectory uses the URL filepath \\myservername\shared\Receipts\rename.bat
And it doesn't seem to matter whether I use \\myservername\shared\Receipts\rename.bat or k:\Receipts\rename.bat
in the .bat file
Hope this helps anyone
February 4, 2009 at 11:37 am
Thank you Lizzy good work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply