Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Running .bat file in SSIS package Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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!
Post #1349290
Posted Thursday, August 23, 2012 1:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,392, Visits: 2,401
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?
Post #1349316
Posted Thursday, August 23, 2012 1:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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

Post #1349321
Posted Thursday, August 23, 2012 1:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,392, Visits: 2,401
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.
Post #1349328
Posted Thursday, August 23, 2012 1:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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
Post #1349330
Posted Friday, August 24, 2012 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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!
Post #1349753
Posted Friday, August 24, 2012 10:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,392, Visits: 2,401
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.
Post #1349801
Posted Friday, August 24, 2012 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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!
Post #1349834
Posted Friday, August 24, 2012 11:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:20 AM
Points: 1,392, Visits: 2,401
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.
Post #1349847
Posted Friday, August 24, 2012 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 9:10 AM
Points: 39, Visits: 142
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?
Post #1349866
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse