Proactive Database Administration

SQL Server Integration Services

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.

If you need to use SSIS to consume methods of a web service that require a client certificate, the first thing you need to know is this: the Web Service Task will not get you there. (Regardless of its… other issues.)

The Properties GUI is misleading in that you can specify a certificate to test the connection and evaluate the methods, but that’s as far as it goes — the certificate information isn’t passed along to the underlying HTTP Connection Manager at runtime, and you end up with “403 forbidden” errors for no apparent reason.

The HTTP Connection Manager does have a very tantalizing Certificate property… which can’t be set using an Expression. (Or at least I haven’t figured out how.)

We would have to resort to using a Script Task (or Script Component) to set the Certificate property, but going that route, it’s actually easier to take a different approach entirely within the task.

First, though, let’s take a step back, because we still need a way to get the certificate so it can be used with the secure service.

Below is a Script Task function that will return a reference to a certificate based on a certificate store location and a certificate serial number (run certmgr.msc to view the local certificate store).

Note that this is not a complete solution! You’ll probably want to keep this code in its own script task which sets a package variable, so the certificate is available for all the web service calls you need to make. Also, it would be a good idea to externalize the input parameters so your package is configurable. I’m showing it this way here for simplicity.

The next step is to configure a Script Task to actually call the web service. First, create the new task or component and go into the Visual Studio code editor. Right-click on the project file, and use Add Web Reference to generate proxy classes for your web service.

Now, here is where I’ve had a bit of frustration. Sometimes exiting out of Visual Studio at this point does not correctly save the project file, and you end up with the web reference files in the file system, but not actually in the project. There’s no way to “add” them back to the project the way they were. Sadly, the easiest way I’ve found to clean it up… is to start again with a new Script Task. So what I’ve tried to do is use the Save All function to basically hope and pray that it sticks, then exit out, and go back in to make sure the folder still appears in the project. If it’s still there, we’re good to proceed.

At this point, try to build the project by using the Build | Build st_<guid> menu item. If you get an error “Task failed because “sgen.exe” was not found, or the correct Microsoft Windows SDK is not installed. …” open the project properties, go into the Build tab, and change the Generate serialization assembly option to Off. The project should build successfully now.

So after all this leadup, here is the code to actually consume the web service in the Script Task (or Script Component):

If you need to make many calls to the same web service, it’s possible to add a reference to an external assembly in the Script Task project file, instead of generating the proxy classes directly inside the project. While the steps needed to do this are beyond the scope of this post, a common assembly is a great way to centralize the logic and service references in a larger project.