<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Running .bat file in SSIS package
17 posts, Page 1 of 2
1
2
»»
Running .bat file in SSIS package
Rate Topic
Display Mode
Topic Options
Author
Message
infodemers
infodemers
Posted Thursday, August 23, 2012 12:23 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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
sestell1
sestell1
Posted Thursday, August 23, 2012 1:00 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 854,
Visits: 1,273
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
infodemers
infodemers
Posted Thursday, August 23, 2012 1:06 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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
sestell1
sestell1
Posted Thursday, August 23, 2012 1:24 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 854,
Visits: 1,273
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
infodemers
infodemers
Posted Thursday, August 23, 2012 1:32 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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
infodemers
infodemers
Posted Friday, August 24, 2012 9:24 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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
sestell1
sestell1
Posted Friday, August 24, 2012 10:22 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 854,
Visits: 1,273
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
infodemers
infodemers
Posted Friday, August 24, 2012 11:23 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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
sestell1
sestell1
Posted Friday, August 24, 2012 11:48 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 854,
Visits: 1,273
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
infodemers
infodemers
Posted Friday, August 24, 2012 12:23 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 14, 2012 11:47 AM
Points: 39,
Visits: 141
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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
