May 16, 2012 at 6:09 am
HI
I have a SSIS package that is loading a fact table that I have just inherited
The warehouse load has failed with the error : Conversion failed when converting date and/or time from character string(validation failure)
So I open the package and run it manually in production and it works fine, so Im thinking what gives?
So I re-create the error but the error only fires when I execute the package from the agent
Here is the code that is failing under the agent:
SELECT BrandKey,
MemberTypeKey,
CONVERT(VARCHAR,CONVERT(DATE,dateJoined),112) as DateJoinedKey,
CONVERT(DATE,dateJoined) as DateJoinedValue,
Listkey,
CustomerKey,
MemberID_ as MemberID
FROM me_migration
WHERE RK = 1
If I add the 103 below it all runs fine:
SELECT BrandKey,
MemberTypeKey,
CONVERT(VARCHAR,CONVERT(DATE,dateJoined,103),112) as DateJoinedKey,
CONVERT(DATE,dateJoined,103) as DateJoinedValue,
Listkey,
CustomerKey,
MemberID_ as MemberID
FROM me_migration
WHERE RK = 1
My question is really what do you think is going on here? Does SSIS parse t-sql differently when running under the agent? Also this code has been live for months and suddenly this week its started failing on my first week on the job(typical). Of course people are claiming nothing has changed. Anyway any thoughts from the community would be appreciated
thanks
Chris
May 16, 2012 at 8:18 am
The problem is that how date strings are interpreted are a user/connection settable option, so the SQL Agent's login/connection may have something set differently than your personal account.
Consider the following string: "02/03/2012"
What date does it represent? Is it February 3rd or is it March 2nd? The answer is that it depends on those aforementioned settings. (this is one of several reasons why dates should never be stored as strings).
Now if it happens to be after the 12th of any month and you get a string like: "02/13/2012", those user's who thought the first string above was in February are still fine, but those who thought it was March are suddenly unable to CONVERT this new string into a Date. And that''s the good news.
The bad news is that up until now, your SQL Agent jobs have been mis-interpreting these strings and converting them into the wrong dates, without throwing any errors. ... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2012 at 9:54 am
Thank you
I just spoke to DBA and he changed it on the 9th from British to US
May 16, 2012 at 11:01 am
Yep, that would do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply