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
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
No comments:
Post a Comment