How to: Configure the OLE DB Command Transformation

To add and configure an OLE DB Command transformation, the package must already include at least one Data Flow task and a source such as a Flat File source or an OLE DB source. This transformation is typically used for running parameterized queries.

To configure the OLE DB Command transformation

1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.

2. In Solution Explorer, double-click the package to open it.

3. Click the Data Flow tab, and then, from the Toolbox, drag the OLE DB Command transformation to the design surface.

4. Connect the OLE DB Command transformation to the data flow by dragging a connector—the green or red arrow—from a data source or a previous transformation to the OLE DB Command transformation.

5. Right-click the component and select Edit or Show Advanced Editor.

6. On the Connection Managers tab, select an OLE DB connection manager in the Connection Manager list. For more information, see OLE DB Connection Manager.

7. Click the Component Properties tab and click the ellipsis button (…) in the SqlCommand box.

8. In the String Value Editor, type the parameterized SQL statement using a question mark (?) as the parameter marker for each parameter.

9. Click Refresh. When you click Refresh, the transformation creates a column for each parameter in the External Columns collection and sets the DBParamInfoFlags property.

10. Click the Input and Output Properties tab.

11. Expand OLE DB Command Input, and then expand External Columns.

12. Verify that External Columns lists a column for each parameter in the SQL statement. The column names are Param_0, Param_1, and so on.

    You should not change the names. If you change the column names, Integration Services generates a validation error for the OLE DB Command transformation.

    You should also not change data type. The DataType property of each column is set to the correct data type.

13. If External Columns lists no columns you must add them manually.

    • Click Add Column one time for each parameter in the SQL statement.
    • Update the column names to Param_0, Param_1, and so on.
    • Specify a value in the DBParamInfoFlags property. The value must match a value in the OLE DB DBPARAMFLAGSENUM enumeration. For more information, see the OLE DB reference documentation.
    • Specify the data type of the column and, depending on the data type, specify the code page, length, precision, and scale of the column.
    • To delete an unused parameter, select the parameter in External Columns, and then click Remove Column.
    • Click Column Mappings and map columns in the Available Input Columns list to parameters in the Available Destination Columns list.

14. Click OK.

15. To save the updated package, click Save on the File menu.

See Also

Concepts

OLE DB Command Transformation
Integration Services Transformations
Integration Services Paths
Data Flow Task
Data Flow How-to Topics (SSIS)

Other Resources

Integration Services Sources
Integration Services Destinations

Help and Information

Getting SQL Server 2005 Assistance