question

Brenden avatar image
Brenden asked

SQL Agent & SSIS job step

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?

sql-server-2008ssissql-agentjob
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Gustavo avatar image
Gustavo answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Brenden avatar image Brenden commented ·
No connection/permissions issue that i'm aware of. I've had permissions issues before, but created a network login for the instance and SQL is running under that login. The way i'm currently running the job is selecting it under SQL Agent and clicking "Start job at step...". Does that method still run it under my login? I was assuming that was the same as if i set up a schedule and let SQL Server Agent run based on that schedule.
0 Likes 0 ·
Gustavo avatar image Gustavo commented ·
Everytime you run it ( even clicking "start job at step" ) its your credentials that is being used. When running through SQL SERVER AGENT scheduler, it the service/local/proxy account that is used. If you suspect this to be a secutiry problem, try creating a proxy account for your AGENT.
0 Likes 0 ·
Raj More avatar image
Raj More answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Brenden avatar image Brenden commented ·
Under each step, in the Advanced section, i have "Log to Table", "Append output...", and "Include step output..." all checked. Where else do i enable the logging so i can see at any point where the package is processing?
0 Likes 0 ·
Raj More avatar image Raj More commented ·
@brenden: answer edited for comments
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.