Exporting SQL Server to a DataReader.

In this article, I will demonstrate using SSIS to connect to SQL Server, export data from a table into a DataReader.

When we’ve completed this tutorial, you should see the above screen.

First, drag a DataFlow task unto the Control Flow designer.

Next, double-click the DataFlow task which will take you into the Data Flow designer.

Configuring a Data Source

Once in the Data Flow designer (the Data Flow tab should be selected in the workspace, drag an OLE DB Source unto the designer from the DataSource section of the Toolbox.

Double-click the Data Source to configure its connection to SQL Server.
Enter the server name and your credentials, then select a Database (example).
Next, select “Table” for “Data Access Mode” (example).
Select the database table you wish to see. Also, you may click “Preview” to see its data (example).
To optimize the Data Source, you may select “Columns” to deselect certain unused columns being returned (example).

Configuring a Data Transformation

From the Data Flow Transformations, drag a Derived Column unto the designer.
Then, connect the green handle from the Data Source to the Derived Column.

Next, to configure the transformation, double-click the Derived Column.
In this example, I create a new “FullName” column that will be added into the pipeline which is an aggregation (using the UPPER() String function provided as well as the concatenate operator.

Configuring a Data Destination

Finally, to configure a destination for the data, drag a DataReader Destination unto the designer.

Like the other objects, double-click it to configure its properties.
In this example, I’m choosing not to show all the data being returned from the Customers table.
You may now run this Package to see data being pulled into your Data Source, into the pipeline, through the Derived column where any additional columns you may have created will be added into the pipeline. The data will then be transported into the DataReader.