DTS Packages (SSIS) Running as Scheduled Jobs

A lot of people, including myself, have been confounded by some mildly complex security settings in relation to DTS tasks.

Having deployed a DTS package to SQL Server 2005 Integration Services and then running the package from SSIS, I received no errors. The package ran as expected. I needed to run this package every ten minutes, so I set up a single step job via SQL Server Agent using the SQL Server Management Studio. When I attempted to run teh job there was no joy, simply a failed job.

I investigated the History logs as suggested by the very vague error message provided. You can do this by right-clicking on the job and selecting "View History". I discovered that the job was attempting to run as a domain service account. It turns out that when you run a job it is run by the server and not the user context. Running a package manually will use whatever user you logged on as.

So a bad way of fixing this problem would be to simply restart the SQL Server Agent windows service using the relevant user credentials (the credentials used to deploy the package to SSIS). We obviously don't want to do this.

So we must create a Credential and a Proxy, and then set up the particular step of the job to run as the Proxy rather than the SQL Server Agent account. Details can be found here.

These settings took me a bit of experimenting before I understood what was going on.