August 8, 2016 at 10:49 am
I am sorry I don't see a Business Intelligence forum for 2008. I have several SSIS packages that launch external programs either batch programs for things like SFTP or VB Scripts for older programs. These work perfectly in BI Development manager but once they are packaged and sent to MS Agent they appear to run fine but the system just seems to skip those tasks and they don't appear to run.
any advice would be helpful.
Charles
August 8, 2016 at 12:15 pm
charles.russell (8/8/2016)
I am sorry I don't see a Business Intelligence forum for 2008. I have several SSIS packages that launch external programs either batch programs for things like SFTP or VB Scripts for older programs. These work perfectly in BI Development manager but once they are packaged and sent to MS Agent they appear to run fine but the system just seems to skip those tasks and they don't appear to run.any advice would be helpful.
Charles
Lots to go over on this but Ill just start with the obvious.
Does the SQL Server Agent account have permissions to the applications/files/folders your processes are running?
August 8, 2016 at 12:31 pm
Yup...there is a lot that could be going on. Debugging a package generally requires a log as unknown errors or issues are hard to find. So you'd want package logging if you don't have it already.
And then you may not get any errors when calling an external app from SSIS (or Agent) as just executing an can be considered success even if the process itself fails. So you often need logging in whatever external processes you call.
You could try executing those same commands using the security context of the Agent account to start troubleshooting, running the same commands from the command prompt, etc.
There just isn't enough information for anything other than guesses.
Sue
August 9, 2016 at 10:07 am
Thank you for the replies. I am a little new to this so forgive me off the bat. I don't know how to run these packages from the command line I am either running them via debug mode in BI Development Studio and then from SQL Management Studio via the agent.
The Agent user should have the same permissions as the user that I am invoking the BI debug process from.
There are 2 processes that I am working with. The one I will concentrate on invokes 2 .net exe's as part of its multi part process. When I run it in debug mode the exe's get ran (I see them run in the task manager and see their consoles pop up. I also look at the debug log after execution and I don't see any errors. When they are invoked by the agent the whole package runs successfully (and gives me a success message) but it act's like it just skips over the executable steps as I don't see the consoles and I don't see them in the task manager. I looked at the log file summary in the agent and I see no errors.
like I said I am new to this. I have built many processes but I have never added anything other then SQL or data transformation tasks.
Hope this helps
August 10, 2016 at 10:27 am
No worries. The problem in troubleshooting this with no errors is that it's a nested process - the executable on the server, those are called by the assemblies, those are used in the package, that is run through SQL Agent.
So you have at least four different pieces to isolated and try to track down. The issue could be in either one of those or permissions issues anywhere in the chain.
To run an executable from the command line, you run from Start -> Run and execute the command or run cmd to get a command window depending on the executable. Usually you would execute the same command as the assembly does but that's not always feasible so you may have to execute something similar that doesn't change any data, send notifications or other things you wouldn't want to have done when testing.
You can execute assemblies from SQL Server and eliminate the package or job pieces.
You can manually execute a package to eliminate the job component.
You just run through trying to isolate pieces, start at the lowest level and work up in the chain to see where things are failing.
I would still want to set up package logging as you could at least see how much time was spent on each part of the process in the package which would help you get an idea of if the steps really are being skipped or just throwing an error right away.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply