2

How can I store dataset object into a package variable of data type Object and assigning it back to another Dataset object from within a script task?

Sreejesh Kumar
  • 2,449
  • 12
  • 51
  • 72
  • Sreejesh, I updated my answer below with the other side of what you were looking for. – William Salzman May 22 '10 at 15:06
  • Do you mean SSIS, or DTS? Which version? – John Saunders May 22 '10 at 15:40
  • Yes Wiliam. But my question is how to store the dataset or datatable object to a SSIS variable – Sreejesh Kumar May 23 '10 at 17:47
  • OK I showed you code examples of reading from a dataset variable in a script task, I also showed you code examples of writing out to a dataset variable in a script task, and I also mentioned that you can use a recordset destination to write to a dataset variable from a dataflow task. What part of the question that you asked am I missing? – William Salzman May 24 '10 at 11:37

1 Answers1

9

Here is a code example from a previous answer of mine. It shows how, in a script task, you can fill an OleDB Data Adapter from an Object Variable. You can store recordsets into object variables by using the recordset destination in a dataflow task.
How to access a Recordset Variable inside a Script Task

To further extend what I did in the script from my other answer, if you have manipulated the rows in your object variable and want to save the new results into the object variable for further processing, you can do the following near the end of your process. Dts.Variables("rsRecipients").Value = dt

An example of this is located at: Update SSIS Object Variable Used In Foreach Conainer From Script Task
Code from example #2:

Public Sub Main()
Dim Header As String
Dim Body As String
Dim Footer As String
Header = "blah"

Footer = "blah"

Try
    Dim olead As New Data.OleDb.OleDbDataAdapter
    Dim dt As New Data.DataTable
    olead.Fill(dt, Dts.Variables("rsRecipients").Value)

    For Each row As Data.DataRow In dt.Rows
        If UCase(Trim(row("EmployeeCode").ToString())) = UCase(Trim(Dts.Variables("colEmployeeCode").Value.ToString())) Then
            Body = Body + "Label: " + Trim(row("colum").ToString()) + System.Environment.NewLine
            row.Delete()
        End If
    Next
    Dts.Variables("rsRecipients").Value = dt
    Dts.Variables("EmailMessage").Value = Header + Body + Footer
    Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
    Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

The line olead.Fill(dt, Dts.Variables("rsRecipients").Value) reads in from a recordset variable. The line Dts.Variables("rsRecipients").Value = dt writes the dataset back out to a variable (in this case the same one, however you could write it to a different variable if you wanted to). The other ways to load a dataset into a object variable include using the recordset destination in a dataflow task, or using a sql task and setting the full resultset to a variable by choosing full result set on the result set field in the dialog, then setting the output to go to a variable of type object.

Community
  • 1
  • 1
William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • But William, how do u store the Dataset/Datatable object to the variable in Script Task? – Sreejesh Kumar May 21 '10 at 11:53
  • 1
    Dts.Variables("VariableName").Value = dt where dt is a datatable in your script task that has data loaded into it, either from a .Fill operation or by adding rows manually using the datatable api's. – William Salzman May 24 '10 at 11:47