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.
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
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
XMLinput 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/tg:element/@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
Next, we need to select the appropriate data types for:
- the SSIS Package variable(s); and
- the procedure's parameter(s).
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.
SSIS supports a subset of .Net data types for the SSIS Package Variables.
XML is not supported explicitly, although either
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: 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 data types, respectively. However, neither of these types is available to the Execute SQL Task in SQL Server Integration Services. Using the usual candidates,
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.1
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.
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|
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...