September 16, 2002 at 1:39 pm
Hello all,
Is it possible to set the value of a DTS Global Variable with a select statement at run time?
SELECT myDTSGlobalVar = MAX(transaction#) FROM myTable
If so would I do it in an ActiveX script task?
Thanks!!!!
Jonathan
September 16, 2002 at 1:44 pm
I should have mentioned, I am using SQL 7 SP4
September 17, 2002 at 1:27 am
Yes. You can!
If you right click on the DTS pane and select properties. In (I think) the second tab you can define your Global Variables for the DTS. If you define it as myDTSGlobalVar then your statement would be
DTSGlobalVariables("myDTSGlobalVar").Value = SELECT MAX(transaction#) FROM myTable
And yes, this would be in an ActiveX script.
Clive Strong
September 17, 2002 at 1:36 am
Sorry...It's way to early to be trying to answer questions...especially before having a coffee!
I forgot to state that you need to code in a connection to the SQL Server before you can run any queries!
Clive Strong
September 17, 2002 at 8:50 am
Thank you Clive. I will give it a try today and post my code if it works!
Jonathan
September 17, 2002 at 11:32 am
Ok I'm stuck. I am very new at VBScript so bear with me!
I created the global variable in the package property window and named it "maxtran". I tried to run a command on the ADO connection but was told it was not supported so... I am trying the following:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim cnn
Dim rst
Dim theSQL
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
Function Main()
cnn.Open "DSN=TM SQLServer Syteline_test"
theSQL = "SELECT MAX([trans-num]) FROM matltran"
rst.Open theSQL, cnn
DTSGlobalVariables ("maxtran").Value = rst![trans-num]
Set cnn = Nothing
Main = DTSTaskExecResult_Success
End Function
I can't get the value into the variable!!
Help!
September 17, 2002 at 12:15 pm
Have you looked at the Execute SQL task object? If you have your global variable set up for the package, put your sql statement in a execute sql task, you can choose in the execute sql task to assign the value of the field returned by your sql statement to the global variable...
hth,
Michael
Michael Weiss
Michael Weiss
September 17, 2002 at 12:33 pm
Hi Mike
I defined global variable in package property "maxtran" as
maxtran Int 0
I then made my connection object to the local SQL database. I then made the SQL Task with the following:
DTSGlobalVariables("maxtran").Value = SELECT MAX([trans-num]) FROM matltran
I get a syntax error "near maxtran".
Do you see the problem cause I don't?
Thanks
Jonathan
September 17, 2002 at 12:45 pm
I hope I didn't steer you down the wrong path! I just now noticed you are using SQL 7...I am not sure what the execute sql task object looks like in Sql 7...is there a button or tab labeled variables or global variables or something like that? if so, that is where you assign the results of the sql statement to the global variable, not in the actual sql statement itself as you are trying to do...that is what is throwing the error...the statement in the sql window of the execute sql task object should just be your "select MAX(transaction#) FROM myTable"...
I am also puzzled as to why your activex task didn't work...did you verify that you were actually getting a value back from the sql statement there?
Isn't DTS fun? 🙂
~Michael
Michael Weiss
Michael Weiss
September 17, 2002 at 1:12 pm
Nope, there is nothing on the sql task window to deal with a global variable that I can see nor is it mentioned in BOL. I guess its a SQL7 limitation.
I was wondering the same thing about the activeX task. Can I "Print rst" in VB script? I am used to debugging in VBA where I would use Debug.Print but there is no immediate window here.
Thanks
Jonathan
September 17, 2002 at 1:28 pm
use a msgbox to return the value of your rst field and see what you are getting...sql server 2000 dts execute sql task allows you to assign a field value to a global variable or an entire row or an entire result set...tell your boss you need to upgrade! lol
good luck!
Michael Weiss
Michael Weiss
September 17, 2002 at 1:36 pm
I just ran:
Function Main()
Dim cnn
Dim rst
Dim theSQL
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
'Set rst = DTSGlobalVariables ("maxtran").Value
cnn.Open "DSN=TM SQLServer Syteline_test"
theSQL = "SELECT MAX([trans-num]) FROM matltran"
rst.Open theSQL, cnn
MsgBox("maxtran is ' " & rst.Fields([trans-num]).value & " ' ")
'DTSGlobalVariables ("maxtran").Value = rst.Fields("[trans-num]").Value
Set cnn = Nothing
Main = DTSTaskExecResult_Success
End Function
generated error "Item cannot be found in the collection corresponding to the requested name or ordinal"
So I don't think its getting a value.
September 17, 2002 at 2:30 pm
Try changing the lines:
"SELECT MAX([trans-num]) FROM matltran"
MsgBox("maxtran is '' " & rst.Fields([trans-num]).value & " '' ")
to:
"SELECT MAX([trans-num]) AS max_trans FROM matltran"
MsgBox "maxtran is " & rst.Fields("max_trans").value
If that doesn't work, check the spelling of the field name...it is looking for a field and not finding the field name you are giving it...that is what the error message means...
Michael Weiss
Michael Weiss
September 18, 2002 at 7:08 am
Mike,
Your suggestion worked perfectly. I am capturing the maxtran value I need!
If you can tolerate one more question: my understanding is that this DTS global variable is only useable in a script environment, that is, it cannot be referenced in the SQL of the source connection in a data pump?
If this is correct what I intend to do is use the variable to open a ADO recordset in a script and wend through doing what I need to.
All of this is the result of trying to query a Progress database for new activity and bring it over to a SQL reporting db. The Progress ODBC provider will not allow me to register it as a linked server for a distributed query. This is the only way I can find of passing a value from the SQL connection to the Progress connection.
Thanks for your time, I really appreciate it!
Jonathan
September 18, 2002 at 11:41 am
Jonathan,
I do not have access to SQL Server 7.0, but in SS 2000 you can enter a ? for a parameter placeholder in your sql statement and then click on the global variables (or is it properties) button on the execute sql task and assign an input variable to the sql parameter. Outside of that, it appears that your only choice is to use an ActiveX script and loop through a recordset...
Good luck! Let us know what you come up with or if you have more questions don't hesitate to ask...
hth,
Michael
Michael Weiss
Michael Weiss
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply