Passing Table-Valued Parameter in SSIS 2008
By : Kasim Wirama, MCITP, MCDBA
One of new TSQL enhancements in SQL Server 2008 is table-valued parameter. The challenge is how to pass table valued parameter into stored procedure from SSIS. This process involves 2 major steps. First step is to save the resultset into SSIS object-type variable. Second step is to fill SSIS object-type into ADO.NET datatable, which, in turns, pass the data table into stored procedure table valued input parameter.
Let’s try to scenario here. You need to pass customer information (first name and last name) from 1 database (AdventureWorks) to other database (let’s say tempdb database). The tempdb database contains customer table here :
CREATE TABLE dbo.Customer
(
FirstName VARCHAR(50),
LastName VARCHAR(50))
You create the stored procedure to insert new data into Customer table here :
CREATE PROCEDURE dbo.InsertCustomer
(
@CustomerInfo AS CustomerInfoType READONLY
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT FirstName, LastName FROM @CustomerInfo;
END;
Notice that CustomerInfoType is user defined type with table type, the definition of the type is shown here :
CREATE TYPE CustomerInfoType AS TABLE
(
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL
);
Create the type first before you create the stored procedure.
Now you open new SSIS project from Visual Studio 2008. Create new package.
First step is to save source data into SSIS object-type variable. So create SSIS variable with object type and name it tempCustomer. Drag Data Flow Task into Control Flow designer and double click on it to switch into Data Flow designer. At this stage, we need to specify source data where customer information comes from. Drag OLEDB source, and double click to open OLEDB source editor. Click New button to specify new OLE DB connection manager, with database name : AdventureWorks and click Test Connection button. Once OLE DB connection manager has been created and selected on OLE DB Source Editor, choose data access mode : Table or View and table name : Person.Contact.
Click “Columns” on left pane, and select only FirstName and LastName by ticking the check boxes and click OK to close OLE DB Source Editor dialog window. Now you are returned back to Data Flow designer. From SSIS toolbox, drag Recordset Destination onto Data Flow designer and drag connection arrow from the OLE DB Source to the Recordset Destination. Double click on the Recordset Destination instance and set SSIS variable (tempCustomer) into the instance property called VariableName. Move to second tab (Input Columns) and make sure 2 columns (FirstName and LastName) are selected and click OK to return back to Data Flow Designer. You need to create 1 connection manager as destination of data transformation with ADO.NET Destination type. Double click on the new connection manager, and specify database name is tempdb where dbo.Customer and its stored procedure reside. Click OK to close the connection manager. You can rename the ADO.NET destination connection manager to remember easily, for example : destination.
Now you will configure how SSIS variable will be passed into table valued type stored procedure. I will tell you how at second step below.
Switch to Control Flow designer and drag Script Task into the designer and connect arrow from Data Flow Task to Script Task. Double click on Script Task to open Script Task Editor. At Script page, select your language preference (VB.NET or C#), by default it is C#. Specify ReadOnlyVariables to SSIS variable : tempCustomer. Then click Edit Script button to open Visual Studio Tools For Application (VSTO).
In the VSTO, specify 2 namespaces (using System.Data.Oledb and System.Data.SqlClient). Move to Main routine and create 3 instance here :
OleDbDataAdapter oledbDA = new OleDbDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
Next code is to fill datatable from SSIS variable through data adapter here :
oledbDA.Fill (dt, Dts.Variables[“User::tempCustomer”].Value;
Next step is to create instance of destination connection (created before second step, named destination) here :
SqlConnection conn = (SqlConnection)Dts.Connections[“destination”].AcquireConnection(Dts.Transaction);
The remaining step here is typical steps to execute stored procedure in .NET.
cmd.Connection = conn;
cmd.CommandText = “dbo.InsertCustomer”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@CustomerInfo”, dt);
If (conn.State == ConnectionState.Closed) {conn.Open(); }
cmd.ExecuteNonQuery();
If (conn.State == ConnectionState.Open) {conn.Close(); }
Dts.TaskResult = (int) ScriptResults.Success;
Save the code and compile it. Close all dialog windows and run the package. If it runs successfully, check into destination table to see all customer information has been populated into destination.
That’s the way how you could leverage TSQL new feature in SQL Server 2008 in SSIS.