Using Recordsets in DTS
-
I have a DTS package in which I need to retrieve a recordset from my SQL Server connection and work with each individual item in the retrieved recordset.
Can I return a recordset from a stored procedure using the Execute SQL Task? If so, what data type of Global Variable do I return the recordset to? And, then, how do I access the individual items in the recordset?
Can I execute a Transact SQL query in an ActiveX Script Task? If so, how do I do this? How do I hold the returned recordset, and access the individual items in it?
I spent all day yesterday searching the internet on how to do this, but I could find nothing useful.
Can anyone help me?
-
Not sure on your exact details, but I would look into using a cursor if you're trying to work with a selected record set, one line item at a time.
BOL has some examples, I would read toward the T-SQL section for a simple declare, open and fetch to start with.
hope that helps.
-
Let me be very clear, here.
I am trying to return the recordset to my DTS package. I need to process the recordset in the DTS package, not process it in a stored procedure. I need to retrieve, access, and process the recordset all from within a DTS Package ActiveXScript Task using VBScript.
Does anyone know any way to do this?
-
Hello there. Maybe I can give you a start on what you are trying to accomplish.
What I do first is to create a UDL for the connection to the SQL Server. I put full path name to the UDL file in the GlobalVariables. I do this so I can change between the Test, Development and Production environments without having to go into the code. But you can "hard-code" to get started.
When you use VBScript in a DTS Package, it is in an ActiveX Script Task. All you can do is Dim the different variables you will be using: Dim cnSQL, Dim rs as all variables are variant.
Here is the code I use to open the Connection:
gCernerUDL = DTSGlobalVariables("gCernerUDL").Value
Set cnCerner = CreateObject("ADODB.Connection")
cnCerner.ConnectionString = "File Name=" & gCernerUDL
cnCerner.Open
Here is the code I use to open the Recordset:
strSQL = "Select ...... "
Set rsSex = CreateObject("ADODB.Recordset")
rsSex.Open strSQL, cnCerner, adOpenStatic, adLockReadOnly, adCmdText
If rsSex.EOF = True Then
'Do something here
Exit Function
End if
Do While Not rsSex.EOF
rsSex.MoveNext
Loop
rsSex.Close
Set rsSex = Nothing
-
-
Thanks for your help, everyone.
-
Just one comment on technique here:
You can return a recordset using the Execute SQL step by retrieving it into a global variable. (SQL2K+) You can also return and manipulate a recordset in an active X script using ADO command objects.
You should use the latter unless you need to manipulate the recordset across mutiple steps, or because of some processing that will take place, you won't be able to retrieve the desired recordset at the end of the process that you could have received at the beginning.
-
Was there some reason you couldn't have done this with a transformation that used a SQL Query, rather than a table, as its source?
-
Maybe because I didn't know I could, which is why I was asking how to do it in the first place?
-
I'm not sure if that means you still don't know how or not, but all you do is create a transformation and in the "Source" tab, instead of choosing a table, you select the SQL Query radio button, and in the box below, enter a query that produces a recordset. You can "Preview" the recordset to make it looks the way you want. Then do the rest of the transformation as usual.
HTH.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply