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


ERROR when Executing a Job within SQL Server Agent from an SSIS package. SQL Server 2012, Visual...


ERROR when Executing a Job within SQL Server Agent from an SSIS package. SQL Server 2012, Visual Studio 2010

Author
Message
calebf
calebf
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Hello,
I created an SSIS package within Visual Studio 2010 that takes data from cURL, exports to a XML file then imports into a specified database, and it executes fine.
I then created a job within SQL Server Agent, to attempt to schedule this SSIS package. The job I am testing only contains one step; run the package created within SSIS.
When I "Start Job at Step" the first action of "Start Job [Job Name]" is a success. The second action of "Execute Job [Job Name] errors.
Once I go to the Package within SSMS, right click, Reports\Standard Reports\ All Executions, I see my Job failed. I click on "All Messages" for my Job. And I get the following Error:

Execute Process Task:Error: In Executing "\\CRN-EDW-01\InContact\Config File\1.bat" ""at"", The process exit code was "23" while the expected was "0".


For the life of me, I cannot find anything that references this exit code, nor can I figure out what the problem is. I have messed around with user rights within SSMS, as well as sharing the folders that the cURL is trying to access. The thing that really gets me is that the execution of the SSIS Package works fine, yet when it is ran as a job it errors.
Please help Smile
Thanks
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8631 Visits: 7660
The exit code comes from something in the .bat file, not SSIS. My guess is you've got a double-hop of some kind going on, messing with the rights to whatever it's doing.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
calebf
calebf
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
What do you mean by "double-hop"? I apologize, but I am still learning most of this.
I am using the .bat file as a means to pull from cURL. A vendor provided me with the cURL information.
First it accesses the drive where the cURL.exe file is located: \\CRN-EDW-01\InContact\curl-7.28.0\curl
Second it outputs to a certain file: -o \\CRN-EDW-01\InContact\InContact\data\1.xml
Then there is some sort of "k" command that was provided to me by the vendor.

The weird thing is, is that it works fine when executed manually, but as a job it gives the error.
Any ideas?

Thank you!!
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8631 Visits: 7660
Apologies, I forget not everyone has beaten their heads on authentication before. Oops.

I have no idea what cURL is or any of that, unfortunately. I couldn't tell you what's going on under the hood. However, the troubleshooting you've done leads me to believe it's the job causing the issue, not the code itself. The most likely problem when you can manually run something, but a job can't, is one of a few issues.

First, a job runs under the authentication of the SQL Agent. That Agent has a login defined in the services on the box it's installed on. You need to find out that user, and confirm that they have equivalent access that you do to be able to run all the necessary pieces. It may not have access to one or more files/directories.

If that's all well and good, then you need to determine if you're dealing with a Kerberos double-hop issue. Google that and you'll find reams of information on the problem, but the root cause is this: If your network isn't setup the way you need, authentication only can visit one server out. Otherwise it needs the equivalent of a repeater to send that security token to the next place. So, if your .bat is then calling for something on another machine, it may (note, may. Talk to your sysadmins) not be able to pass it along to the next one. Usually my office uses wireshark to determine when there's a problem of this nature.

From there, keep examining security possibilities. That is the 99% cause (in some form) when SQL Agent won't work, but you can manually run things.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
calebf
calebf
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Thank you. I will look into the SQL Agent access and post any successes.
My .bat file is not attempting to access any networks, it is only accessing the local machine (Though the server is a VM, but I don't believe that has anything to do with this.)
calebf
calebf
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Thank you again for your help.
After banging our heads for a couple of hours, we simply added "Everyone" as a Read/ Write user to the drive we were accessing the cURL file from within the Job, and BING, it worked!
RESOLVED!
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8631 Visits: 7660
I'd... be careful of considering that resolved. Even anonymous logins can get access with that security setting. However, you've confirmed, decisively, that it's security.

Now you just need to figure out why at some point. :-)

Glad you got it working, however.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
calebf
calebf
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Kraig
You are correct that Anonymous logins would be a concern, but this server has tight security for login access.
Thank you
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