Query


SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

Looking for older releases? Check here.

Overview

The Query Component is an SSIS Data Flow Component providing the ability to retrieve data from a SQL database by defining data flow input columns as query keys. The Query component supports a variety of connection types as a source.

Demonstration

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the Query component for processing.

General

The General page is used to define the component source database and statement.

Type

Select the connection type. This parameter has the options listed in the following table.

Value Description
OLEDB Use the OLEDB Connection Manager for the Connection option.
ADO Use the ADO Connection Manager for the Connection option.
ADO.NET Use the ADO.NET Connection Manager for the Connection option.
Connection

Select an existing connection manager.

New

Click New to create a new connection manager.

Related Topics: OLE DB Connection Manager, ADO Connection Manager, ADO.NET Connection Manager

Table / View radio button

Select an existing table or view as the query source.

SQL Query radio button

Specify an SQL statement as the query source.

Insert sequence terminator

Indicates whether an empty line should be inserted at the end of each set of records returned by the query. The default is true.

Options Description
True Empty line will be inserted.
False Empty line will not be inserted.

Columns

The Columns page is used to set up the query key columns and output columns.

Available Input Columns

List of available input columns from the upstream data flow component. Connect only the key column(s) to available lookup columns to use as query key columns.

Available Query Columns

List of available columns in the records being searched. Use the check boxes to include columns in output.

<SQL Command> is a special query column. This allows you to provide a SQL statement from upstream to be used for the query. Map it to the input column containing the statement to populate the SQL statement the Query will use with content from the input row.

Lookup Column

Select lookup column to include in output.

Output Alias

Specify output column name. The default is the name of the lookup column.