Voluntary DBA

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.

CREATE TYPE [dbo].[TestTable] AS table
(
	Column1 varchar(10) NOT NULL,
	Column2 varchar(10) NOT NULL
);
GO

CREATE TABLE [dbo].[TestData]
(
	Id int IDENTITY PRIMARY KEY,
	Col1 varchar(10) NOT NULL,
	Col2 varchar(10) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[TestProcedure]
(
	@testData [dbo].[TestTable] READONLY
)
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [dbo].[TestData](Col1, Col2)
		SELECT
			Column1,
			Column2
			FROM @testData;

END
GO

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:

SELECT 'a' AS c1, 'b' AS c2 UNION ALL
SELECT 'c', 'd' UNION ALL
SELECT 'e', 'f'

Super junky.

There are two other settings to change in the Script Component before we start coding. First, select both input columns on the Input Columns tab:

and then expose the Target Connection connection manager:

Now we’re ready to write the code that calls the stored procedure. Edit the script of the Script Component and update the methods as follows:

using System.Data.SqlClient;  // Add this

...

public class ScriptMain : UserComponent
{
    private DataTable _data = null;  // Add this

    public override void PreExecute()
    {
        base.PreExecute();

        _data = new DataTable();

        // This matches the schema of the user-defined table type
        _data.Columns.Add("Column1", typeof(string));
        _data.Columns.Add("Column2", typeof(string));
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Make sure the order of the values matches the schema above
        _data.Rows.Add(new object[] { Row.c1, Row.c2 });
    }

    public override void PostExecute()
    {
        base.PostExecute();

        // Create a connection
        using (SqlConnection conn = (SqlConnection)Connections.Target.AcquireConnection(null))
        {
            try
            {
                // Open the connection
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                // Prepare a stored procedure command to execute
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[dbo].[TestProcedure]";

                // Add the parameters for the command
                cmd.Parameters.AddWithValue("@testData", _data);

                // Execute the stored procedure
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Clean up
                if (conn != null)
                    Connections.Target.ReleaseConnection(conn);
            }
        }
    }
}

 

• 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.