When I select “SQL Server Integration Services Package” as one of the job steps, SQL Agent gets to that step and hangs there ad nauseam.
What should take minutes has yet to complete. I’ve quit the job in the middle because of this, so no logging is provided other than letting me know that I’ve just cancelled the job.
I can run T-SQL steps and Powershell steps with flying colors.
I’ve created the package via “BIDS” Business Intelligence Development Studio, and clicked on “Save copy of package to..” and saved it to our SQL server. The job step points to the saved SSIS package.
Is there anything I’m doing wrong? Anything I should look at? How do I know where the SSIS package is in the process when running through SQL Agent?
Answer by Gustavo ·
Are you having connection/permissions problems ? because when you execute the SSIS Package manually, you run it under your current user, but when executing within SQL Server Agent you run it under with a Service/Local/Proxy Account.
Answer by Raj More ·
You should turn on Logging and log the activity in your package to whichever option you find best meets your needs. That will tell you a good deal about what the package is up to at any point.
There are several levels to logging so try it in DEV first before you turn on the hose in Production.
Logging is a two part process. Part 1 is where you tell SSIS what to log. So in the package, under logging, you tick mark what you want logged. Part 2 is you choose WHERE to log. When I schedule the package to run using SQL Agent, I go to the logging tab and I put in the options of the SQL Server or File that I want to log to be written.