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?

more ▼

asked Nov 23, 2009 at 03:26 PM in Default

Brenden gravatar image

11 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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.

more ▼

answered Nov 23, 2009 at 04:33 PM

Gustavo gravatar image

592 4 4 7

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.
Nov 23, 2009 at 04:58 PM Brenden

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.
Nov 24, 2009 at 07:17 PM Gustavo
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 23, 2009 at 03:47 PM

Raj More gravatar image

Raj More
1.7k 80 82 84

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?
Nov 23, 2009 at 05:00 PM Brenden
@brenden: answer edited for comments
Nov 24, 2009 at 06:16 AM Raj More
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 23, 2009 at 03:26 PM

Seen: 3605 times

Last Updated: Nov 23, 2009 at 05:52 PM