Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.
The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.
Can anyone help?
If inside a script component the common method is to use row
So you can do row.xxx where xxx is the field name... then using row.next row etc you can loop and such.
|||Ok, how do we reference the dataset? I appreciate your answer Dave but its very vague.
So I have the execute SQL statement create a dataset named "dataset1" using an ado.net object type.
Then within the script component how to we initialize, and work with "dataset1"? I wish to loop through "dataset1" and perform various functions on it. Sure the row access sounds great for the looping part but what of the other parts?|||Use a foreach loop to open that ADO recordset and loop through it. There are plenty of examples in this forum.|||I know that method and that won't work for what i'm trying to do, or at least i don't think so.
All I want to know is how to reference a dataset from an execute sql statement within a script component. I wish to reference this dataset within the script component and loop through and create an array with specific information. Then I want to use the array as loop through that to create a body of an email. So PLEASE answer the question of ->>>>>
How do I reference a dataset after it being populated via an Execute SQL Statement?
<<<<<--
I tried
dim results as dataset
dim reader as datatablereader
reader = results.tables(0).createdatareaders
do while reader.read
execute statements
loop
I get the error that reader is referenced to no table.|||
I misunderstood your initial question, I thought you needed basic access. I was thinking you used the execute sql command component to create the data set. So if you execute the sql, and then have the output of that component connect to a script component you can just use the Row call I mentioned, because it automatically ties to the "input" dataset from the sql execute component.
|||Here is my code. I have OBJ_NULL_SET as the result set object from the execute sql components output.
Public Sub Main()
'Assume success
Try
Dim results As DataSet
results = CType(ReadVariable("OBJ_NULL_SET"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next
Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
When the msgbox pops up it says Table with nothing else. It does not display the name of the dataset so i'm sure that i've done something wrong where the dataset is not being referenced properly.|||
You are already referencing the dataset by iterating through the DataTables. The first table's name so happens to be "Table", and that's what the message box call shows.
So, for the only DataTable of the DataSet, iterate through the rows using standard ADO.NET. For example, call this function right after you're first MsgBox() call, and it will display the column names and stringify the data. You're refencing the data from the Execute SQL task at this point, and what is done beyond that is up to your imagination.
Private Sub DisplayTable(ByVal tbl As DataTable)
Dim dr As DataRow, dc As DataColumn
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
For colNum As Integer = 0 To tbl.Columns.Count - 1
dc = tbl.Columns(colNum)
sb.Append(dc.ColumnName).Append(",")
Next
Dts.Events.FireInformation(1, "Columns", sb.ToString(), "", 0, True)
For rowNum As Integer = 0 To tbl.Rows.Count - 1
sb.Remove(0, sb.Length)
dr = tbl.Rows(rowNum)
For itemOffset As Integer = 0 To dr.ItemArray.Length - 1
sb.Append(dr.ItemArray(itemOffset)).Append(",")
Next
Dts.Events.FireInformation(1, "Data", sb.ToString(), "", 0, True)
Next
End Sub
sql
