다음을 통해 공유

Date format Issues in SSIS Packages between MM/dd/yyyy & dd/MM/yyyy

  • 아티클

In this blog, I would like to address one of the interesting issues that I came across recently with the SQL Server Integration services packages related to the inconsistencies noticed with the Date format.

In this scenario, the SSIS packages were using a system variable @[System::StartTime], which was giving the value in format as '18/08/2016 13:33:11'.. wiz. dd/MM/yyyy format in the development machines. But when the SSIS packages were moved to the production server and configured under a SQL Agent Job, the SQL Agent jobs were failing with the following error message.

Error:

Msg 242, Level 16, State 3, Line 6

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 

Now in order to troubleshoot this issue, we tried to open this SSIS package using the SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) in the production environment. When we opened the package and looked into the date format for the @[System::StartTime], it was showing as '08/18/2016 13:33:11'. wiz. MM/dd/yyyy format. We collected a SQL Server Profiler traces reproducing the issue and we could clearly see that the SQL Server was throwing this error as a result of a mismatch between what is provided and what the SQL Server is expecting.

Exception: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Microsoft SQL Server
Now why do we see this inconsistency in the date format between 2 different machines for the same SSIS system date variable like @[System::StartTime].

The things that would come into picture here are:

Is it the same SSIS package used in both the environments? Yes, it was the exact same package.

But in order to verify this, we opened the SSIS packages using the VS SSDT/ BIDS environment and checked one of the related SSIS package properties.

1.SSIS package Locale ID property:

<DTS:Property DTS:Name="LocaleID">2057</DTS:Property>

   1       
Check the package property LocaleID and verify that they are set as per our need.

In our case it should be and it was set to English (United Kingdom)

b. Region Format setting:

In our development machine, the region settings (intl.msc) [Control Panel --> Region --> Format]

were set as below.

      2

      In our production machine, the region settings were set as below. 3

 

So this was causing the SSIS package variables to show the Date in the MM/dd/yyyy format in our production environment.

 

Now, after we had changed this setting back to English (United Kingdom), the SSIS packages in production environment using the SSDT/BIDS development tools were showing the variables in the expected dd/MM/yyyy format. Also the SSIS packages now started to execute fine without any issues using the SSDT/BIDS development tools.

 

When we tried to schedule the execution of the SSIS packages using the SQL Agent Jobs, it still failed with the following error message,

Error:

Msg 242, Level 16, State 3, Line 6

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 

Now, this seems interesting. Upon further investigation, we figured out that the SQL Server service account under which the scheduled SQL Agent Jobs are executed still had its Region settings as English(United States). So the changes that we had made under the Region setting at the machine level, holds good only for the logged in account using which the changes were made. It is not universally applicable to all the users profiles. i.e. The Region settings is based on and specific to the User profile alone.

 

So having understood this, we dropped and recreated the user profile on this machine for the SQL Server Agent service account at the machine level. This way we aren’t changing the SID associated with the user profile. After this, validated that the user profile’s region setting was set to English(United Kingdom). Then ran the SQL Agent job and it executed without any issues.

 

NOTE: You can also create a SQL Agent Proxy on a user account that has the region setting as English(United Kingdom) and still run the SQL Agent Jobs/ step using this proxy account.

 

Author:        Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Reviewer:    Sarath Babu Chidipothu - Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Comments

  • Anonymous
    November 30, 2017
    Thanks :) So simple but works.