Query external DB and store results in a variable

Question

Hi Experts, 
 I'm connecting to an external system table which gives me a list of manager's and their reportees (one line for manager and their reportee). 
 For various reasons, I need to process this table for every manager separately - after processing all reportees for each manager I need to update a specific field for that manager on the Person object which is why a simple ORDER BY won't help. 
 What I think I need to do is 2 steps: 
 1) Perform a SELECT DISTINCT on the external table to get a list of all managers 
 2) Run SELECT queries in a for loop iterating over the managers in the where clause 
 Question is, how do I store the list of returned values from SELECT DISTINCT into a variable so that I can iterate over it? I tried SQLExecutor but that gives me an integer value to indicate success or failure. 
 So, to summarize, any way to run a query and store the results in a local variable inside a script? 
 Thanks in advance 
 Kin 
 6.1.4

Barry Jackson · Accepted Answer

Hi, 
 
Perhaps this code snippet might help .... sorry I lost the formatting: 
 
Public Sub CCC_DataReader_Test() 
Dim con As New SqlConnection(GetMsSqlConnectionString()) 
Dim cmd As New SqlCommand("getUser", con) 
cmd.CommandType = Data.CommandType.StoredProcedure 
cmd.Parameters.AddWithValue("@accountID", "310211") 
Dim x As Integer = 0 
Try 
con.Open() 
Dim reader As SqlDataReader = cmd.ExecuteReader() 
While reader.Read() 
For x = 0 To reader.FieldCount - 1 
If Not IsDbNull(reader.getValue(x)) Then 
VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & CStr(reader.getValue(x))) 
Else 
VID_Write2Log("C:\RPTest.log",reader.getName(x) & ": " & "null") 
End If 
Next 
'Exit While 
'count += 1 
'VID_Write2Log("C:\RPTest.log", "Count: " & count) 
End While 
Catch ex As SqlException 
Finally 
con.Close() 
End Try 
End Sub 
 
HTH, Barry.

Barry Jackson · Answer

If your connection string is in an encrypted config parm you will need to call the script in a job step and mark the parameter that is passing the connection string to the script as 'hidden' and 'encrypted'. The job server service will then decrypt the value as it passes it to the script.