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.

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 on
use tempdb
if object_id('dbo.persontest','u') is not null drop table dbo.persontest
CREATE 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 Programmability\Types\User 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 
END
GO

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.

1. Add another ADO.NET connection, but this time to the tempdb database. Rename it sqlTempDbDestination.
2. Right-click the Control Flow Designer and select variables. Add a variable named rsPersonTest with a data type of Object.
3. Drag a Script task to the Control Flow Designer.
4. Drag a Data Flow task to the Control Flow Designer. Double-click this object to get into the Data Flow tab.