OLE DB Command Transformation Usage

OLE DB Command Transformation Component can reference parameters, line-by-row calls Sqlcommand,this transformation is typically used for running Parameteri Zed queries.

1, custom properties

OLE DB Command Transformation is a very powerful component, but its configuration properties are simple, with only three custom properties,

SqlCommand: SQL command to execute, using? Instead of a parameter, the value of the parameter originates from the input of the upstream component .

2, Parameters

OLE DB Command Transformation Component is a row-by-row conversion , especially if parameters can be used in SqlCommand. The parameter values is stored in external columns in the transformation input, and mapping a input column to an Externa L column maps an input column to a parameter.

2.1 SqlCommand Reference parameter value

OLE DB links are different from adonet links, and references to parameters are different. OLE DB link use? Represents a parameter,? The ordinal number starts at 0, is fixed, and OLE DB references the parameter by ordinal.

For SqlCommand? To reference the parameter of the corresponding ordinal. First parameter, ordinal is the first of 0,sqlcommand? Reference the parameter.

Update dbo.tis set name=name+ ' _update ' where code=?

Type the parameterized SQL statement using a question mark (?) as the parameter marker for each parameter.

2.2 Setting Column Mappings

After the SqlCommand reference parameter, click the Refresh button, the component is automatically recognized? , and then generate parameters, the name of the parameter name is param_ordinal, and you need to set the value of the parameter in the Column Mappings tab and the mapping between the input column of the upstream component to set the source of the parameter value. Available input columns is an input column for an upstream component, and Available destination columns is an automatically generated parameter column.

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

Example: The value of the PARAM_0 parameter is assigned by code.

2.3 Viewing parameter information

In the input and Output Properties tab, select OLE DB command input-external columns, you can see that the component automatically adds a parameter param_0, whose value is derived from Input columns. Note the datatype,codepage and Mappedcolumnid of the check parameters. Mappedcolumnid is the ordinal of the parameter in the SqlCommand.

Verify that External Columns lists a column for each parameter in the SQL statement. The column names is param_0, param_1, and so on.

You should don't change the column names. If you change the column names, integration Services generates a validation error for the OLE DB Command transformation.

Also, you should don't change the data type. The DataType property of each column was set to the correct data type.

Dbparaminfoflags is a custom properties that matches the dbprarmflagsenum of OLE DB, and the value ofdbparaminfoflags represents the characteristics of the parameter. For example, a value of 1 specifies that the parameter is an input parameter, while a value of zero specifies that the parameter is an input parameter, the data type is a character type, and may contain a null value, 81 specifies that the parameter is an input parameter, and the data type of the parameter is int.

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 O f the column.

• To delete a 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 Avail Able Destination Columns list.

Properties in Input Columns

Externalmetadatacolumnid: Specifies the ID of the external column to which the value of input column will flow

MSDN's Introduction to OLE DB Command transformation component is as follows:

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.

You can configure the OLE DB Command transformation in the following ways:

• Provide the SQL statement, the transformation runs for each row.

• Specify the number of seconds before the SQL statement times out.

• Specify the default code page.

Typically, the SQL statement includes parameters. The parameter values is stored in external columns in the transformation input, and mapping a input column to an Externa L column maps an input column to a parameter. For example, to locate rows in the dimproduct table by the value in their ProductKey col Umn and then delete them, you can map the external column named param_0 to the input column named productkey, and then run the SQL statement delete from dimproduct WHERE ProductKey =?.. The OLE DB Command transformation provides the parameter names and you cannot modify. The parameter names is param_0 , param_1 , and so on.

If You configure the OLE DB Command transformation by using the Advanced Editor dialog box, the Parame Ters in the SQL statement is mapped automatically to external columns in the transformation input, and the Characteris Tics of each parameter defined, by clicking the Refresh button. However, if the OLE DB provider that the OLE DB Command transformation uses does does support deriving parameter Informatio n from the parameter, you must configure the external columns manually. This means so must add a column for each parameter to the external input to the transformation, update the column NA Mes to use names like param_0 , specify the value of the Dbparaminfoflags property, and M AP the input columns that contain parameter values to the external columns.

The value of dbparaminfoflags represents the characteristics of the parameter. For example, the value 1 Specifies the parameter was an input parameter, and the value of the specifies The parameter is an input parameter and may contain a null value. The values must match the values in the OLE DB Dbparamflagsenum enumeration. For more information, see the OLE DB reference documentation.

The OLE DB Command transformation includes the SQLCommand custom property. The "Can be" updated by a property expression "when the" is loaded. For more information, see Integration Services (SSIS) Expressions, use property Expressions in Packages, and Transformatio N Custom Properties.

This transformation has one input, one regular output, and one error output.

OLE DB Command Transformation Usage