January 28, 2013 at 5:11 pm
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,
January 29, 2013 at 1:48 am
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,
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 29, 2013 at 4:06 am
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,
January 29, 2013 at 4:21 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 29, 2013 at 4:37 am
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.
January 29, 2013 at 4:48 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 29, 2013 at 8:13 am
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!!
January 29, 2013 at 8:34 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 29, 2013 at 8:41 am
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,
January 29, 2013 at 12:49 pm
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. 😎
January 29, 2013 at 12:55 pm
cafescott (1/29/2013)
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. 😎
Holy c**p! Never even heard of that EV before.
That is definitely not the sort of problem to be a pioneer with. I pity the first guy, who probably grew a beard while trying to solve this 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 29, 2013 at 1:01 pm
That is a wild solution. Out of curiousity I went bounding through the intertubes to attempt to figure out what that does. It appears to suppress warning errors for downloaded files... which makes some sense here, as a message box doesn't have a timeout, so it'll stall out your system.
My guess is you have some setting like this disabled or you trust files that your proxy doesn't, and thus is getting a warning about the file.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply