Executing a SSIS Package from an ASP.NET page.

I am using the following code to try and execute a package from a asp.net page. The server has both SQL, SSIS, IIS and ASP.NET on it. The package runs fine from the SQL Management Studio. The Execute result from the web page is 'Failure'.

My questions are:
1. How do I catch errors to see exactly what is failing and why?
2. Is there a better way to execute a package using SSIS from asp.net?

Thanks,

Nathan

Sub ExecutePackage()
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

Dim testBool As Boolean = app.ExistsOnSqlServer("\\Import_Quotes", "povnet", "webapps", "password")
lblStatus.Text = testBool.ToString
pkg = app.LoadFromSqlServer("\\Import_Quotes", "povnet", "webapps", "password", Nothing)
pkgResults = pkg.Execute()

lblErrorOther.Text = pkgResults.ToString()
End Sub

> How do I catch errors to see exactly what is failing and why?

There are multiple ways:
1) Enable logging and configure package to log execution and error information to a file, event log or other log provider. This gives you a lot of information about package.
2) Implement IDtsEvents interface and supply it to Execute method. You'll get lots of information (in particular, all the error information) back.

The code seems fine.

By the way, the common problem with executing SSIS package from ASP.NET is user identify - the package is executed under account used by ASP.NET service, which might not have permissions to access all the data sources.|||Michael,

Can you help me with some code samples of implementing IDtsEvents and using it in the Execute method?

Also, I will check BOL but any info you can provide on enabling logging on a package would be nice.

Thanks for your quick response.

Nathan|||Implementing IDtsEvents is simple - subclass from Microsoft.SqlServer.Dts.Runtime.DefaultEvents class and override the methods corresponding to events you are interested in, most probably OnError. Pass instance of your class to Execute call, your OnError method will be called whenever an error occurs during execution.

To enable logging, right click the package main control flow in designer, select Package Configurations and follow the wizard.

Note - to edit configuration the package should be part of SSIS project, this currently does not work for packages edited "standalone" (unfortunately, this problem was found too late). I'm fixing it for SP1.|||

Here are some of the errors. I believe it is a permissions problem. I am importing an Excel spreadsheet to the database. The spreadsheet exists on the same server as the database. I have given NTFS permissions to NETWORK SERVICE to access the file. What other types of permissions do I need to grant the NETWORK SERVICE account?

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: The AcquireConnection method call to the connection manager "POVNET SQL Database" failed with error code 0xC0202009.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1071611876

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

AND

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 10/28/2005
Time: 1:08:31 PM
User: NT AUTHORITY\NETWORK SERVICE
Computer: POVNET
Description:
Event Name: OnError
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.

Operator: NT AUTHORITY\NETWORK SERVICE
Source Name: Import Quote Pricing
Source ID: {55F39A44-4089-4C2E-9267-33332166020D}
Execution ID: {68483D6C-895B-450A-ABA5-9E50E333D4C9}
Start Time: 10/28/2005 1:08:31 PM
End Time: 10/28/2005 1:08:31 PM
Data Code: -1073450985

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.|||The message
Message: component "SQL Server Destination" (953) failed validation and returned error code 0xC020801C.
indicates an error with SQL Server destination, not the Excel spreadsheet.

Also see the error the connection manager "POVNET SQL Database" - well, only you know where this connection manager points to.|||When I am in SQL Management Studio and run the Package, it runs without errors. I understand that the error is realted to the SQL Destination. When I execute this command from ASP.NET, which username is used for the operation?

Thanks,
Nathan|||

Do you use integrated NT authentication for SQL Destination? Then account ASP.NET is running under, usually Network Service (it authenticates as Domain\Computer$ to remote servers).

|||I can get my package to execute successfully now using ASP.NET.

Can you help me out on implementing IDTSEvents using asp.net and vb.net? I know you mentioned that it is simple, however, I am a beginner and am not sure how to tackle it.

I am looking to collect information on the results of the package like success or failure and also other things like varibles from the package like row count etc.

Thanks for your help on this.

Nathan|||

Microsoft.SqlServer.ManagedDTS assembly has a class DefaultEvents (in namespace Microsoft.SqlServer.Dts.Runtime). You just subclass this type and override the methods corresponding to the events you are interested in (e.g. OnError).

Then you pass an instance of your class to package.Execute method, like
package.Execute(null, null, myEvents, null);

|||Michael,
As you know, the word 'simple' is totally relative to a persons knowledge base. In my case at least, it is very limited in the program development arena so could you please direct me to documentation or an example of how to implement the event handler that you mentioned?

Thanks,
Mark.