Table-valued parameters (TVPs) are a great way to move chunks of data between your application and SQL Server, while still retaining the ability to abstract database functionality with stored procedures and functions.

TVPs were introduced in SQL Server 2008, but I feel they may be underused, not known, or only just now being put on the table for the developers of vendor applications where support for SQL Server 2005 is finally being discontinued.

I'm putting this post out there for DBAs, because while TVPs are relatively easy to implement as a programmer, not all DBAs have the same set of programming skills. There are many, many good reasons for a DBA to learn the .NET framework in at least one modern language (probably C#), but that's a topic for another post. The point here is to put TVPs in the context of SSIS, where they can be very useful. The only reason this is remotely tricky is because TVPs are not exposed in SSIS directly -- you have to implement them yourself in code.

The first thing we need to do is set up some database objects to play with. The following script creates a user-defined table type (which is the table-valued parameter type), a table into which we'll dump some data, and a stored procedure that has a TVP, which we'll call from our SSIS package.

That's pretty straight-forward. All we're going to do is accept a table as a parameter to the stored procedure, and insert the rows of that table parameter into the base table.

In order to use the procedure, we have to write a little bit of code. Essentially what we must end up with is our data in a DataTable object, which we'll pass as a parameter when we call the stored procedure. There are many different ways to do this in SSIS, depending on where our data is coming from, and what, if anything, we need to do with the data before it gets sent off to the stored procedure.

In this example, I chose to use a Script Component destination inside a Data Flow task. If you're following along at home, the design surface should look like this, noting that the connection manager must be an ADO.NET Connection Manager:

The Target Connection points to our testing database, wherever it was landed.

In the ADO.NET Source, I chose to fabricate some junk data using this query:

The PreExecute method creates the DataTable object with a schema that matches the user-defined table type.

The ProcessInputRow method is called once for each incoming row to the Script Component, so we add a row to the DataTable for every input row.

In PostExecute, we connect to SQL Server and call the stored procedure. The magic happens in the cmd.Parameters.AddWithValue method -- it's smart enough to realize that we passed in a DataTable instead of a simple type, and automatically handles sending the data to SQL Server. How it does this I will leave as an exercise for the reader to discover using Profiler.

As I said before, there are many different ways to incorporate this code into an SSIS package. It's not a lot of code, but may be a little bit tricky for those who aren't fluent .NET programmers.