After Establishing a Connection to the data source, you can use the CData Microsoft Excel Task to run stored procedures and SQL queries at the Control Flow level.

Querying Microsoft Excel Data with the Microsoft Excel Task

Complete the following steps to connect to Microsoft Excel and execute custom SQL queries at the Control Flow level. A common use case for this would be to truncate table data or execute stored procedures before executing your workflow.

1. In the SSIS Toolbox, drag the CData Microsoft Excel Task into the Control Flow.
2. Double-click the CData Microsoft Excel Task. The CData Microsoft Excel Task opens.
3. In the Connection Manager > SQL Statement > Connection drop-down list, select an available CData Microsoft Excel connection manager, or create a new connection manager if one is not already available.
4. Configure the query you want to execute. There are three properties that control this:
   • SQLSourceType: Where to source the query from, either a direct input in the CData Microsoft Excel Task UI or from a variable in the package.
   • CommandType: The type of command to run. The options are "Table Or View", "Stored Procedure", and "Command Text". For the first two, the Task expects only the name of the object to execute. The Command Text option allows you to execute any SQL command you'd like.
   • The last option is context-specific based on the values you set for the above two settings.
     • If SQLSourceType is set to Direct Input, a different setting appears for each Command Type:
       • Table or View: Appears if Command Type is set to Table or View. In the dropdown menu, select the table or view that you want to extract data from.
       • Stored Procedure: Appears if Command Type is set to Stored Procedure. In the dropdown menu, select the stored procedure you want to execute. The available inputs are listed automatically in the Parameter Mapping tab. Supply values for the stored procedure inputs here.
       • SQLStatement: Appears if Command Type is set to Command Text. Enter the SQL query you would like to execute.
     • If SQLSourceType is set to Variable, the final setting will always be SourceVariable, but the behavior varies based on the Command Type:
       • When Command Type is set to Table or View, select a variable containing the name of the table or view that you want to retrieve data from.
       • When Command Type is set to Stored Procedure, select a variable containing the name of the stored procedure that you want to execute. You must manually add the inputs in the Parameter Mapping tab. Ensure that the Parameter Name value for the inputs you add matches those of the stored procedure's available inputs.
       • When Command Type is set to Command Text, select a variable containing an entire SQL query you want to execute.
5. Optionally, you may assign parameters and/or a result set, as described in the following section.

See SELECT Statements for the supported SQL syntax and examples.

Defining the Parameter Mapping

Parameters can be used with the Stored Procedure and Command Text command types. On the Parameter Mapping tab, add as many Parameters as you need. When using the "Stored Procedure" option, the "Parameter Name" must exactly match the name of the parameter defined in the stored procedure. For the "Command Text" option, the name must match the name used in the SQL command. You can assign a variable to either read from or write to depending on the Direction chosen. The available Direction options are as follows:

• Input: Indicates the value is read from an SSIS variable when executing the call.
• Output: Indicates the value is returned from the command call and stored in a local variable for use later in the package.
• InOut: A combination of the above. The value is read when the query is executed and the returned value overwrites the existing value after execution is complete.

Configuring a Result Set

A Result Set can be configured if the query results are needed later in the package. There are two options: Single Row and Full ResultSet. Each one affects how the values are output by the Task and how you need to define the output on the Result Set tab in the UI. Note that when using a Stored Procedure, defining OUT or IN/OUT parameters can be used instead of configuring a Result Set.

• Single Row: When a single row is returned, you need to set the ResultSet Name to the index (i.e. 0, 1, 2, etc.) of the column you are querying. The Variable Name must be mapped to a variable in SSIS that is the correct data type as the output column.
• Full ResultSet: With the Full ResultSet option, only a single value is returned which is a DataTable containing the full data returned from the query. The ResultSet Name should be set to 0. The SSIS Variable Name must be set to a variable of type "Object". Note that if multiple ResultSets are returned from the query, the Task currently only supports outputting the first.