Biml XIII – Oledb Command Part II – Return Variables


In our prior post, we looked at the Oledb Command, a data flow transform that fires a statement for each row in the pipelines dataset. That post, we used generic parameters that were defined in our query. For example our query was;

insert into [dbo].[customers]
(customer_code, first_name, last_name)
 values
  (?, ?, ?);

 

The transform allows us more flexibility and we will look at some of that now. We will look at the execution of a stored procedure (and the variation in parameter specification) and the usage of return parameters (so that values are appended to the dataset as it flow through the buffer).

This post is broken into two components. Firstly, we quickly examine the biml just for the transform – I’m assuming that you can follow based on the prior post. Secondly, we present a more complete example so a full solution can be worked through.

The Task

Often I can delegate logic to a stored procedure, for example, the generation of audit keys. For example (and very simply), we could take the example of inserting a record (see this post), use a stored procedure to insert the data and then return information about that process. Since we are working with customers, let’s just use a procedure to insert a record and return the first letter of the customer’s name. A procedure would look like this;

Create proc [dbo].[customer_udpate]
(
	@customer_code nvarchar(15)
	, @first_name nvarchar(50)
	, @last_name nvarchar(50)
	, @last_name_first_char nvarchar(1) output
)
as 
begin

	set nocount on;

	insert into [dbo].[customers](customer_code, first_name, last_name)
	values(@customer_code, @first_name, @last_name);

	set @last_name_first_char = LEFT(@last_name,1); 

end 

If we were to execute that through an SQL Script we use some code like (with the output below);

declare @customer_code nvarchar(15)='AW00011000';
declare @first_name nvarchar(50)='Jon';
declare @last_name nvarchar(50)='Yang';
declare @last_name_first_char nvarchar(1);

exec [dbo].[customer_udpate]
	@customer_code = @customer_code
	, @first_name = @first_name 
	, @last_name = @last_name
	, @last_name_first_char = @last_name_first_char output ;

select @last_name_first_char as last_name_first_char ;

This is simple for a single SQL query, but what about in SSIS? Remember for our situation, that the oledb command runs for each row that enters the command (each row in the buffer) and we want to append the output value to our dataset (so that it can be consumed later in the dataflow). So how does our biml for the oledb command look?

<OleDbCommand Name="insert_customer_dynamic" ConnectionName="dest_stage">
	<DirectInput>
		exec dbo.customer_udpate @customer_code=?, @first_name=?, @last_name=?, @last_name_first_char=? output;
	</DirectInput>
	<Parameters>
		<Parameter SourceColumn="customer_code" TargetColumn="@customer_code" DataType="String" Length="15" />
		<Parameter SourceColumn="first_name" TargetColumn="@first_name" DataType="String" Length="50" />
		<Parameter SourceColumn="last_name" TargetColumn="@last_name" DataType="String" Length="50" />
		<Parameter SourceColumn="last_name_first_char" TargetColumn="@last_name_first_char" DataType="String" Length="1" Direction="Output" />
	</Parameters>
</OleDbCommand>
 

Here we can see a few differences since our last implementation.

  • This is given but query has changed and matches the SQL command (note the output clause for the return parameters)
  • TargetColums (for parameters) a match by name rather than the generic Param_n (as in the prior example)
  • The output parameter includes an additional Direction attribute (specifying direction out rather than the default input value)

It should also be apparent that the dataset entering the transform needs to put the return value in a column. That is, we need to append a column to the dataset to store the return value. This brings us to our ‘full’ implementation.

The Full Implementation

If we continue with the name of the prior post, and the stored procedure shown in this post, we can derive a business case by which we want to achieve the following outcomes;

  1. source the customer records
  2. fired the procedure (to insert them into the staging table)
  3. record the procedures return result (even if it is just a simple value)
  4. record the outcome of the entire load (that is, the results for each record loaded)

Finished package (dataflow) is shown in the following image below. Note that we include a derived column(s) that store the return values from our procedures in the dataset as it flows to the destination.

The complete biml for this below. There are no extraordinary considerations that need further explanation other than perhaps the choice of posting the record into the same destination table twice (ie [dbo].[customers]). The only reason this was done was for simplicity and demonstration purposes. If we queried a single customer we could see their entry and audit record in the same table. Certainly not a production solution though.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<OleDbConnection Name="dest_stage" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2008R2;Integrated Security=SSPI;Initial Catalog=stage" />
		<OleDbConnection Name="src_adventure_works" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2008R2;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008R2" />
  </Connections>
	
	<Packages>
    <Package Name="oledb_cmd_02" 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>
						
						
						<DerivedColumns Name="add_first_name_place_holder">
							<Columns>
								<Column Name="last_name_first_char" DataType="String" Length="1">NULL(DT_WSTR, 1)</Column>
              </Columns>
            </DerivedColumns>
				
						
						<OleDbCommand Name="insert_customer_dynamic" ConnectionName="dest_stage">
							<DirectInput>
								exec dbo.customer_udpate @customer_code=?, @first_name=?, @last_name=?, @last_name_first_char=? output;
                            </DirectInput>
							<Parameters>
								<Parameter SourceColumn="customer_code" TargetColumn="@customer_code" DataType="String" Length="15" />
								<Parameter SourceColumn="first_name" TargetColumn="@first_name" DataType="String" Length="50" />
								<Parameter SourceColumn="last_name" TargetColumn="@last_name" DataType="String" Length="50" />
								<Parameter SourceColumn="last_name_first_char" TargetColumn="@last_name_first_char" DataType="String" Length="1" Direction="Output" />
              </Parameters>
            </OleDbCommand>

						<OleDbDestination Name="dest_customer" ConnectionName="dest_stage">
							<ExternalTableOutput Table="[dbo].[customers]" />
            </OleDbDestination>
						
          </Transformations>
        </Dataflow>
      </Tasks>
		 </Package>
    </Packages>
</Biml>
Advertisements

2 thoughts on “Biml XIII – Oledb Command Part II – Return Variables

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