Problems
Due to a bug in SQL Server, a SQL Agent jobstep for executing an SSIS package that has a DateTime parameter, can get unusable on a Windows server with European Regional Settings, and more specific, a dd-mm-yyyy dateformat.
The jobstep is stored with the datetime parameter value in a d-m-yyyy hh:nn:ss format.
This gives two problems:
- During execution the following error occurs: String was not recognized as a valid DateTime
- When the jobstep is opened for editing, the same error occurs: “Microsoft SQL Server Management Studio String was not recognized as a valid DateTime. (mscorlib)”
Solution
Correct the jobstep in the msdb database: convert the datetime to a yyyy-mm-dd format, which does not give this error when the job is executed or when the jobstep is opened for editing.
You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
--\
---) Author: Hans Michiels
---) With this script you can correct a SQL Agent jobstep that has a DateTime parameter
---) and would otherwise be uneditable due to a bug in SQL Server.
---) You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
--/
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
UPDATE js
SET command = REPLACE(js.command, expr3.original_date, expr5.corrected_date)
FROM [msdb].[dbo].[sysjobsteps] AS js
CROSS APPLY ( SELECT
CHARINDEX('(DateTime)', js.command) AS [datetime_pos]
) AS expr1
CROSS APPLY ( SELECT
CHARINDEX(';”\”', js.command, expr1.datetime_pos + 1) AS [start_pos],
CHARINDEX(' ', js.command, expr1.datetime_pos + 1) AS [end_pos]
) AS expr2
CROSS APPLY ( SELECT
SUBSTRING(js.command, expr2.start_pos + 4, expr2.end_pos - start_pos - 4) AS [original_date]
) AS expr3
CROSS APPLY ( SELECT
CASE
WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 4, 1) = '-'
THEN '0' + SUBSTRING(expr3.[original_date], 1, 1) + '-0' + SUBSTRING(expr3.[original_date], 3, 6)
WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
THEN '0' + expr3.[original_date]
WHEN SUBSTRING(expr3.[original_date], 3, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
THEN SUBSTRING(expr3.[original_date], 1, 3) + '0' + SUBSTRING(expr3.[original_date], 4, 6)
ELSE expr3.[original_date]
END AS [fixedwidth_date]
) AS expr4
CROSS APPLY ( SELECT
CONVERT(CHAR(10),
CONVERT(DATE, SUBSTRING(expr4.[fixedwidth_date], 7, 4)
+ SUBSTRING(expr4.[fixedwidth_date], 4, 2)
+ SUBSTRING(expr4.[fixedwidth_date], 1, 2), 112)
, 120) AS [corrected_date]
) expr5
WHERE js.subsystem = 'SSIS'
AND expr1.datetime_pos > 0
AND expr2.start_pos > 0
AND expr2.end_pos > expr2.start_pos
AND SUBSTRING(expr4.fixedwidth_date, 3, 1) = '-'
AND SUBSTRING(expr4.fixedwidth_date, 6, 1) = '-'
Download the script here.
Known limitations
This script works only for jobsteps with only one DateTime parameter.
Conclusion / Wrap up
With this script you can correct a SQL Agent jobstep that has a DateTime parameter and would otherwise be uneditable due to a bug in SQL Server.
You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.