posted on Wednesday, April 26, 2006 11:54 AM by SimonSabin

Don't apply SQL 2005 Service Pack 1 if you use SSIS

If you already use SSIS and you use date variables do not install service pack 1 without amending your packages

There has been change to the way that dates are stored in packages, it appears it is to do with locales.

Simply put a variable that is defined as 1 march 2006 in a pre SP1 package will be treated as 3 january 2006 in SP1.

The solution is to open your package correct the date and redploy. I suspect this will apply to configuration files and dates being passed in on the command line as well. In this case I believe you need to specifiy your dates in american format (mm/dd/yyyy).

Why dates aren't stored in ANSI format is beyond me anyway :(

I will bring you an update when I get one :)

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=474c85d2-57f9-4219-9037-f2ee34948612

Comments

# SSIS: Warning - don't install SP1 for SSIS until you've read this. @ Wednesday, April 26, 2006 2:30 PM

For those of you that have not read Simon Sabin's latest blog post you need to be aware of...

Anonymous

# SSIS: Warning - don't install SP1 for SSIS until you've read this. @ Wednesday, April 26, 2006 2:30 PM

For those of you that have not read Simon Sabin's latest blog post you need to be aware of...

Anonymous

# re: Don't apply SQL 2005 Service Pack 1 if you use SSIS @ Wednesday, April 26, 2006 4:40 PM

This is only really an issue for users on non-US formats, or organisations that pass packages between date formats. It is not often that you need a saved variable in a package, but it is still pants, and shocking that the dates are not in 8601 format, this is XML after all! I have tried to explain in more detail here http://wiki.sqlis.com/default.aspx/SQLISWiki/DateConversions.html

Darren Green

# re: Don't apply SQL 2005 Service Pack 1 if you use SSIS @ Thursday, April 27, 2006 6:48 AM

just another way to make us an american state - after replacing all the s's with z's now they're going to make us use backward dates.

colin leversuch-roberts

# Interesting Finds (oS edition) 2006/04/26 @ Saturday, April 29, 2006 5:24 PM

Anonymous

# Interesting Finds (oS edition) 2006/04/26 @ Saturday, April 29, 2006 5:33 PM

Anonymous