SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running .bat file in SSIS package


Running .bat file in SSIS package

Author
Message
infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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!
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3531 Visits: 3508
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?
infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3531 Visits: 3508
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.
infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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

infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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!
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3531 Visits: 3508
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.
infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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!
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3531 Visits: 3508
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.
infodemers
infodemers
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search