Package would not open in BIDS or run from DTExec when objects with expressions existed in the design

Package would not open in BIDS or run from DTExec when objects with expressions existed in the design

Rate This
  • Comments 2

 

The Funny Symptom

This was a very strange case. Only one SSIS package would not load. All the other packages we had loaded fine. It would fail to open in BIDS, and failed when running it from the DTExec.exe command line with similar errors.

The one failing package has an expression on the DataReader source [Source].[SqlCommand] property, which references a string concatenated with a variable. Looks like this exercised a source code path that needed to find a dll but could not find it.

The Errors

{

Error: 2010-07-02 13:25:54.01
   Code: 0xC0010018    
   Source: {7863B209-97D8-4865-967B-31BD90ED03C3}     
   Description: Error loading value "<DTS:PropertyExpression xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Name="[Source].[SqlCommand]">"select * from MyTable" from node "DTS:PropertyExpression". 
End Error 

Error: 2010-07-02 13:25:54.02
   Code: 0xC0010018
   Source: {BC842E99-5EC7-4FD0-8540-4EC8AC4FA186}
   Description: Error loading a task. The contact information for the task is "Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails.
End Error

Error: 2010-07-02 13:25:54.03
   Code: 0xC0010021
   Source: 
   Description: Element "{740CF2E9-5D70-4D43-9299-0B051290A516}" does not exist in collection "Executables".
End Error

Error: 2010-07-02 13:25:54.03
   Code: 0xC0010018
   Source: 
   Description: Error loading value "<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" IDREF="{740CF2E9-5D70-4D43-9299-0B051290A516}" DTS:IsFrom="0"/>" from node "DTS:Executable".
End Error


Error: 2010-07-02 13:25:54.03
   Code: 0xC0010018
   Source: 
   Description: Error loading value "<DTS:PrecedenceConstraint xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></" from node "DTS:PrecedenceConstraint".
End Error


Could not load package "C:\Folder\MyPackage.dtsx" because of error 0xC0010014.
Description: 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.
Source:
Started:  1:25:51 PM
Finished: 1:25:54 PM
Elapsed:  2.282 seconds

}

 

Our Methodology:

We used  Process Monitor to trace the system with an “Include” filter on the [process name] = "DTExec.exe” to see which registry keys and files were being touched when the failure occurred.

Process Monitor shows a funny thing when loading DTSPipeline.dll. The (default) value searched for below under Typelib for win32 key appears missing. Many times in process monitor, a single NAME NOT FOUND is usually OK because it finds it in another folder or reg key, but if its not found at all, its a bad sign.

This threw us for a curve ball because DTSPipeline.dll was picked up successfully prior in execution. Only the Expression code path needed to reference the TypeLib registry keys, but the other code paths did not need that key.


More information on Typelib COM registry keys can be found at
http://msdn.microsoft.com/en-us/library/ms688573(VS.85).aspx

This was the telltale sign:  RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

We checked the registry and these two keys were missing. This was a 64-bit machine, so there are the 32-bit keys and the 64-bit equivalent keys:
HKEY_CLASSES_ROOT\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32
HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32
 
Context of the output – find the needle in the haystack.
{

4:23:39.0671671 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B} NAME NOT FOUND

4:23:39.0672086 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B} SUCCESS

4:23:39.0672478 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B} SUCCESS Query: Name

4:23:39.0672906 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 NAME NOT FOUND

4:23:39.0673328 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 SUCCESS

4:23:39.0673988 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 SUCCESS Query: Name

4:23:39.0674539 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 NAME NOT FOUND

4:23:39.0675518 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0676469 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0676934 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0677418 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0677874 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0678271 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0678671 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0679017 PM devenv.exe 6740 9612 RegCloseKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0679283 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 SUCCESS Query: Name

4:23:39.0679695 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 NAME NOT FOUND

4:23:39.0680079 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0680514 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0680944 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0682244 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0682760 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0683402 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0684159 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0684778 PM devenv.exe 6740 9612 RegCloseKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0685203 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 SUCCESS Query: Name

4:23:39.0686017 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 NAME NOT FOUND

4:23:39.0686395 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0686795 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0687176 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0687562 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win32 NAME NOT FOUND

4:23:39.0687835 PM devenv.exe 6740 9612 RegQueryKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS Query: Name

4:23:39.0688229 PM devenv.exe 6740 9612 RegOpenKey HKCU\Software\Classes\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0688635 PM devenv.exe 6740 9612 RegOpenKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0\win16 NAME NOT FOUND

4:23:39.0689122 PM devenv.exe 6740 9612 RegCloseKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0\0 SUCCESS

4:23:39.0689607 PM devenv.exe 6740 9612 RegCloseKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B}\1.0 SUCCESS

4:23:39.0690063 PM devenv.exe 6740 9612 RegCloseKey HKCR\Wow6432Node\TypeLib\{BB92AA81-0996-4768-BE15-E47433F0204B} SUCCESS

}
 

Solution…

It’s similar to http://support.microsoft.com/kb/919224 but with a different dll. Not sure how dtspipeline.dll got unregistered – that’s odd.

Register the dtspipeline.dll and check the registry key to make sure the Win32 key gets populated:

%windir%\syswow64\regsvr32 "C:\Program Files (x86)\90\DTS\Binn\DTSPipeline.dll"
%windir%\system32\regsvr32 "C:\Program Files\90\DTS\Binn\DTSPipeline.dll"
Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • I was having errors that were nearly identical to what you listed above, and I was able to follow your methodology to find out that ExecPackageTask.dll was no longer registered on my dev server.  Thanks for your detailed article.

  • Thanks.. Your solution works perfectly

Page 1 of 1 (2 items)