Problem assigning val to a GV in ActX

Question

Problem assigning val to a GV in ActX

pop

SSCrazy

Points: 2130
More actions
June 29, 2005 at 3:17 pm

#61995

I am trying to assign the value of the DATEDIFF function comparing two Global Variables to a third GV, called "DurationInMins". I've tried many different variations of the following script, but cannot get it to work. Here is the Query I'm trying to use:
"SELECT DATEDIFF(minute, " & DTSGlobalVariables("RefreshStartDate").Value & "," & DTSGlobalVariables("RefreshEndDate").Value & ")"
Here is the script:
Function Main()
Dim objRs, CommandText, ConnectionString
Const DS = "server"
Const DB = "db"
Const DP = "SQLOLEDB"

Set objRs = CreateObject("ADODB.Recordset")

CommandText = "SELECT DATEDIFF(minute, " & DTSGlobalVariables("RefreshStartDate").Value & "," & DTSGlobalVariables("RefreshEndDate").Value & ")"
ConnectionString = "Provider=" & DP & _
                       ";Data Source=" & DS & _
                       ";Initial Catalog=" & DB & _
                       ";Integrated Security=SSPI;"

' Connect to data source and execute the SQL command.
objRs.Open CommandText, ConnectionString, adOpenStatic, adLockReadOnly, adCmdText
DTSGlobalVariables("RefreshEndDate").Value = Now()
DTSGlobalVariables("DurationInMins").Value = objRs(0)
MsgBox DTSGlobalVariables("RefreshEndDate").Value
MsgBox DTSGlobalVariables("DurationInMins").Value
objRs.close
Main = DTSTaskExecResult_Success
End Function
I'm at my wits end here. Any help would be greatly appreciated. Thanks.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How about this??

"SELECT DATEDIFF(minute, '" & DTSGlobalVariables("RefreshStartDate").Value & "','" & DTSGlobalVariables("RefreshEndDate").Value & "')"

pop SSCrazy Points: 2130 More actions · Answer 2

I get the following error:

Error on Line 22

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Line 22 is: objRs.Open CommandText, ConnectionString, adOpenStatic, adLockReadOnly, adCmdText

Any ideas? Thanks for the reply as well.

Pat

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Get a copy of the query that is being executed on the server... there's most certainly a stupid syntax error in it (test in query analyser).

Also here's the equivalent vb code to get this information :

msgbox DateDiff("s", DTSGlobalVariables("RefreshStartDate").Value, DTSGlobalVariables("RefreshEndDate").Value) / 60

One last thing since I can't spot the error in the query :

What are the values of the 2 variables?

pop SSCrazy Points: 2130 More actions · Answer 4

It checks out okay in QA, that's why I'm confused. Here are the values of the two variables:

DTSGlobalVariables("RefreshStartDate").Value = 6/27/2005 1:09:28 PM

DTSGlobalVariables("RefreshEndDate").Value = 6/29/2005 5:16:36 PM

Pat

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Did the vba code I sent you worked, because other than that I have no clue of what the problem can be?

The last thing I can imagine to see the problem would be for you to fire the profiler and execute the package. Then track the script it executes and reexecute in QA... Maybe it's a syntaxe error we missed or simply a login problem (but I don't see why with this simple select).

mike gercevich SSCrazy Points: 2961 More actions · Answer 6

It appears that your global variable is not initialized when you are building your commandtext string. My guess is that it is currently defined as Nothing.

DTSGlobalVariables("RefreshEndDate").Value = Now()

I believe should be executed prior to building your SELECT statement. VBScript has a datediff function as well... is there a reason why you can't use this feature and not use SQL to process the timestamps?

-Mike Gercevich

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Damn, I saw that but I thaught there was 4 variables but there are only 3.

Using the profiler would have caught that .

pop SSCrazy Points: 2130 More actions · Answer 8

Thanks so much guys ! All I needed to do was use the vbscript DateDiff Function and it worked just fine.

Function Main()

DTSGlobalVariables("RefreshEndDate").Value = Now()

DTSGlobalVariables("DurationInMins").Value = DateDiff("n", DTSGlobalVariables("RefreshStartDate").Value, DTSGlobalVariables("RefreshEndDate").Value)

MsgBox DTSGlobalVariables("RefreshEndDate").Value

MsgBox DTSGlobalVariables("DurationInMins").Value

Main = DTSTaskExecResult_Success

End Function

That was just my inexperience in ActiveX Scripting and not knowing how to approach it. Thanks again, I really appreciate the help.

Pat

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Glad to hear that.

Did you try my profiler proposition to see what the statement did look like?

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 10

I would have to agree with Mike -- why not just use VBScript to perform the calculation? Simply dim a variable in the ActiveX Script and perform the calculation. You could then save this new value to a different global variable to be used by later tasks.

Function Main()

Dim myDuration

DTSGlobalVariables("RefreshStartDate").Value = "6/29/2005 1:10:28 PM"

DTSGlobalVariables("RefreshEndDate").Value = "6/29/2005 1:15:28 PM"

myDuration = DATEDIFF ( "N" , DTSGlobalVariables("RefreshStartDate").Value , DTSGlobalVariables("RefreshEndDate").Value )

DTSGlobalVariables("DurationInMins").Value = myDuration

MsgBox DTSGlobalVariables("DurationInMins").Value

Main = DTSTaskExecResult_Success

End Function

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 11

I see you already have the solution.

Always a day late and a dollar short -- story of my life