Execute SSIS package through ASP.NET Web Service - different problem

Hi all,

There was already several posts about executing packages programmatically from the context of Web Services. I have decided to follow Web Service approach because we were already calling packages programmatically through .NET. So far I have been able to clear all security configuration obstacles as I am able to load package and call Execute but mysteriously package doesn’t execute. I am reading StopTime, StartTime and ExecutionDuration properties after call to Execute and evidently package doesn’t execute because StartTime and EndTime are identical and ExecutionDuration is 0 but no error is raised or logged anywhere but package return status is failure)!

We are using SQL Server 2005 with Service Pack 2 on Windows 2003 R2 Enterprise, .NET Framework 2.0. The Domain Account that has Admin privileges on both servers is calling Web Service. DefaultAppPool uses Network Service and Anonymous access is enabled. Network Service is also member of the sysadmin role.

I am sure root of the problem is still in the Security but I am not getting any clue because of lack of any error. Does anyone has any suggestion? Thanks in advance.

Sinisa Catic

Is logging enabled on the package? If so, are you getting any error messages?

If not, enable a text file log for the package so you can see if any errors are getting logged.

|||

Hi, This is not a problem with package execution. All my packages have logging enabled but there is no possibility to log anything because package never started. The problem is strictly with the Microsoft.SqlServer.ManagedDTS and Execute() method and for me this is a bug. My understanding is that calling Execute method will cause SQL Server Integration Services to spawn another process. If this is not possible for one or another reason as is the case when call is made by the remote client that doesn't have SSIS installed or because of the issue of not propagating security context, Execute method reports package execution failure, which is misleading. I am hoping that someone from Microsoft Team can finally post some clarifications and guidelines because this is a thorny issue for many people.

Thanks.

Sinisa