Could not load the package "\MSDB\....."

Hi,

I have a intermittent problem with my ssis packages. I have a couple of packages running on a schedule with SQL Server Agent wich sometimes gives me the following errors in agent history:

Message
Executed as user: MyDomain\SQLAgentServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:05:00 Could not load package "\MSDB\MyPackageName" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 21:05:00 Finished: 21:05:00 Elapsed: 0.047 seconds. Process Exit Code 5. The step failed.

When the package does'nt fails it gives me the following message:

Message
Executed as user: MyDomain\SQLAgentServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:10:00 DTExec: The package execution returned DTSER_SUCCESS (0). Started: 21:10:00 Finished: 21:10:02 Elapsed: 1.312 seconds. Process Exit Code 0. The step succeeded.

The jobs moves data between the sql server that executes the code and a SQL 2000 database on another server. Does anyone recognize this error?

http://www.eggheadcafe.com/forumarchives/SQLServerdts/Nov2005/post24522953.asp

This is the same error, but not necessarily the same problem. Does your package reference tables that might need to be wrapped with [] or prefixed with the schema?

Could not load file or assembly SQLXMLBULKLOADLib

Greetings,

I'm in the process of migrating several SQL Server 2000 DTS packages to Integration Services packages. One of the old 2000 DTS packages used the SQLXML Bulk Loader component. In order to use the new SQLXML 4 COM object in my Script Task (to initiate the Bulk Loader using .NET code) I've used the tlbimp.exe tool to create a .NET wrapper DLL. I've placed the DLL in the appropriate directory (C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies), successfully added it to my project (with Intellisense working), but when I run the package it fails with the following error:

Could not load file or assembly 'SQLXMLBULKLOADLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Note: I also tried placing the DLL in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 with no prevail.

I've confirm the file exists (and any dependencies) in their appropriate locations. Has anyone else run into this? Any help is much appreciated.

Thanks, Shaun

i'm not sure if this resolves your issue, but have you considered using the ole db destination component to bulk load the data?|||Reason I leaned toward the SQLXML Bulk Loader class is because i have a ton of XML I'm downloading and want to leverage the performance of the XML bulk loader. I'm not aware of how the OLE DB Destination object can help me with this.|||

I've resolved this issue by using... well... what I'm supposed to use. I created a Data Flow task and implemented an XML Source (applying an XSD transformation), Data Conversion (to convert from Unicode) and Ole Db Destination. This successfully loads the data into the database from XML.

|||shaun,

i'm glad that you found a solution to your problem. as you probably already know, the ole db destination is designed to bulk load data.|||

Did you add it to the GAC? You need to add it to the framework folder for design-time only (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727) but at runtime it uses the GAC.

I think the better solution is to re-write to use the XML Source as this is very powerful and handles hierarchical data, but obviously this may not be ideal if you have a lot of packages and XMl formats.