You receive an error message when you try to load an SSIS package that contains a DateTime type variable in SQL Server 2005 (918038)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Bug #: 428373 (SQLBUDT)

SYMPTOMS

Consider the following scenario. In Microsoft SQL Server 2005, you try to load a SQL Server Integration Services (SSIS) package. The SSIS package contains a DateTime type variable. You define the period (.) character or another user-defined character as the date separator in the Windows locale setting before you create the SSIS package. In this scenario, you receive an error message.

Note The date separator in some locales uses the period (.) character by default, such as the German locale.

Depending on how you load the SSIS package, you receive the following error messages.

You try to modify the SSIS package in SQL Server Business Intelligence Development Studio

When you try to modify the SSIS package in SQL Server Business Intelligence Development Studio, you receive the following error message:
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.
When you click OK, you receive the following error message in the package designer:
Microsoft Visual Studio is unable to load this document:
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::LoadFormXML fails.

You execute the SSIS package

When you try to execute the SSIS package in the Execute Package Utility dialog box, you receive the following error message:
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.
When you click OK, you receive an error message that resembles the following:
Error: 2006-04-14 21:21:55.05
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:VariableValue xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:DataType="7">30.12.2000</DTS:VariableValue>" from node "DTS:VariableValue".
End Error
Error: 2006-04-14 21:21:55.10
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:Variable xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnl" from node "DTS:Variable".
End Error
Could not load package Path\PackageName.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:

CAUSE

This problem occurs because the value of the DateTime variable is saved in string format when you save the SSIS package. However, the string representation of the DateTime variable is based on the date format that is specified in the Windows locale. Therefore, when you try to load the SSIS package on a computer that has an incompatible locale date format, the operation fails.

WORKAROUND

To work around this problem, use one of the following methods.

Method 1

Manually update the DateTime type string in the SSIS package to use the slash (/) character as the date separator. To do this, follow these steps:

Note If you store the SSIS package in the SSIS package store or in SQL Server, you can export the SSIS package to a file, update the DateTime type string in the package, and then import the package.
  1. In Notepad, open the SSIS package file.
  2. Locate the following DTS variable tag:

    <DTS:VariableValue DTS:DataType="7">

    The value that follows this tag is the DateTime type-formatted string that represents the DateTime variable.

    Note The number of DTS variable tags in the package is equal to the number of variables that are defined in the package.
  3. Update the DateTime type formatted string that follows the DTS variable tag to use the slash (/) character as the date separator.
  4. Repeat steps 2 and 3 for every DateTime type-formatted string in the SSIS package file.
  5. Locate the following DTS property tag:

    <DTS:Property DTS:Name="CreationDate" DTS:DataType="7">

  6. Update the date part of the value that follows the DTS property tag to use the slash (/) character as the date separator.
  7. Save and then close the SSIS package file.

    You can load the SSIS package successfully.

Method 2

Update the date separator in the Windows locale setting to be identical to the date separator of variables in the SSIS package file. To do this, follow these steps:
  1. In Notepad, open the SSIS package file.
  2. Locate the following DTS variable tag:

    <DTS:VariableValue DTS:DataType="7">

    The value that follows this tag is the DateTime type-formatted string that represents the DateTime variable. Note the date separator.
  3. Close the SSIS package file.
  4. In Control Panel, open Regional and Language Options.
  5. Click Customize.
  6. In the Customize Regional Options dialog box, click the Date tab.
  7. Update the value of the Date Separator box to be identical to the date separator that you noted in step 2.
  8. Click OK to save the change, and then close the Customize Regional Options dialog box.
  9. Click OK.

    You can load the SSIS package successfully.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Modification Type:MajorLast Reviewed:4/28/2006
Keywords:kbsql2005repl kbExpertiseAdvanced kbtshoot kbprb KB918038 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.