We have been getting a strange error with this particuar package that we've been running a scheduled job, from Integration Services and havn't been able to figure it out. It works fine when running from Visual Studio but not so much when we are scheduling it. Below is all of the OnErrors that we are seeing. Anybody have any insight into these errors? Its SQL 2005 SP1 on Windows 2003 SP1 Server.

Thank you!






An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

The PrimeOutput method on component "INVOICES" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Thread "SourceThread0" has exited with error code 0xC0047038.

Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Thread "WorkThread0" has exited with error code 0xC0047039.

Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Thread "WorkThread1" has exited with error code 0xC0047039.

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

component "dimProduct Lookup" (12442) failed the pre-execute phase and returned error code 0xC020824E.
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

component "dimProduct Lookup" (12442) failed the pre-execute phase and returned error code 0xC020824E.
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

component "dimProduct Lookup" (12442) failed the pre-execute phase and returned error code 0xC020824E.

If the person who created the job is not an administrator, I suggest you get
an administrator to create the job and see whether the error still occurs.
It is very likely that this is a security error. The problem can still occur
if the job creator is a sysadmin but the SQL Server Agent service has no
access to some of the resources required to execute the package [e.g. SQL
Server AGent is running with a Local System account and it needs to access a
domain resource].

You need to create a proxy and specify it in the Run As box of your job
step. Steps to create a proxy are:
1) Create a credential and give it a Windows account. The Windows account
must have access to all the resources required to run the package. Best
choice is the Windows account of the person who interactively successfully
ran the package. Go to Security/Credentials folder in SSMS to do this.
2) Create a proxy and associate it with the credential you just created.
Specify the principals for the proxy. This specifies the logins/users/roles
who are allowed to use the proxy.
3) Make sure that the user creating the job is in one of these three msdb
database roles: SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorRole
(permissions increase in this order).
4) Now create the job and specify the proxy created at Step 2 in the Run As
box of the job step.(The job step type must be set to SQL Server Integration
Services Package).

Note that this is the only supported way of scheduling SSIS packages.

Hope this helps.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email alias: charles
email domain: kangai.demon.co.uk






"James Wuerflein" wrote: