April 11, 2024 at 11:23 am
We recently moved our production databases to a new server.
Since then, an SSIS package has changed its behavior. The package is doing row by row validation using a C# Script Task. The script is using the TryParse method on a string containing a date to test if it is a date and convert it into a date type variable.
All string dates are in dd/MM/yyyy format.
Since the migration date, the package has rejected any record containing a date that does not conform to MMddYYYY format instead.
The SQL servers have the same regional settings
I cannot recreate in Visual Studio debug on my dev PC as it parses correctly on this machine.
I have fixed this by changing to TryParseExact and specifying a date format, but I'd like to know what configuration item is responsible for setting the script task's regional settings and date format assumptions if no explicit format is provided.
The Job Step uses a proxy set to an AD Service Account to execute the package. This domain account was the same one used on the old server.
April 11, 2024 at 2:41 pm
If I am not mistaken, C# scripting allows you to pass more parameters to the datetime.tryparseexact() function. Needs more parameters, but that'll allow you to set the exact format for the date object.
Documentation on it:
Appears to have existed since at least .NET 5, so you should be good to use that on your server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply