Biml XII – Oledb Command (Dataflow transformation)


The data flows OLEDB Command executes an SQL statement for each row of the data flow. A good example of its use is the update of expired records end dates for type 2 changed dimensions. I’ve briefly discussed the logic for roll your own type 2 (changing) attributes here with the need to update expired records at point 3.3. In that post, we looked at the conditional split (the biml for its basic functionality) in a very simple example. The main point to reiterate for the OLEDB command is that a command executes for each row of the transforms input and (naturally) we have access to each columns value in that row.

As a sidebar to the use of the OLEDB Command (and the overhead of executing a statement for every row in the transforms input), an alternative method of update is to dump the input into a temporary table and then execute a single update command against the dimension table using the temporary table.

In this post we look at the biml configuration for using the oledb command to inserting rows into a table. We’ll keep the focus on customers (code, first name and last name). The completed dataflow will look like the following. The update syntax is easily based on this example.

The Biml

The package only contains two transforms – the source and the oledb command (acting as a destination). As we would expect, the oledb command requires three basic settings. These are ;

Requirement

Comment & Notes

Values for Name and Connection

All tasks and transforms require a name. Since we’re executing an SQL statement, we need the OLEDB connection.

Since they apply to the entire transformation, their attributes of the tasks root definition (ie attributes of the OledbCommand node).

The Statement to Execute

This is managed by the <DirectInput> tag’s text

Note that parameters are introduced to the statement using the ? symbol

Parameters specifications that map the input columns to command parameters

Managed by the transform Parameters node (grouping each parameter).

Each parameter is defined by the its source column, and target parameter position and a data type.

Note the parameter position (the order by which it appears in the SQL statement) is a zero based value (its index) and suffixed to the Param_ target column.

In defining the data type, only its type is required. For example, we could define the parameter as only being a String type. Where precision is not specified (that is, the length), the package will be build and execute however it will warn that columns are out of synchronisation.

 

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

  <Connections>
		<OleDbConnection Name="src_adventure_works" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2008R2;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008R2" />
		<OleDbConnection Name="dest_stage" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2008R2;Integrated Security=SSPI;Initial Catalog=stage" />
    </Connections>
	
    <Packages>
      <Package Name="oledb_cmd" ConstraintMode="Linear">
			<Tasks>
				<Dataflow Name="oledb_cmd">
					<Transformations>
						<OleDbSource Name="src_customer" ConnectionName="src_adventure_works">
							<DirectInput>
								select 
								  CustomerAlternateKey as customer_code
								  , FirstName as first_name
								  , LastName as last_name
								from dbo.DimCustomer; 
              </DirectInput>
            </OleDbSource>
						
						<OleDbCommand Name="insert_customer_dynamic" ConnectionName="dest_stage">
							<DirectInput>
								insert into [dbo].[customers]
								(customer_code, first_name, last_name)
								values
								(?, ?, ?);
              </DirectInput>
							<Parameters>
								<!--
									note that you do not have to specify string length 
									if not, the package will warn for column synchronization 
								-->							
								<Parameter SourceColumn="customer_code" TargetColumn="Param_0" DataType="String" Length="15" />
								<Parameter SourceColumn="first_name" TargetColumn="Param_1" DataType="String" Length="50" />
								<Parameter SourceColumn="last_name" TargetColumn="Param_2" DataType="String" Length="50" />
               </Parameters>
              </OleDbCommand>
						
             </Transformations>
            </Dataflow>
      </Tasks>
		</Package>
		 
    </Packages>
</Biml>



 

Advertisements

One thought on “Biml XII – Oledb Command (Dataflow transformation)

  1. Pingback: Biml XIII – Oledb Command Part II – Return Variables | Paul te Braak

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s