THE PACKAGE FAILED TO LOAD DUE TO ERROR 0XC0011008

Background: While working on a small SQL Server Integration Service project I ran into this problem.

I first developed the packages locally on my laptop using 2008 versions of SQL Server and SSIS. After creating two separate packages, one for the import and one to clean out the tables as I was testing the import successfully I knew I was going to have to update some the components in the package after moving it to the remove development box used by the dev team.

After copying the files from my laptop to the remote server, running them, making a few changes here and there and getting them to work like they were designed I reported in our morning standup meeting I was 95% complete. I just need to document the information for turnover and wanted to confirm all was still working. When this error popped up after another change and tried to redeploy.

The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.



I was confused. What change from yesterday to today?

My code had not changed except for the minor update I just performed. So, I reverted the change and tried to deploy again. Same error. I then went to Google to see what information was out on the web for this error. Surely someone had run into this issue before. After finding several posts in forums by others dating as far back as 2005 I did not find anything to indicate what was wrong or what else to investigate. I sent a Tweet to some SSIS people and to the #ssis list. I also posted the error in several forums still thinking someone has run into this before.

On Tweet mentioned the Security Protection level. It was set to the default of EncryptSensutiveWithUserKey. I had not changed that, (at least not that I remembered). So I changed it to DontSaveSensitive just to see if somehow I had changed it in my sleep. Still no love.

Two people respond with this link;

http://tinyurl.com/yazwg58

No love here. I was working remotely but that was the only similarity.

For some reason I tried to open the dtsx file in the Deployment folder. After all I had nothing to lose. That is when I got these errors;

Error 1: Error loading CleanStagingTables.dtsx: The version number in the package is not valid. The version number cannot be greater than current version number.

Error 2: Error loading CleanStagingTables.dtsx: Package migration from version 3 to version 2 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”.

Error 3: Error loading CleanStagingTables.dtsx: Error loading value “<DTS:Property xmlns:DTS=”www.microsoft.com/SqlServer/Dts” DTS:Name=”PackageFormatVersion”>3</DTS:Property>” from node “DTS:Property”.

Error 4: Error loading ‘CleanStagingTables.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails.

Aha! Could these be the underlying issue(s)? At least I had a different path to look down.

After searching again I found this post;

http://tinyurl.com/y8z9bud

According to the blog post the problem is this;

Reason for the error: Old version of the DTEXEC is picked up by SQL Server instead of the new one. That means, the exe shipped with 2005 is picked up when it is expected to use the one shipped with 2008. So, obviously this happens when SQL Server 2008 is running along with SQL Server 2005 on the same machine. As a result of this, we end up with two versions of DTEXEC executables. One residing in SQL Server 2005 path (“C:\Program Files\Microsoft SQL Server\90\DTS\Binn”) and the other in SQL Server 2008 path (“C:\Program Files\Microsoft SQL Server\100\DTS\Binn”).

There are three choices to fix this problem.

1. Hard code the path of SQL Server 2008’s DTEXEC while calling the SSIS package as shown below.

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe /F “D:\MyFolder\MyPackage.dtsx”

2. Rename the old exe in the 2005 path to a different name (Example:- C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC_Old.exe)

3. Go to PATH environmental variable and edit it in such a way that “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” path appears well before the “C:\Program Files\Microsoft SQL Server\90\DTS\Binn” path.

I first tried choice #2. Renaming the file. Yup you guessed it. No love here.

I then proceeded to the choice #3. Again, no love. I don’t know why I never tried choice #1. Other than figuring if renaming the file and changing the ordering in the path environmental variable didn’t work why would the first.

My next move will not work for the majority of you. I uninstalled SQL 2005. We were not using it on this server and the only project on it was the one I was working so. After uninstalling I reapplied SP1 to SS 2008 just in case. This SS 2005 gone this of course caused other issues. (say it isn’t so).

The next issue was the dtsx files and the deployment manifest files were no longer associated with any program. Good and bad. Good because I knew I had finally broken the chain linked to SS 2005. Bad because now I couldn’t use my files! AHHH! Well not really. When I clicked on the manifest file the open with dialog box opened and once I associated the files with the SS 2008 versions all worked as designed. Dtsx files should be associated with Integration Services Package and the manifest files associated with DTSInstall.exe.

After getting this all working I went back and looked at the log tables to see if I could find anything to point to why this started after I had deployed the package without error.

Sure enough, I found this note in the logs.

Product: Microsoft SQL Server 2005 – Update ‘Service Pack 3 for SQL Server Database Services 2005 ENU (KB955706)’ installed successfully.

I know I had applied SP1 to SQL Server 2008 before I even started the work.  I actually had that on my laptop because I had recently gotten a new hard drive and had to reinstall everything.

I don’t recall clicking on SP3 to download/install but I have to take the responsibility since I can’t blame it on anyone else. The only thing I can think is that when SP3 got installed it make SQL 2005 the default server again and that is why I was getting the version out of sync error.

I heard a quote last night from comedian Ron White, “You can fix a lot of things, (my injection here) TSQL code, bugs, but you can’t fix stupid. Stupid is for-ever”

Reference

DBCC CHECKDB :A database snapshot cannot be created because it failed to start.

Disk space issues during DBCC CHECKDB

Sometimes an issue arises when the hidden database snapshot runs out of space. Because it’s implemented using alternate streams of the existing data files, the database snapshot consumes space from the same location as the existing data files. If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow. In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error. An example of this is shown here (the errors can vary depending on the exact point at which the database snapshot runs out of space):

DBCC CHECKDB ('SalesDB2') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Msg 1823, Level 16, State 1, Line 5
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5128, Level 17, State 2, Line 1
Write to sparse file 'C:\SQLskills\SalesDBData.mdf:MSSQL_DBCC20' failed due to lack of disk space.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'SalesDB2', an error occurred at log record ID (1628:252:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

In this case, the solution is to create your own database snapshot, placing the snapshot files on a volume with more disk space, and then to run DBCC CHECKDB on that. DBCC CHECKDB recognizes that it’s already running on a database snapshot and doesn’t attempt to create another one. If a database snapshot was created by DBCC CHECKDB, it’s discarded automatically after the consistency-checking algorithms are complete.
While it runs, DBCC CHECKDB creates a database snapshot (if needed) and suspends the FILESTREAM garbage collection process. You might notice garbage collection run at the start of the consistency checking process; the creation of the hidden database snapshot starts with a checkpoint, which is what triggers FILESTREAM garbage collection. The suspension of garbage collection activity allows the consistency-checking algorithms to see a transactionally consistent view of the FILESTREAM data on any FILESTREAM data containers

Alternatives to using a database snapshot

A database snapshot isn’t required under the following conditions.

• The specified database is a database snapshot itself.
• The specified database is read-only, in single-user mode, or in emergency mode.
• The server was started in single-user mode with the –m command-line option.

In these cases, the database is already essentially consistent because no other active connections can be making changes that would break the consistency checks.
A database snapshot can’t be created under the following conditions.

• The specified database is stored on a non–NTFS file system, in which case a database snapshot can’t be created because it relies on NTFS sparse-file technology.
• The specified database is tempdb, because a database snapshot can’t be created on tempdb.
• The TABLOCK option was specified.

If a database snapshot can’t be created for any reason, DBCC CHECKDB attempts to use locks to obtain a transactionally consistent view of the database. First, it obtains a database-level exclusive lock so that it can perform the allocation consistency checks without any changes taking place. Offline consistency checks can’t be run on master or on tempdb because these databases can’t be exclusively locked. This means that allocation consistency checks are always skipped for tempdb (as was usually the case with SQL Server 2000, too). This also isn’t possible if the database is an Availability Group replica, in which case error 7934 is reported if the database snapshot creation fails.
Rather than wait for the exclusive lock indefinitely (or whatever the server lock timeout period has been set to), DBCC CHECKDB waits for 20 seconds (or the configured lock timeout value for the session) and then exits with the following error:

DBCC CHECKDB ('msdb') WITH TABLOCK;
GO
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

If the lock was acquired after the allocation checks are completed, the exclusive lock is dropped and table-level share locks are acquired while the table-level logical consistency checks are performed. The same time-out applies to these table-level locks.
One way or another, DBCC CHECKDB obtains a transactionally consistent view of the database that it’s checking. After that, it can start processing the database.





Reference:Microsoft SQL Server 2012 Internals

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Try this Solution : 

Nothing could be found in SQL Error logs.Check the service account of SQL Server, find out whether it has enough permissions to run SQLServer. If you are not able to change the Service acccounts permissions immediately and wants to come out of this Service Pack Installtion errors.  Change Service account for SQL Server to Local System. SQL Server DB Engine Service will be started and the build number will be updated too. Can revert your SQL Servers Service account back to Original from Local System.

Checking Email log in SQL Server

--SENT MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC

--FAILED MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

--ALL MESSAGES – REGARDLESS OF STATUS
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC