Using Data Transformation Services: Capture Recordset with a Global Variable - Retrieve rowset using Activex Script
(Page 4 of 4 )
In this part, we will take a look at the Global variable Contacts created in the previous step using Activex Script. Pick up an Activex task and instantiate it on the design pane. As you click the Activex task in the tasks collection, an Activex icon is placed on the design pane, simultaneously opening the window to write your Activex Script as shown in the next screen. Please review my previous tutorials on the details about this window and its several controls.
In this screen we declare an object RS and assign to it an ADODB.Recordset object created using the CreateObject() method. In the next line, we associate this record set with a DTSGlobalVariable. Now it is easy to cycle through the recordset to look at its properties, such as the number of records, the various fields and their properties, and so on. This is what is being achieved in the For ...Next statement. The script used to retrieve the recordset and display in a MsgBox is shown here.
'**************************************************************
' Visual Basic ActiveX Script
'**************************************************************
Function Main()
dim cnt
dim RS
set RS = CreateObject("ADODB.Recordset")
set RS = DTSGlobalVariables("Contacts").value
for cnt = 1 to RS.RecordCount
MsgBox ( "Contact Name: " & RS.Fields("ContactName").value _
&" ; ContactTitle: " & RS.Fields("ContactTitle").value _
&" ; City : " & RS.Fields("City").value _
&" ; Country: " & RS.Fields("Country" ) & vbcrlf )
RS.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
Finishing up the DTS Package
The package now contains an ExecuteSQLTask and an Activex Task. We want the Activex task to be processed after the ExecuteSQL task. We invoke the services of a Work Flow. Highlight both of the tasks and set up the work flow as shown here(review the previous tutorials for a more detailed explanation). We want the Source Step to succeed (complete is also acceptable) before the Destination step to be processed.
Now, we will test this package. Save the package and from menu item Package's drop-down click on Execute. This runs the package. The result is shown in the next picture. The MsgBox will pop up seven times, each time bringing a row from the queries result set and finally a DTS message (Package Execution Results window) about the success of the run. The picture shows the first row in the message box.
Summary
Assigning records returned from SQL Queries to a global variable is described in detail. The record set was displayed using a message box, but using the powerful scripting support available in VBA, it is possible to take these into a variety of forms, text files, Excel spread sheets, and so forth.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
