PROBLEMS TRYING TO EXECUTE AN SSIS USING AN SQL JOB
-
Hi All,
My team and I are investigating on this issue for 5 days and we don't find the solution.
We try to execute an SSIS using a SQL job and the following error appears:
"Executed as user: . The package could not be loaded. The step failed."
We execute the SSIS using the sql_agent_proxy account that is administrator of the machine.
The most curious is that if we execute the IS using a job with the operating system comand option and dtexec.exec the SSIS is executed correctly, but is we use the SQL Server Integration package option to execute the SSIS, this fails.
Please, someone can help us??
Thanks in advance!!!
-
Please!! Help us! No one have had this problem? We have investigated all the possibilities and we don`t find the solution...
-
Not sure if this will help you but we also use SQL Jobs to execute SSIS packages. Sounds like it is definitely a security issue... perhaps you need to execute the job with the same user that is running the SQL Server Agent Service?
Anyway, I tried many approaches and the one that "made it work" was to deploy the SSIS package using Visaul Studio (as opposed to the deployment utility). Click in the yellow background of your package. This will activate a menu item in the File Menu called "Save a copy of Package As...". Use this menu item. Save the package to SQL Server package store. In the last option, make sure you choose the "use SQL Server security" (last on list of options if I recall correctly).
Hope this helps - sorry it's just a workaround!
-
Hi,
I suppose the problem is with protection level you set in the package and that it is created with one account and executed with another (however the message you get is quite strange).
I normally use ProtectionLevel = EncryptSensitiveWithUserKey and when deploying it to the server (have to be done with the same account that was used to build) I select "Rely on server storage for encryption". I'm assuming you run pacakages stored in msdb database not as files.
If you need more information please read the following article:
http://support.microsoft.com/kb/918760
Jakub
-
Hi,
We have prove it and the SSIS continue failing.
We are using the Polyserver technology, and the SISS are in one server and the database is in other server.
The curious is that we have a lot ot SSIS and only one doesn't work, the rest of the SSIS work perfectly, and we don't see any difference between the ones that are working and the one that fails.
The protection level are the correct one and the user that execute this jobs-> SSIS is the dir\sql_agent_proxy_usr, that is administrator of the machine.
Some new idea?
-
Looks to me like a problem with security, have a look at the account in which SQL agent starts with, if you are on a domain try using a dedicated domain account, otherwise use a local admin user for SQL server.
Are you also running the package as a dtsx package, another option might be to run the job as a CmdExec job and using dtexec.exe 'Full Command Line package name'
Let us know how it goes.
-
Yes, we had had used the option 'Full Command Line package name', and using it, the dtsx works correctly. This could be a solution, but we would need to execute the package using the "SQL Server Integration Services package" option because this is the way that the other SSIS's work, using the same local admin user for SQL server with administrator permissions.
Thanks for your help!!
We are investigating the case, We will let you know if we fing a solution.
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply