How to consume a Datareader destination in code?
Hello,
I can gain access to and execute my package using the Application and Package objects. I am handling the events of the Package object and can even see the OnPipelineRowsSent event when it fires to deliver the rows to the Datareader destination. How do I gain access to those rows for use in my application? I have scanned the SSIS object model looking for the right place to hook in, but was not able to find one. Am I heading in the right direction? Is there a sample application out there I have missed?
Similarly, how does one deliver rows in code to the Datareader source? Does this take place prior to calling Execute on the Package, or it is handled when a particular event fires, or perhaps something else?
Thank you,
Brandon
OK, let's try that copy/paste again for the sake of readability...
Loading Data Flow Results into a Client Application
6/20/2005 [This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]
Client applications can read the output of Integration Services packages from SQL Server destinations by using ADO.NET, or from flat file destinations by using the classes in the System.IO namespace. However a client application can also read the output of a package directly from memory, without the need to persist the data as an intermediate step. The central element in this solution is the Microsoft.SqlServer.Dts.DtsClient namespace, which contains specialized implementations of the IDbConnection and IDbCommand interfaces from the System.Data namespace.
This topic describes how to read package results into any managed application. For information on loading package results directly into a Reporting Services report, see Retrieving Package Data from SQL Server Integration Services.
Description
Developing a client application in managed code that reads package results directly from memory requires the following approach. The steps summarized here are demonstrated in the code sample that follows.
Loading data flow results into a client application
In the package, configure a DataReader destination to receive the output that you want to read into the client application. Make a note of the name of the DataReader destination.
In the development project, set a reference to the Microsoft.SqlServer.Dts.DtsClient namespace by browsing to the assembly Microsoft.SqlServer.Dts.DtsClient.dll, which is installed by default in C:\Program Files\Microsoft SQL Server\90\DTS\Binn. Import the namespace into your code by using the C# Using or the Visual Basic.NET Imports statement.
In your code, create an object of type DtsClient.DtsConnection with a connection string that consists of the command-line parameters required by dtexec.exe to run the package, and then open the connection by using this connection string. For more information, see dtexec Utility.
Create an object of type DtsClient.DtsCommand whose CommandText property is the name of the DataReader destination in the package. Associate the command with the connection and call the ExecuteReader method of the command object to load the package results into a new DataReader.
Call the Read method of the DataReader repeatedly as needed to loop through the rows of output data. Use the data, or save the data for later use, in the client application.
Close the DataReader and the connection objects.
Important:
After reading the last row of data in the DataReader, you must call its Read method one more time; otherwise, when you attempt to close the DataReader or the connection, an unhandled exception will occur. This behavior is different from that of many other DataReader implementations. When using a loop to read through the rows in the DataReader, you need to write code that recognizes that, on the last successful call to the Read method, no row of data is in fact available to read.
Example
The following example runs a package that calculates a single aggregate value and saves it to a DataReader destination, then reads this value from the DataReader and displays it in a text box on a Windows Form.
Setting up the Visual Basic.NET test application
Create and save a package that uses the Aggregate transformation to calculate a single value and then saves the value to a DataReader destination. The sample code uses "DataReaderDest" as the name of the DataReader destination and "TestClientAccess.dtsx" as the name of the package.
Create a new Visual Basic.NET Windows Forms application.
Add a reference to the Microsoft.SqlServer.Dts.DtsClient namespace.
Copy and paste the sample code below into the code module for the form.
Modify the value of the dtexecArgs variable as necessary so that it contains the command-line parameters required by dtexec.exe to run your package. The sample code loads the package from the file system.
Modify the value of the dataReaderName variable as necessary so that it contains the name of the DataReader destination in the package.
Place a button and a text box on the form. The sample code uses btnRun as the name of the button, and txtResults as the name of the text box.
Run the application. After a brief pause while the package runs, you should see the aggregate value calculated by the package displayed in the text box on the form.
Sample Code
Visual Basic Copy Code
Imports System.Data
Imports Microsoft.SqlServer.Dts.DtsClient
Public Class Form1
Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim dtexecArgs As String
Dim dataReaderName As String
Dim dtsConnection As DtsConnection
Dim dtsCommand As DtsCommand 'IDbCommand
Dim dtsDataReader As IDataReader
dtexecArgs = "-f ""C:\packages\TestClientAccess.dtsx"""
dataReaderName = "DataReaderDest"
dtsConnection = New DtsConnection
With dtsConnection
.ConnectionString = dtexecArgs
.Open()
End With
dtsCommand = New DtsCommand(dtsConnection)
dtsCommand.CommandText = dataReaderName
dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default)
With dtsDataReader
.Read()
txtResults.Text = .GetDouble(0).ToString("C")
End With
'After reaching the end of data rows,
' the program must call the Read method one more time.
dtsDataReader.Read()
Try
If dtsDataReader IsNot Nothing Then
dtsDataReader.Close()
End If
Catch ex As Exception
MessageBox.Show("Exception closing DataReader:" & ControlChars.CrLf & _
ex.Message & ControlChars.CrLf & _
ex.InnerException.Message, "Exception closing DataReader", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Try
If dtsConnection.State <> ConnectionState.Closed Then
dtsConnection.Close()
End If
Catch ex As Exception
MessageBox.Show("Exception closing connection:" & ControlChars.CrLf & _
ex.Message & ControlChars.CrLf & _
ex.InnerException.Message, "Exception closing connection", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class
Description
Important: 
Copy Code