Executing Package From Web Service

Hi,

I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.

If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.

Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.

Many thanks in advance,

Grant

Are any exceptions being thrown within the WS code?

Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?

Do you get any error events?

You really need to have decent error handling and make good use of the events from those methods to find out what is going on.

Without an error message we are in the dark out here.

|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.

I suggest you to verify the permissions given to this user into the DB tables and also read this articles:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

regards|||Hi Darren,

You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.

The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".

If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.

In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.

I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.

Thanks,

Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.

What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?

Cheers,

Grant|||

Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)

Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)

I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.

Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.

|||hi Darren,

Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.

It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?

Many thanks for your help,

Grant