SSIS: SQLServer to DataReader

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.

1-SSIS-SQLServertoDataReader

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

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

2-SSIS-SQLServertoDataReader

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.

3-SSIS-SQLServertoDataReader

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.

8-SSIS-SQLServertoDataReader

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.

9-SSIS-SQLServertoDataReader

Configuring a Data Destination

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

10-SSIS-SQLServertoDataReader

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.

11-SSIS-SQLServertoDataReader

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s