October 18, 2010 at 4:40 pm
Hey guys, looking for a quick workaround that doesn't break SARGablility against the base table or possible data corruption on skipped records.
Short form. I go to the target, find out the last record's inserted timestamp. SELECT MAX( InsertedDT) FROM targetTable. Drop this to a package variable of DATETIME.
I then select off the sourceTable with SELECT fld1, fld2, fld3, ... FROM sourceTable WHERE InsertedDT > ?, setting the parameter equal to the user variable in the package.
It works as expected except for one thing. SSIS DATETIME variable truncates off the .063 in this result from the SELECT MAX(): 2010-10-11 19:28:25.063
What's the recommended or standard workaround/fix? Should I string/convert this on inbound to the select off the sourceTable, or do I need to update a .dll somewhere?
EDIT: String's even worse. Trying to see if using a convert instead of CAST( MAX() AS VARCHAR(200)) gets better results.
EDIT2: CONVERT( VARCHAR(100), MAX( InsertedDT), 121) on the select statement from the beginning, passed to string, then recast on its way into the WHERE clause on the source select is successful.
Of course, that still leaves the question open. Am I missing a driver or is the DATETIME in SSIS just that screwy that it drops .mmm from timestamps?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 1:15 am
According to this issue on Connect, SSIS indeed truncates the milliseconds:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply