I created a dtsx package which will fetch data from Excel file and insert them into respective SQL Server table. When i run the package from SQL Server Business Intelligence Development studio it works fine. When i run package from Command prompt also it works fine. But when we create it as a SQL Server Agent Job, It says the following error.
The package execution failed. The step failed.
I found this article from Microsoft Support team for all scenarios while execution of Job failed.
http://support.microsoft.com/kb/918760
It was clear, in my case it was permission issue, So i kept looking for blogs on such case. Managed to find this blog which helped me to solve the issue.
Following are two points which i believe, did the trick for me...
1. Under SQL Server Configuration Manager:
* Right click SQL Server Agent(MSSQLSERVER)
* Click Properties
* Select radio button this Account, Gave the User Name & Password (the user name with which i have created the .dtsx package )
2. In Computer Management(Right click on my computer & Click Manage)
* Under Local Users and Groups
* Under Groups
* Right Click SQLSERVER2005SQLAGENTUSER$YourServerName$MSSQLSERVER
* In Properties add the user name with which i have created the .dtsx package
These steps solved my problem, Hope this should be helpful :)
Regards
Mohammed Fauzi
