Passing a Table Variable from SSIS

Table variables have been around since SQL Server 2000, but in SQL Server 2008 you can use them as parameters. No more comma-separated strings, parsing with functions, staging tables, custom CLRs, tricky uses of the file system, or bulk inserts. Combine table variables with the power of SQL Server Integration Services (SSIS) and you can implement an inserting task that reduces the need for row-by-row processing, simplifies code for maintainability, and keeps trips to the database at a minimum. In this article, I'll show you how to create a stored procedure that takes a table variable as a parameter and an SSIS package that implements it.

Related: SSIS Package Pings Servers

Set Up a Table Variable Parameter

First, run the code in Listing 1 to create the table.

Listing 1: DDL Statement to Create PersonTest Table set nocount onuse tempdbif object_id('dbo.persontest','u') is not null drop table dbo.persontestCREATE TABLE \[dbo\].\[PersonTest\](     \[PersonFirstName\] \[varchar\](50) NULL,     \[PersonLastName\] \[varchar\](50) NULL )

PersonTest is a simple table with first and last names. The code in this article places all objects in tempdb.

The table variable parameter depends on a user-defined table type that's stored in the system tables and displayed in the programmability hierarchy of SQL Server Management Studio (SSMS). Listing 2 shows the code to create your table type, called PERSONTYPE.

Listing 2: DDL Statement to Create User Defined Person Type  IF EXISTS (SELECT * FROM sys.types st   JOIN sys.schemas ss ON st.schema_id = ss.schema_id  WHERE st.name = N'PersonType' AND ss.name = N'dbo')DROP TYPE \[dbo\].\[PersonType\] CREATE TYPE \[dbo\].\[PersonType\] AS TABLE(  \[personfirstname\] \[varchar\](50) NULL,  \[personlastname\] \[varchar\](50) NULL) 

This type will be the structure for your table variable. One limitation of user-defined types is that they maintain a relationship with the items that use them. If you need to make modifications to this type later, you'll need to remove the reference in your stored procedure. See the sidebar at the end of this article for more limitations.

Once created, you can view your object in SSMS under ProgrammabilityTypesUser Defined Table Types, but you won't be able to modify the structure from the SSMS GUI. You need to use T-SQL to drop and recreate this object.

Create Stored Procedure to Take the New Table Type

Listing 3 gives the T-SQL code to create a stored procedure to accept this type as a parameter.

Listing 3: DDL Statement to Create Stored Procedure with @table parameter IF EXISTS (SELECT * FROM sys.objects  WHERE object_id = OBJECT_ID(N'\[dbo\].\[insertPersons\]')  AND type in (N'P', N'PC'))DROP PROCEDURE \[dbo\].\[insertPersons\]GO CREATE PROCEDURE \[dbo\].\[insertPersons\] (@Persons PersonType READONLY ) AS BEGIN SET NOCOUNT ON; insert into PersonTest select * from @Persons ENDGO

Note that your parameter is read only, although this limitation might be lifted in future releases. Test your stored procedure with the following code:

Declare @mytable PersonType    insert into @mytable select 'firstnametest', 'lastnametest'    execute insertpersons @mytable

Also while testing, try to drop your new table type.

DROP TYPE \[dbo\].\[PersonType\]

Note that you'll have to alter your stored procedure if you want to drop your table type. If you try the above drop statement, you'll get the following error:

Msg 3732, Level 16, State 1, Line 2 Cannot drop type 'dbo.PersonType' because it is being referenced by object 'insertPersons'. There may be other objects that reference this type.

Create an SSIS Package

Open Visual Studio and create a new blank Integration Services Project from the Business Intelligence Project type. In the Control Flow tab

Right-click the Connections Manager section of the Control Flow tab and add a new ADO.NET connection to the AdventureWorks database. If you don't have AdventureWorks, any source with a table containing first and last names will do, assuming the data types match and you use the same data provider for both connections.

In the Data Flow tab

In the Control Flow tab

Now use the following code to import.

Imports System.Data.OleDbImports System.Data.SqlClient

That's it. Now the easy part. Add your code to the main sub using the code in Listing 4. Note that you're using the AddwithValue method, which takes a string (the parameter) and an object (which lets you use your datatable).

Listing 4: Adding code to the Main SubDim oleDA As New OleDbDataAdapterDim dt As New DataTableoleDA.Fill(dt, Dts.Variables("rsPersonTest").Value)Dim sqlConn As SqlConnectionDim sqlCmd As New SqlCommandsqlConn = CType(Dts.Connections("sqlTempDbDestination").AcquireConnection(Dts.Transaction), SqlConnection)sqlCmd.CommandType = CommandType.StoredProceduresqlCmd.CommandText = "insertPersons"sqlCmd.Parameters.AddWithValue("@Persons", dt)If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()sqlCmd.Connection = sqlConnsqlCmd.ExecuteNonQuery()If sqlConn.State = ConnectionState.Open Then sqlConn.Close()Dts.TaskResult = ScriptResults.Success

Close out of the Script Task and run your package with F5. If you get an error, you can always review the Progress tab or set a break point in the script task code to find out what went wrong. (When I first ran mine, I received an error because I'd forgotten to rename my destination connection properly.) When everything turns green in your package, it will have migrated your list of names from AdventureWorks to your Tempdb.PersonTest table utilizing your insertPersons procedure, including your precious new @persons table variable parameter.

Code Maintainability

There are many different paths that you can choose to move data around, but in my book, code maintainability is just as important as performance. Keeping the majority of your code in one place and making it easy to fix later keeps the office sane.

The entire reason I jumped into using table variable parameters was that I wanted to use the new Merge function with SQL Server 2008. Using this method, I could feed an entire set of data to my procedure instead of making a call for every record. While SSIS performs well with row-by-row processing, sometimes it just isn't necessary. This simple method for using table variable parameters with SSIS shows how you can simplify code for maintainability, reduce the number of hits on the database, and keep all your data manipulation tasks in one place—the stored procedure, where you and I are most comfortable.

Sidebar: Limitations of Using the Table Type as a Parameter

Related Reading: