After Establishing a Connection to the data source, you can use the CData CData Connect source component to pull data into your Data Flow task.

Querying CData Connect Data with the Source Component

Follow the procedure below to connect to CData Connect, retrieve data, and provide data to other components in the workflow.

1. In the SSIS Toolbox, drag the CData CData Connect source component into the Data Flow task.
2. Double-click the CData CData Connect source component. The CData CData Connect Source Editor will display.
3. In the Connection Managers menu, select an available CData CData Connect connection manager, or create a new instance if one is not already available.
4. Choose your Access Mode: "Table or View" or "SQL Statement". Select "Table or View" to use the GUI to select a table or view. Select "SQL Statement" to configure a statement of your choice.
5. Select the Columns tab and rename any output columns as desired.

When you execute the data flow, rows from your selected table or statement will be made available to the components in the data flow.

See SELECT Statements for the supported SQL syntax and examples. See Calling Stored Procedures for how to call stored procedures.

Using Parameterized Queries

Parameterized statements provide an efficient way to execute queries dynamically and mitigate SQL injection attacks. The Source Component provides a Parameters button that can be used to map parameters defined in the query to variables in the data flow when using a custom SQL Command. The component will execute these queries as parameterized statements at runtime.

In order to use the Parameters option, your query must contain parameters, which can be either defined positionally as a named prarameter:

• Positional parameters: When setting up the parameter mapping, the names in the Parameter list must be the index (starting from 1) of that parameter in the query. Example query:
  

  SELECT * FROM Table WHERE FirstName = ? AND Date > ?

  Here, the Parameter names must be set to '1' and '2' for 'FirstName' and 'Date' respectively.

• Named parameters: When setting up the parameter mapping, the names in the Parameter list must exactly match the names of the parameters in the query without the preceding '@' symbol. Example query:
  

  SELECT * FROM Table WHERE FirstName = @FirstName AND Date > @Date

  Here, the Parameter names must be set to 'FirstName' and 'Date'.

Building Dynamic Queries in the Expression Builder

After configuring a source component, you can then use the SSIS Expression Builder to access the SQL statement that the source component executes at run time.

The component will build and execute these dynamically defined queries at runtime.

1. In SSIS Designer, click the Control Flow tab.
2. In the Properties pane, click the button in the box for the Expressions property.
3. In the resulting Property Expressions Editor, click an empty row in the Property box and select the SQLStatement property of the CData CData Connect source component from the drop-down menu. Then click the button in the row you just added. This displays the Expression Builder.
4. In the Expression box, you can create new SQL commands that use the variables available at run time as input parameters. Ensure that you enclose the expression in quotes. For example:
   

   "SELECT * FROM Table WHERE FirstName = '" + @[User::Name] + "' AND Date > '" + (DT_WSTR, 50) DATEADD("day", -30, GETDATE()) + "'"