SSIS and data contracts

Have you ever tried to use a stored proc with a temp table in an SSIS package?  Did it work?  SSIS is severely hampered by the fact that this doesn't work seamlessly.  In this post I'll demo the problem and I'll give you the best fix for it.  The objective is to run a stored proc from SSIS and optionally send the output to a text file. 

Repro 

• Create a very simple stored proc.  You can download the repro script here.  Our stored proc simply builds a temp table, inserts a few rows and then reads those rows as a result set to the caller.  No parameters...very simple.  
• Create a new SSIS package.  
• Add a Data Flow Task 
•  
• Double-click it.  
• Add an "OLE DB Source" to the designer
• 
• Connect to your instance/db where you have dbo.SampleDataContract installed
• Change the "Data access mode" to SQL Command and enter the call to our stored proc
• 
• Click "Preview..."
• You should immediately generate an error 
• 
• The error text is:  

Exception from HRESULT:0xC020204A
Error code: 0x80004005
Description: "Invalid object name ''.".
Unable to retrieve column information from the data source. Make sure your target table in the database is available.  

This error occurs because you decided to use a temp table (#temptable...the kind in tempdb) somewhere in the text of your procedure.  That's not allowed.  SSIS (and SSRS and a number of ORM tools, etc) attempt to read the metadata about your procedure to determine what the output will look like (number of columns, names, and data types).  The error is telling you that the metadata could not be found for the temp table that you are attempting to use.  

BTW, this will not happen if you usse @TableVariables.  However, there are performance ramifications of using table variables in some versions of SQL Server.  

Data Contracts to the Rescue

I've written about [[data contracts for stored procedures]] before.  Quick recap...in the Oracle world you CANNOT return a result set from a stored procedure.  Instead you create a package to do this.  In the package "header" you declare the "data contract" (inputs and outputs, including ordinal position, name, and datatype).  Then, in the package body you write the actual code and that code must adhere to the declared data contract.  In the example, emp_actions is the package and it implements 2 procedures, a cursor (which is nothing like a SQL Server cursor, it is more like an ADO cursor in that it is a "CURrent Set Of Records"...ie a result set) and the output format of the cursor, which is known as a record type.  Note that the package body actually outputs the data when someone queries for the desc_salary result set.  

When I first started working with Oracle I thought this syntax was EXTREMELY cumbersome.  Too much typing of redundant information (a CURSOR is declared over a TYPE and then the actual query has to be bound to the CURSOR and to the TYPE.  Too much can go wrong here.  

I thought this was cumbersome until I started working with SQL Server in the Nineties.  SQL Server then only partially declared the full data contract.  Stored procs declared the inputs but not the outputs.  In later releases M$ graciously gave us functions and table-valued parameters which FULLY implement data contracts.  But they never fixed procedures to fully support data contracts.  Why do we need data contracts for stored procs?  See this connect item for a list of reasons...but having a true data contract solves the SSIS problem I noted above.  In this post a blogger lists exactly what he would want to see in a fully implemented data contract for stored procedures.  That's a great list.  

While having full-blown data contracts would be nice, you don't have to wait for M$ to roll your own data contracts and solve some of these issues.  Here's how I do it.  At the top of your stored procedure add a little code block that can NEVER execute that defines your data contract.  When these "smart tools" like SSIS, SSRS, and most ORMs try to read your metadata they see that you have a SELECT statement and determine that is the "shape" of the result set.  Even though the SELECT code block is in a IF block that will never be executed (note the IF 1 = 0).  Just doing that little bit of work is enough to fool EVERY "smart tool" that looks for result set metadata.  

Mocking up a data contract really fixes this?

Yes.  Let's demo.  Go back to SSIS to your OLE DB Source component.  Click the "Preview..." button again.  Last time we got an error.  This time we get our result set as expected.  

Summary

Creating your own mocked-up data contract is an easy way to overcome tools that attempt to read the metadata of your procedures and fail because they contain references to a temp table.  If your SSIS packages require the IsSorted flag you can even put an ORDER BY clause on your "data contract" and your output will automatically have the IsSorted flag set.  

You have just read "[[SSIS and data contracts]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.