Exec query with ActiveX against existing DTS connection
-
How do I execute an query using ActiveX against an existing connection? In other words, my package already has a connection object - how do I access it to run a query, or do I have to redefine a connection?
-
You'll have to create an new ADO connection within your ActiveX task.
--------------------
Colt 45 - the original point and click interface
-
You can re-use your connection with the execute statement. I normally create and open my connection then populate a sql query string and do a oCN.Execute sqlstring
hope this helps.
Darrell Parrish
La Crosse, WI -
It does help, and even better, it's in direct opposition to the previous poster.
Could you post a code example (I'm not a VB coder)?
Thanks
-
This will create a reference to your connection:
Dim oPkg
Set oPkg = DTSGlobalvariables.parent
Dim oConn
Set oConn = oPkg.Connections("myConn")
However you cannot execute a sql statement against this connection.
I take it you are trying to create a SQL string dynamically?
You hardly have to know how to code to use the Dynamic Properties Task. Just use your ActiveX script to create a global variable called sSQL and write your SQL string to it. Then go into your Dynamic Properties Task and set the SQL statement of an ExecSQL task equal to that string. You can also do this for transformations as well.
[font="Courier New"]ZenDada[/font]
-
No, I'm not trying to create a statement on-the-fly. All I'm trying to do is find out if a table has any rows (count(*)). If not, DTSStepScriptResult_DontExecuteTask.
Please note that I'm on SQL 7; I don't have a "Dynamic Properties Task".
I just don't understand why, when I have a connection created in the DTS package, I can't use it to run a simple query.
-
This is a DTS connection object, not an ADO connection object. You can definitely code this with the DTS object model. DTS connection objects have child objects that execute SQL. Or since you know ADO syntax, just take the performance hit and go with the ADO. You could also control your workflow such that you do not flow to the task in the first place.
Too bad you don't have 2000. It's a lot more fun and a lot more productive with less coding.
[font="Courier New"]ZenDada[/font]
-
So...
Dim oPkg
Dim oConn
Dim oRS
Set oPkg = DTSGlobalvariables.parent
Set oConn = oPkg.Connections("myConn")
Set oRS = CreateObject("ADO.RecordSet")
Set oConn.Open
Set oRS = oConn.Execute "SELECT COUNT(*) FROM myTable"
?? Like I said, I'm not a VB coder; I really don't know how to do this.
-
....
strSQLText = "SELECT COUNT(*) FROM myTable"
set rs = CreateObject("ADODB.Recordset")
rs.Open strSQLText, oConn, adOpenDynamic
If (rs.BOF = True and rs.EOF = True) Then
set rs = Nothing
' MsgBox "No data found"
Else
Do While Not rs.EOF
rs.MoveFirst
....
Else
...
End If
rs.MoveNext
Loop
set rs = Nothing
End If
-
Ok, but I get a runtime error - "Object required: '[string: "select count(*) from"]'"
Here's what I have:
dim oPkg dim oConn dim oRS set oPkg = DTSGlobalvariables.parent set oConn = oPkg.Connections("myConnection") set oRS = CreateObject("ADODB.RecordSet") set strSQLText = "SELECT COUNT(*) FROM myTable" oRS.Open strSQLText, oConn, adOpenDynamic If (oRS.BOF = True and oRS.EOF = True) Then MsgBox "No data found" Else MsgBox "Data in table" End If set oRS = nothing set oConn = nothing -
You "set" objects. You simply assign values to variables. strSQLText is not an object. It is a variable.
Also, you are trying to use the DTS connection object to open an ADO recordset object. You need an ADO connection object. Different object models. Bear and bare. Sound the same. Are not the same. The DTS object model is the syntax behind the DTS GUI. If you can't use the little connection icon to execute a SQL statement in the GUI, you can't do it in the code either. You first have to create a task... don't bother. If that's what you had wanted to do, you could have done it with the GUI.
Is this logic something you could handle upstream from this ActiveX? If so, you probably should.
[font="Courier New"]ZenDada[/font]
-
Ok, so what do I do? All I'm trying to do is check to see if there are records in a table before executing the step. I'm trying to apply the code to the Workflow properties. Can I handle this upstream? How? The problem is, if I execute the statement and there's no records in the table, the whole job dies. I just want to create a simple little condition on a given step in the workflow stream. Why is that so hard?
-
Well, it gets so easy in 2000 I have almost forgotten the pain of 7. Pester your boss to upgrade.
In the meantime, here are your options:
1 - Do what you are doing. You just need to declare an ADO connection, that's all!
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oCx
Dim oRs
Dim sSQL
Set oCx = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")
oCx.Provider = "sqloledb"
oCx.Properties("Data Source").Value = "myServer"
oCx.Properties("Initial Catalog").Value = "myDatabase"
oCx.Properties("Integrated Security").Value = "SSPI"
oCx.CommandTimeout = 360
oCx.Open
sSQL = "Select count(*) from myTable"
Set oRs = oCx.Execute(sSQL)
If (oRs(0)) = 0 Then
'do something
Else
'do something else
End If
oRs.Close
Set oRs = Nothing
oCx.Close
Set oCx = Nothing
Main = DTSTaskExecResult_Success
End Function
2 - Skip this step in your package by setting the next step to waiting in the previous step. This requires that you undertand the DTS object model.
======================================================
There are reasons you are frustrated and this seems hard. What you are trying to do requires that you know 1 - VBscript, 2 - the ADO Object model and 3 - the DTS Object model.
Forgive me while I jump on my soapbox for a moment:
This is what makes experienced developers more valuable and why they make more money. Yes, we all took the same coursework in school. But it is only through experience that we become familiar with numerous object models and various languages. Experience allows us to become faster and better at thinking of various ways to get stuff done. Fret not, end-user - it only hurts for the first couple of years. After that it's fun. Really fun!
Here are some tips to help you on your way:
1 - Here is a shortcut for creating a connection string (learned this from a Microsoft Evangelist) - create a blank txt file named test.udl. Click it open and you will see the Dialog box for making a connection. When done, right click test.udl and open in notepad to get your string.
2 - O'Reilly books - get the one for VBscript and the one for ADO.
3 - Wrox books - get SQL Server 2000 Programming and SQL Server 2000 DTS. The Apendices are loaded with object models.
4 - Save your package as a Visual Basic file. You can then open it in Visual Studio or even Excel VBA. Use Intellisense to get familiar with syntax. You can set a reference to the DTS object library in Excel and get Intellisense prompts for your code. You must instantiate an object to get Intellisense to recognize it and prompt you with properties and methods.
5 - You can run your ActiveX script in the debugger like this: turn on just-in-time debugging from the context sensitive menu on the DTS node in the EM. Type the word "stop" above function Main() in your script. Right click on the script task to execute. This will launch your scipt in the debugger. You can use either the InterDev debugger or the MS Script Debugger.
[font="Courier New"]ZenDada[/font]
-
Ok, I'll use that. It's just a mystery to me as to how/why this is all setup this way.
In our defense, this is the first time we've ever needed to create a DTS package, so it's a tough arguement for upgrades.
I agree, experienced coders are worth their salt. However, if there's little-to-no work for them, it's hard to keep them on payroll. This is just one small task with a tiny amount of VB code. It's also why we steer away from M$ products when we can - it requires vast human resources, training, and experience to accomplish even the smallest tasks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply