Dec
5
2012

Using table-valued parameters in SSIS

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.

Oct
25
2012

Consuming a secure web service in SSIS

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.

 

using System.Security.Cryptography.X509Certificates;

private X509Certificate2 _GetClientCertificate(
	StoreLocation storeLocation, string serialNumber)
{
	X509Store store = new X509Store(storeLocation);
            
	store.Open(OpenFlags.ReadOnly);

	try
	{
		foreach (X509Certificate2 cert in store.Certificates)
		{
			if (cert.SerialNumber.CompareTo(serialNumber) == 0)
				return cert;
		}

		// No match
		throw new ArgumentException();
	}
	finally
	{
		store.Close();
	}
}

 

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):

using Services = st_<guid>.csproj.MyService;

Services.MyService svc = new Services.MyService();
svc.Url = "https://www.mydomain.com/MyService.svc";
svc.ClientCertificates.Add(
	_GetClientCertificate(StoreLocation.CurrentUser, "123456"));

svc.MyServiceMethod();

 

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.

Oct
24
2012

Fix for Web Service Task "Cannot find definition for .... Service Description with namespace ... is missing. Parameter name: name"

The problem is that the SSIS Web Service Task does not handle namespaces other than the default.

While the web service WSDL file may validate just fine, SSIS still balks at it. Here's how to fix up the WSDL file.

Before:

<wsdl:definitions
	...
	targetNamespace="http://tempuri.org"
	xmlns:tns="http://tempuri.org"
	xmlns:ns1="http://www.MyServiceTargetNameSpace.com">
	...
	<wsdl:service name="MyServiceName">
		<wsdl:port name="MyPortName" binding="ns1:MyBindingName">
			...
		</wsdl:port>
	</wsdl:service>
</wsdl:definitions>

 

After:

<wsdl:definitions
	...
	targetNamespace="http://www.MyServiceTargetNameSpace.com"
	xmlns:tns="http://www.MyServiceTargetNameSpace.com">
	...
	<wsdl:service name="MyServiceName">
		<wsdl:port name="MyPortName" binding="tns:MyBindingName">
			...
		</wsdl:port>
	</wsdl:service>
</wsdl:definitions>

You could also just overwrite the targetNamespace and xmlns:tns attribute values with your service target namespace. Essentially, they all have to end up being the same namespace for it to work.