Issue when running SQL Server 2005 SSIS Package on Remote Server

Question

hi,

i created a SSIS package on my development machine which simply: 1) Deletes records from weak table in database. 2) Loads file from filesystem into weak table in database. 3) The package is called from an asp.net page

When i run the package manually on my machine it works, when the protection level is EncryptSensitiveWithUserKey. When i deploy i change the protection level to 'DontSaveSensitive'.

When i run the web application , i get this error - "An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client".

Unfortunately i don't have much access to the SQL Server 2005 instance on the remote server to run it manually.

When i deploy the package, i include an xml config file and include the password in the connectionstring property.

Also i've turned on logging, but cannot see anything written to Event Log or SQL Server. Can someone pls refer me to log table that SSIS writes to? I've checked in MSDB. Is it sysdtslog90?

Can someone please give me some pointers to what i can use to troubleshoot this.

regards

Kojo

Answer 1

Have you checked the permissions of the user account that is calling the SSIS package to make sure they have adequate privileges to all tables/resources involved?

The ASP.Net Worker Process is typically setup to run under the Network Service account, or even the user on the page (if impersonation is turned on) and they don't have proper authority to access the tables specified.

Answer 2

The table name for logging is sysdtslog90. It will be in the database that matches the database for the connection you specified in the logging. Also, you must have picked the Sql Server logging provider to use that table. Once you track down this table and look at it, you should be able to see the messages that were sent when the application failed.