May 30, 2006 at 11:41 pm
I'm still new to Integration Services, and i need help. I need to calculate the number of minutes that our trouble tickets are in Pending, and save this to the ticket record in one table. All the pending start and end times are in a 2nd table. The trouble that i'm having is that the start and end times are not in the same record.
I had thought of doing 2 if statements similar to the following:
if pending reason = "waiting on Customer" then
pending_start = row.activitydate
end if
if summary = "Pending has been set to No." then
.calcPendingTime = Datediff("n", pending_start, row.activitydate
end if
The problem with the above is that it seems that ever time it loads a new record in the input0buffer, it initalizes the variable "pending_start". I tried to define a global variable, but when i replaced the "pending_start" variable above with Me.Variables.Pending_Start, i would get an compiler error that i could not use the variables outside of the PreExecute().
Any suggestions are more than welcome. Thanks.
May 31, 2006 at 12:08 pm
I think you might be approaching this the wrong way. For a start, you shouldn't try to affect a package variable anyway because even if it worked, only the value put in there by the very last row would be present post execution (i.e. last one wins). That's why it won't let you do it.
If you're trying to compare 2 values linked to the same ticket then you need to make sure those 2 values are in the same record. Join them together on the TicketID using a MERGE JOIN component and then go from there.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply