Maybe someone else has asked similar question. But I still can't solve my problem after looking some threads in this forum.
The package runs fine through VS 2005. After imported into MSDB, it still does well when I start it in Integration service directly. But the packgae never achieved under SQL Agent.
Acturaly,agent can run the package,but always fail in one task. It's an 'Execute Process Task ', I use 'winrar.exe' to extract file from a mapped drive to the local disk. The Agent service account is a domain user who has the read permission on the mapped drive,also has been grant sysadmin on the SQL istance. So I think the right of the Agent account is enough.
Thanks for your help
What's the error?
|||I am having a similar problem.
I have created an Integration Package in BI and deployed it to the SQL Server. I can run the package fine from Integration Services but when I run it as a job I am getting the following message. I have looked up the error but there is no additional information.
TITLE: Microsoft.SqlServer.SmoEnum
Failed to retrieve data for this request.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
BUTTONS:
OK
|||In the package log file,there are no information about the failed task in detail. On error message just show that return value is 1 but 0 is expected.
Now,I know the key is authority. I copy the compressed files from the mapped drive to a local disk drive and change the drive name in the expression of the task,then everything is ok under SQL Agent.
So what about the Agent service account, I am still confused. The account I use can do anything on the local system, I even ask the NetworK Admin grant me the write permission on the mapped drive,but the job still failed when execute the "winrar" process task. As I have said, the pre-executed tasks run fine under Agent. In debug mode or in integration services instance of object explorer, there are no error at all.
