<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
»
Data Warehousing
»
Integration Services
»
what to do when package suspends when run as...
12 posts, Page 1 of 2
1
2
»»
what to do when package suspends when run as a job
Rate Topic
Display Mode
Topic Options
Author
Message
cafescott
cafescott
Posted Monday, January 28, 2013 5:11 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
Hello,
I'm working on a package that does these things:
1. moves a file on a network to a server on that network
2. From that server, encrypts the file
3. copies the encrypted file back to the network location.
My package dynamically creates a single batch file which performs these tasks. It then calls a process that calls the batch file. (I'm using "cmd" as the filename, and pass in the batch file as an argument; /c \\server\temp\mybatchfile.bat)
In the package store on the server where the package resides, when I run by right-clicking the package the batch file works as expected. However, when I try to run the package as a job execution hangs. (There is no error in the History log because Integration Services doesn't regard suspended execution as an error.)
I'm under a lot of deadline pressure. So, I'm wondering if SQL developers out there can help me by answering these questions:
1. How would you create a package that runs as a job that performs the three steps above?
2. How would you debug the problem where a package that has to run as a job hangs, doesn't do anything?
If I could narrow down the source of the problem it would be extremely helpful. However, I don't know what is the best way to do this.
thanks,
Post #1412707
Phil Parkin
Phil Parkin
Posted Tuesday, January 29, 2013 1:48 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 4,242,
Visits: 9,494
The job is probably running under the credentials of the SQL Agent user.
This user may not have permissions to the various files involved - I suggest that you investigate this possibility first.
You would potentially get better visibility of where things are going wrong if you used SSIS components to do the work, rather than an external batch file,
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1412813
cafescott
cafescott
Posted Tuesday, January 29, 2013 4:06 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
Phil Parkin (1/29/2013)
The job is probably running under the credentials of the SQL Agent user.
This user may not have permissions to the various files involved - I suggest that you investigate this possibility first.
You would potentially get better visibility of where things are going wrong if you used SSIS components to do the work, rather than an external batch file,
Thanks for the feedback, Phil. I forgot to mention the crucial point that I have been running the job using a proxy that is based on my network login. So, I don't know what difference there is between running a proxy based on my network login versus me right-clicking on the package in the package store.
Also, I'm pretty sure that you're right about permissions. However, what permissions? There are several layers of security involved here. Microsoft doesn't offer too many ways to solve it. I'm trying to narrow down what permissions are not being met.
Finally, at the bottom of your post is some schooling about how to ask questions on this forum. I'm not sure if that is your generic signature or whether you believe my post violated the decorum. I did ask two questions in my original post.
thanks,
Post #1412886
Phil Parkin
Phil Parkin
Posted Tuesday, January 29, 2013 4:21 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 4,242,
Visits: 9,494
cafescott (1/29/2013)
Phil Parkin (1/29/2013)
The job is probably running under the credentials of the SQL Agent user.
This user may not have permissions to the various files involved - I suggest that you investigate this possibility first.
You would potentially get better visibility of where things are going wrong if you used SSIS components to do the work, rather than an external batch file,
Thanks for the feedback, Phil. I forgot to mention the crucial point that I have been running the job using a proxy that is based on my network login. So, I don't know what difference there is between running a proxy based on my network login versus me right-clicking on the package in the package store.
Also, I'm pretty sure that you're right about permissions. However, what permissions? There are several layers of security involved here. Microsoft doesn't offer too many ways to solve it. I'm trying to narrow down what permissions are not being met.
Finally, at the bottom of your post is some schooling about how to ask questions on this forum. I'm not sure if that is your generic signature or whether you believe my post violated the decorum. I did ask two questions in my original post.
thanks,
It's a generic signature - your post is fine! I've seen too many posts that state a situation without asking for help ("my T-SQL doesn't work") - makes me think they're updating their Facebook status rather than posting in a professional forum.
Are you able to add something to your .bat file to at least work out where it is hanging? Possibly writing to a text file or something similar?
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1412897
cafescott
cafescott
Posted Tuesday, January 29, 2013 4:37 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
Are you able to add something to your .bat file to at least work out where it is hanging? Possibly writing to a text file or something similar?
Your initial comment seems to be the way to go. Instead of having a single batch file that moves files and decrypts a file, I plan to atomize each step using SSIS components. So, I'll move with the File Scripting Task (sp?). If that is working I'll focus on just the decrypting, possibly using the built-in Execute Process Task.
Thanks for the insight. I'll post again later today when I have more answers.
Post #1412909
Phil Parkin
Phil Parkin
Posted Tuesday, January 29, 2013 4:48 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 4,242,
Visits: 9,494
cafescott (1/29/2013)
Are you able to add something to your .bat file to at least work out where it is hanging? Possibly writing to a text file or something similar?
Your initial comment seems to be the way to go. Instead of having a single batch file that moves files and decrypts a file, I plan to atomize each step using SSIS components. So, I'll move with the File Scripting Task (sp?). If that is working I'll focus on just the decrypting, possibly using the built-in Execute Process Task.
Thanks for the insight. I'll post again later today when I have more answers.
Good luck. Yes - try the Execute Process task.
If you're at all proficient at coding, I'd recommend a Script Task rather than a File System Task. You can see and control exactly what is happening in just three or four lines of C#/VB code.
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1412923
cafescott
cafescott
Posted Tuesday, January 29, 2013 8:13 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
Wow, this is proving to be **nightmarish** project. I still can't get my package to run as a job. It runs from the package store just fine. Here is where I am in case someone can help.
When running as a job, the package can create a batch file. This batch file appears on the PGP server. I can also create it at the network location where the encrypted file will ultimately wind up.
The batch file has been reduced to a really simple three commands:
pushd "\\PGPServer\Temp\"
del deletethis.txt
dir *.bat > batchfiles.txt
That's it--I'm not encrypting with this version. All I need it to do is move to the PGP server, delete a text file and create one.
As a job, the package reaches the Execute Process task (to run the batch file) and just hangs. It doesn't delete or create the file on the remote server. It does nothing and leaves no error behind.
I'm running the job using a proxy from my login as well as the SQL Server Agent Service Account.
The PGPServer happens to be the same server where an instance of SQL 2008 is installed. The package resides in the Integration Services portion of this server. In the "user accounts" section of Control Panel for the PGPServer, there are two users created: "PGPServer SQL Agent", and "PGPServer SQL Integration Server". Both accounts are administrators.
I didn't create these accounts. I believe that they match the accounts that are used to run the job. In Windows Explorer, I have explicity granted "control all" to both accounts in the PGPServer's temp folder.
If anyone can help me get to the point where I can run a simple batch file as a job I'd appreciate it!
Many thanks!!
Post #1413071
Phil Parkin
Phil Parkin
Posted Tuesday, January 29, 2013 8:34 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 4,242,
Visits: 9,494
It's probably not relevant, but I thought I'd post just in case. Did you know that if you copy a batch file to another server and then run it, it executes in the context of where it is being run from, not where it is physically stored?
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1413084
cafescott
cafescott
Posted Tuesday, January 29, 2013 8:41 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
Phil,
That is why I'm putting this command at the top of the batch file:
pushd "\\PGPServer\temp"
I'm trying to avoid that context issue. What seems to be happening is that this command is not working.
thanks,
Post #1413090
cafescott
cafescott
Posted Tuesday, January 29, 2013 12:49 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109,
Visits: 498
I discovered this page:
http://sqlserverdownanddirty.blogspot.com/2011/07/ssis-execute-process-task-hangs-when.html
I added the SEE_MASK_NOZONECHECKS environment variable to the server that houses the SQL installation where my package resides. It now works as a job!
Talk about obscure solutions, but I'll take this miracle.
Post #1413269
« Prev Topic
|
Next Topic »
12 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.
