SQL Server Integration Services, Execute SQL with Complex Parameters

If you're familiar with SQL Server Integration Services (SSIS) then you've probably, at one time or another, run into problems with the integration bit – however weird that may sound. I can understand the fact that SSIS is supposed to be generic and universal and platform independent, and therefore not favor a particular DBMS – not even the one that it's a part of. Nonetheless, for years now I have wished for SSIS to support SQL Server just a little better, and I'm still waiting... And as far as I know, so is pretty much everybody else I've talked to about this, ever since SSIS first came out.

In this post I discuss one particular issue with SQL Server Integration Services that has been the source of many headaches for me and I bet for a lot of you as well: complex parameters and the Execute SQL Task.

Rise above Primitive

Just what is a complex parameter? Well, a parameter of a complex data type, of course. For example:

• XML – it was introduced with SQL Server 2005, the same version that brought us SQL Server Integration Services. I've discussed XML on several occasions;
• User-defined CLR Types – also introduced with SQL Server 2005; and
• Table-valued Parameters – introduced with SQL Server 2008.

I. XML

Imagine a stored procedure with an XML parameter... Or better yet, take a look at these two:

• The first one returns the current date and time in an output XML parameter:
  

  create proc dbo.GetDate_asXml
   (
   @dateAsXml xml   = null  output
   )
  as
  begin
   with xmlnamespaces
    (
    'http://schemas.testing-ground.com' as tg
    )
   select @dateAsXml
     = (
     select getdate() as [@tg:date]
      for xml path('tg:element'), root('tg:entity'), type
     )
   ;
  end
  go

• The second one accepts an XML input parameter of the type returned by the first procedure, then extracts a date/time value from it, and returns it in a result set:
  

  create proc dbo.ExtractDate_asXml
   (
   @dateAsXml  xml
   )
  as
  begin
   with xmlnamespaces
    (
    'http://schemas.testing-ground.com' as tg
    )
   select @dateAsXml.query
     ('
     data(/tg:entity[1]/tg:element[1]/@tg:date)
     ').value
      (
      '.'
      ,'datetime'
      ) as ExtractedDateTime
   ;
  end
  go

How do we configure an SSIS Execute SQL Task to successfully execute both these procedures?

Data Source Providers and Data Types

The first choice we need to make is between the two data providers available to the task in question (we're connecting to SQL Server):

• OLE DB; or
• ADO.Net.

Next, we need to select the appropriate data types for:

• the SSIS Package variable(s); and
• the procedure's parameter(s).

Olé, DB!

According to the SSIS/SQL/OLE DB data type mapping, documented in the MSDN Library (unfortunately, not all of it in the same place, but that's another story), the SQL Server XML data type should map to the NVARCHAR OLE DB data type. Unfortunately, this is only half-true. None of the OLE DB data types, available to the Execute SQL Task in SQL Server Integration Services can be used for XML output parameters! To add insult to injury, the NVARCHAR data type can be used for XML data type input parameters.

This is the exception raised by the Execute SQL Task when trying to use the NVARCHAR data type for an XML output parameter:

Error: 0xC002F210 at %task name%, Execute SQL Task: Executing the query "%query 
text%" failed with the following error: "Implicit conversion from data type xml 
to nvarchar(max) is not allowed. Use the CONVERT function to run this query.". 
Possible failure reasons: Problems with the query, "ResultSet" property 
not set correctly, parameters not set correctly, or connection not established 
correctly.

Much Ado about .Net

The ADO.Net provider supports the XML data type, so there should be no problems here. Eventually. In fact, I've run into a different problem (explained later in this post), but was able to fix that in the end.

Variables

SSIS supports a subset of .Net data types for the SSIS Package Variables. XML is not supported explicitly, although either String or Object will do if the variable is to be used for Execute SQL Task's parameters – either using the OLE DB or the ADO.Net provider.

How Big is Your XML?

I've mentioned a problem earlier, haven't I? For output parameters of the XML data type, ADO.Net expects the parameter size to be set. The magic number that always seems to be accepted is 2147483647 (2 GB or 2^31 - 1 Bytes), otherwise the following exception will be raised by the Execute SQL Task (even if parameter size is left at -1, which is the default, and apparently does not stand for unlimited):

Error: 0xC002F210 at %task name%, Execute SQL Task: Executing the query "%query 
text%" failed with the following error: "String[0]: the Size property has an 
invalid size of 0.". Possible failure reasons: Problems with the query, 
"ResultSet" property not set correctly, parameters not set correctly, or 
connection not established correctly.

II. User-defined CLR Types

In this test I've used the Point User-defined CLR Type (CLR UDT) used as an example in Books Online.

According to documentation, OLE DB as well as ADO.Net generally do support CLR UDTs via their UDT or Udt data types, respectively. However, neither of these types is available to the Execute SQL Task in SQL Server Integration Services. Using the usual candidates, Object or String, fails as well.

On the other hand, every CLR UDT implements the ToString() and the Parse() methods, making it possible to use string representations of the UDT in the Execute SQL Task. This workaround is both: a lot of work and a long way around the problem. Why? First of all, they require a rewrite of the SQL query or the procedure, or the creation of a "wrapper" procedure that executes the actual procedure, and is executed from SSIS instead of the "real" one. Second, CLR UDTs usually implement additional accessors and operators – without them a complex type is incomplete, and its usability limited.

III. Table-valued Parameters

Introduced with SQL Server 2008, Table-valued Parameters (TVPs) provide a way of passing a set (a table) to a SQL Server module (procedure or function). Before TVPs the only way to pass a set of values to a SQL Server module, using Transact-SQL or another programming language, would be to pack the set up into a delimited string, to use XML, or to rely on other, usually significantly more elaborate means.¹

SSIS has seen its share of changes for SQL Server 2008, but... long story short: SQL Server Integration Services do not support Table-valued Parameters.

According to documentation (MSDN Library), TVPs are supported by OLE DB via its Object data type (I've never confirmed this, though), and by ADO.Net via its Structured data type (confirmed). However, none of these are available to the Execute SQL Task in SQL Server Integration Services.

Mental Aggregation

And here they are, all the conclusions, neatly packed in a table for your benefit:

	Provider	Variable	Output Parameter		Input Parameter
		Data Type	Data Type	Size	Data Type	Size
XML	OLE DB	String or Object	N/A		NVARCHAR	-1 or 2147483647
	ADO.Net		Xml	2147483647	Xml	-1 or 2147483647
CLR UDT	N/A
TVP	N/A

And the conclusion of all conclusions? XML via ADO.Net seems to be the only fully supported complex parameter in the SSIS Execute SQL Task.

I really hope this changes in the future...

ML

¹ Erland Sommarskog has written a few very useful articles dealing with the subject of exchanging data sets between modules.