August 23, 2012 at 12:23 pm
Hi,
From a VB script task in SSIS, I am trying to run a bat file.
Here is my piece of code. This works fine from Visual Studio but when running it under a scheduled SQL job, it hangs.
Any idea? Is there a security warning on server I do not see?
'Run the batch file created previously, to download the raw data files.
Dim myProcess As New Process
With myProcess
.StartInfo.UseShellExecute = True
.StartInfo.Arguments = " /c \\BLMCIK\CMAMS\Monthly\getMonthlyAllFiles.bat"
.StartInfo.FileName = "cmd.exe"
.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
.Start()
End With
Thanks!
August 23, 2012 at 1:00 pm
I'm curious why you are not using an "Execute Task" task to run the batch file?
If the executable is waiting for an interactive response, it will appear to hang. We had this issue with executables located on 'untrusted' shares, but I haven't seen it happen with a local cmd.exe call.
Can you log onto the server and run the SSIS package interactively?
August 23, 2012 at 1:06 pm
I tried the not to use the execute task but running it on the server, I was getting a security warning pop up window. So I figured out that was the reason why my SQL job was hanging. Waiting for someone to answer the security warning question.
Hope this help to clarify.
Also, in the .bat file, I have the following commands, It might help!
\\BLMCIK\CMAMS\pscp.exe -l admin -pw report 192.168.226.27:/home/report/usage1.20120822 \\BLMCIK\CMAMS\Daily\usage1.20120822
\\BLMCIK\CMAMS\pscp.exe -l admin -pw report 192.168.226.27:/home/report/usage2.20120822 \\BLMCIK\CMAMS\Daily\usage2.20120822
August 23, 2012 at 1:24 pm
You'll need to add the file server shares that those executables in your batch file are on as trusted sources on your server.
You'll want to check with an administrator about this, but I believe you can do this through Internet Explorer on the server. In IE, under Tools/Internet Options/Security/Local Intranet. click the sites button, Click the Advanced button, and add your file share paths.
August 23, 2012 at 1:32 pm
HI ,
I finally changed the way I was trying to get it done.
So instead of writing a .bat file with the PSCP.exe command lines, and then run that .bat file using execute task CMD.exe,
in my process task I run that pscp.exe command with all the arguments.
So it looks like this....
With myProcess
.StartInfo.UseShellExecute = True
.StartInfo.Arguments = "\\BLMCIK\CMAMS\pscp.exe -l report -pw report " & RawData(i).ToString & " " & TreatedData(i).ToString
.StartInfo.FileName = ArgumentLine
.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
.Start()
.WaitForExit()
End With
August 24, 2012 at 9:24 am
Finaly, runnnig from an SQL job, it hangs, waiting for a click to a security warning message I guess.
Did one of you successfully ran pscp.exe from a vb script task and had it in a SQL job?
Thanks!
August 24, 2012 at 10:22 am
You are trying to run pscp.exe from a location that is not trusted by the server for running executables.
To fix this, you will need to either add the file share location to the server's list of trusted locations (see my post above), or install the executables to a location the server will allow executables to be run from such as a local drive or attached SAN.
August 24, 2012 at 11:23 am
Hi,
I have tried both suggestion and still the same issue.
I tried adding \\blmcik to the trusted sites list and enabled the option "Launching applications and unsafe files"
I replaced the \\blmcik\CMAMS\PSCP.exe by c:\progra~1\PuTTy\pscp.exe
But no chance!
August 24, 2012 at 11:48 am
Hmm, there must be some additional reason it is looking for interactive input then. Your best bet would be to log into the server interactively as the account you are using to run the job, execute the program, and see what it is prompting you for.
It is probably a prompt to confirm the machine you are connecting to with PSCP, if you've never connected from the server before. Once you confirm it, future connections should work fine.
August 24, 2012 at 12:23 pm
Hi ,
To run the SQL job, I use NT AUTHORITY\NETWORK SERVICE account but I do not know the password for it.
I tried with a blank password but that did not do it.
What else could I use for the SQL agent user account?
August 24, 2012 at 12:52 pm
Ok, I'm a bit confused now.
Are you running this package on the server using the SQL Server Job Agent?
August 24, 2012 at 1:30 pm
Ok, I think I might know what is going on.
You've created the job running this package as a sysadmin, so the package is being executed as the SQL Agent service account, and the agent is set up to run as "NT AUTHORITY\NetworkService "?
If that is the case, I think you are going to have problems both accessing the file share and executing the Putty SSH tools from that account.
I believe you'll need to use a network account that has access to the file share that you can use to run the package.
To address the security pop-ups, log on interatively to the server using this account, and run the package. Address the security pop-ups, which I believe will only happen on the first run.
Then in SQL Server set up a 'credential', using the username/password of the network account. Then create a PROXY using that credential, and make sure the SSIS subsystem is checked for it.
Edit your job, go to the step running the package, and change the Run As: dropdown to the PROXY you created.
The job will then run your package with the security context of the network account.
August 24, 2012 at 2:02 pm
I would run cmd.exe using system account and give a try running bat file to find interactive issue.
How to run cmd.exe as system account
I had similar issue when I was using xp_cmdshell and running psinfo utility which requires you accept user agreement one time.
August 24, 2012 at 2:33 pm
I got it now. from this link -->
Yes I did get this resolved. It is kind of an ugly work around. The error is because I am getting prompted the first time I run SFTP from that MSSQL account, so Ihave to force in a "y" response.
To do this I created a text file named "yes.txt" and inside of it i typed the word "yes".
Then in my batch script I reference it to answer the prompt like so:
psftp -P 23 192.168.1.251 -l myUserRed -pw mypasswordRed -b E:\Data\Test\Test2\ftp_commands\ftpScriptThatTransfersfiles.txt < E:\Data\Test\Test2\BCP\yes.txt
exit
August 24, 2012 at 2:38 pm
infodemers (8/24/2012)
I got it now. from this link -->Yes I did get this resolved. It is kind of an ugly work around. The error is because I am getting prompted the first time I run SFTP from that MSSQL account, so Ihave to force in a "y" response.
To do this I created a text file named "yes.txt" and inside of it i typed the word "yes".
Then in my batch script I reference it to answer the prompt like so:
psftp -P 23 192.168.1.251 -l myUserRed -pw mypasswordRed -b E:\Data\Test\Test2\ftp_commands\ftpScriptThatTransfersfiles.txt < E:\Data\Test\Test2\BCP\yes.txt
exit
Let's make it easier for others to check out the url you posted:
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply