Kirk Haselden

Occasionally you want to do something that isn't supported as a stock operation in SSIS. Fortunately, Integration Services is designed for extensibility so that if what you want to do isn't already supported, you can easily build your own solution with custom tasks and transforms or script tasks and transforms.

Recently on the beta newsgroup, Thomas Pagel asked how to call a stored procedure per row with an out parameter and apply the value from the out parameter to the current row. The OLEDB Command Transform seems the logical approach to this, but it's not apparent how to apply the returned value to the current row. So, I set about doing it with a script transform. Here's some code that shows how to retrieve a connection, connect with it, execute the stored procedure, retrieve the out parameter and apply it to the row, in this case inserting a new key into the current row.

I defined a stored procedure since I didn't have Thomas' SP and needed to simulate the environment. It's defined like this:
ALTER PROCEDURE [dbo].[spTest]
  @ParameterName1 [char](10),
  @ParameterName2 [int] OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
  SELECT @ParameterName2 = ID
  FROM NewID
  WHERE ID = (SELECT TOP 1 ID
              FROM NewID
              ORDER BY ID DESC)
  SELECT @ParameterName2 = @ParameterName2 + 1
  INSERT INTO NewID VALUES (@ParameterName2)
END;


I defined a working table for the stored procedure to work against. It's defined like this:

USE [AdventureWorks]
GO
CREATE TABLE [dbo].[NewID](
 [ID] [int] NOT NULL
) 
 
This is the script for the script transform:
Public Class ScriptMain
Inherits UserComponent
Private null As DBNull
Private objConn As System.Data.OleDb.OleDbConnection


 'Set up the connection
 Public Overrides Sub PreExecute()
  Try
   'Create connection
   objConn = Connections.AW.AcquireConnection(null) 'null for no transaction

   'Try to open the connection
   objConn.Open()
  Catch ex As Exception
   'Log exception
   'System.Windows.Forms.MessageBox.Show(ex.ToString())
  End Try
  'Call the base class PreExecute to handle the default behavior
  MyBase.PreExecute()
 End Sub


 'Clean up this transform
 Public Overrides Sub PostExecute()
  'Close the connection
  objConn.Close()
  'Call the base class PostExecute to handle the default behavior
  MyBase.PostExecute()
 End Sub


 'Process each row
 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  'Declare the command object
  Dim objComm As OleDb.OleDbCommand
  'Create command
  objComm = New OleDb.OleDbCommand("spTest", objConn)
  'Executing a stored procedure
  objComm.CommandType = CommandType.StoredProcedure
  'Set up the parameters
  Dim IDParm1 As New OleDb.OleDbParameter("@ParameterName1", "Empty")
  Dim IDParm2 As New OleDb.OleDbParameter("@ParameterName2", 0)
  'Second parameter is an output parameter
  IDParm2.Direction = ParameterDirection.InputOutput
  'Add the parameters to the command
  objComm.Parameters.Add(IDParm1)
  objComm.Parameters.Add(IDParm2)
  'Execute the query to get the latest ID
  objComm.ExecuteNonQuery()
  'Build a message box string, uncomment the next three lines to see messageboxes with new values.
  'Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
  'sb.AppendFormat("Value of the Parm1 is {0} and Parm2 is {1}", IDParm1.Value, IDParm2.Value)
  'System.Windows.Forms.MessageBox.Show(sb.ToString(), "Parameter Value", Windows.Forms.MessageBoxButtons.OK)
  'Set the NewID (Key) to the returned out parameter value
  Row.NewID = IDParm2.Value
 End Sub
End Class

Don't forget to create a connection manager to the destination database (AdventureWorks in this case) 
and specify the connection manager on the connections tab in the script transform. 
I gave it the name AW,which is what you see in the script transform code above.
 
As always, if you have questions or input about this, please give feedback. 
 
Thanks,
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden