SQL Server Performance Forum – Threads Archive
Unable to run SSIS Package as a Job
I’ve created an SSIS package but I’m unable to run it as a job from SQL Server Agent. Whenever I run the job, I receive a generic error message with no details. I created the package on my local system and saved it to the SQL Server. The package protection level I chose was "Rely on server storage and roles for access control". I set up the job through my account as a sysadmin. The package itself runs fine by itself, debugs properly, and I can run it on the server manually but not through the SQL Server Agent. The package basically selects data from 3 different servers, unions it, and exports the results as a CSV file on the server. Any help would be greatly appreciated.I found a way to work this out. It was a very odd thing though. The SSIS package I created queried 3 servers, UNIONed the queries, and dumped the results of the UNION to a csv file. I was able to run the package on my machine through SQL Server Business Intelligence Development Studio. The connections were made with my login information using Windows Authentication. I have access to all 3 servers with my login. I can run the built package on the server with SSIS and it completes. I can run the package from the command line on the server also and it completes. However when I ran it as a job, it failed. So I thought it might be an issue that I created the package on my local machine. So I tried to create it on the server. However, the server running the SSIS package did not have the ability to logon to one of the other servers. So I created the login on that server, went back and ran my SSIS package as a job and it completed successfully. Very strange indeed. I hope this helps someone else out if they bump into a similar problem. I think that perhaps my account did not have access to write the file to disk which gave me an error, meanwhile the Server Windows Account did not have access to one of the servers and gave me an error because of that.
This looks a lot like the problems many of us ran into, the first time we try to run our SSIS packages as agent jobs, or have someone else run the package. Have you cahnged the default protectionlevel setting for the package? It is set to ‘encryptsensitivewithuserkey’ so often, things work well from BIDS, yet fail when run from Agent. have a look at the properties on the control flow of the package. under security, there is a protectionlevel setting. I’ve change from ‘encryptsensitivewithuserkey’ to ‘encryptsensitivewithpassword’ – that resolved many problems for me. Note, when you do this, then the job calling the package must pass that password when trying to execute the package. HTH Panic, Chaos, Disorder … my work here is done –unknown
]]>