Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

31 Days of SSIS – No More Procedures (20/31)

31 Days of SSIS

Time to shift off of configurations and environments and talk more about SSIS packages and best practices.  If you haven’t been following this series and are just joining in, we are at day 20 of the 31 Days of SSIS.  Now that is out of the way, let’s move on.

Today, we are going to discuss a recent best practice that I’ve been promoting surrounding the use of stored procedures as source objects for data flows.  Or to put it more succinctly that in SSIS packages data sources should use table-values functions instead of stored procedures for the SQL Command text.

Best Practice Roots

A couple months ago, I came across the following error message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft OLE DB Provider for SQL Server” Hresult: 0×80004005 Description: “Protocol error in TDS stream”.
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (7) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The odd thing about this error is that it only occurred when executing the SSIS package through DTEXEC or SQL Agent.  Running the package in BIDS presented no issue and the package was always successful.  Redeploy the package and the same issue occurred.

After some research, I found a post on Protocol error in TDS stream.  From there I went to the post Stored procedures are not parameterized views by Adam Machanic (Blog | @AdamMachanic).  And finally, I checked out Jamie Thompson’s (Blog | @JamieT) post SSIS: Using stored procedures inside an OLE DB Source component.

To summarize what these posts talk about, stored procedures do not have a defined output schema.  The output can change depending on the parameters supplied.  Furthermore, the metadata that SSIS uses may not be based on the execution path that you intend to us when SSIS package executes.

Output Contract

When SSIS packages are built there needs to be a reasonable expectation that the SQL command text for the data source will provide the columns that the SSIS package requires.  It would be great if stored procedures could be designed in a fashion that allowed for one and only one output.  This isn’t the case and there are no indications that this will change and probably no true need for it to change.

There is, though, a defined metadata output for table-valued functions.  As they say in the Highlander, “there can be only one.”  Whether the table-valued function is inline or multi-line, there is always a defined set of columns that will be returned.  The SSIS data source knows specifically what will be returned from the function.

It’s still true that someone can change the table-valued function and cause similar issues to occur after deployment.  There is the insurance that side effects from table changes will not negatively affect the execution of the SSIS package with the obscure message above that seems more ominous that it actually is.

Best Practice Wrap-Up

Having read this, you may be wondering if this is much ado about nothing.  If I hadn’t uncovered this issue a few months back I might be thinking the same time.  I’ve used SSIS since SQL Server 2005 was released and that was the first time I recall having this issue.

There was time between now and when I originally encountered this issue.  With one of my clients there was a major release between when I first encountered the error and now.  Would you be surprise to find out that we ran into the same issue three more times?  Working on these issues after a release is no picnic and had I followed the advice I give now these issues likely would not have happened.

Guess what I recommended so that we wouldn’t deal with these again in the future?  Now, that you’ve heard my recommendation, what do you think?

Related posts:

  1. 31 Days of SSIS – Create XML Document (13/31)
  2. 31 Days of SSIS – UnPivot Alternative (12/31)
  3. 31 Days of SSIS – 10 Things About Expressions (16/31)


Posted by Norm Enger on 20 January 2011

I have encountered the same issue over the years. Although some will disagree, I consider this to be a flaw in the design of SSIS, although I understand the reasoning MS may have used. They could implement a work-around, in my opinion, similar to what is available when you use stored procedures in SSRS. When you first set a data source in SSRS to use a stored proc, and click OK on the data source designer, it initially is unable to obtain column definitions and thows an error. However you can work around it by clicking the "Refresh fields" button on the tool bar. SSRS then prompts you for a test execution of the proc, and based on the results returned from the execution, gets the metadata needed and is able to populate the column definitions. From that point on, the data source works perfectly. It should be that easy or easier in SSIS.

I believe I heard that Denali will add new functionality to stored procedures to allow the "Contract" of the column definitions of data returned by the proc to be defined as part of the stored proc definition. I am not sure if this will correct the issue in SSIS however.

Posted by Anonymous on 21 January 2011

Pingback from  Twitter Trackbacks for                 SQL Server Central, 31 Days of SSIS ??? No More Procedures (20/31) - StrateSQL         []        on

Posted by nigel.meakins on 22 January 2011

I totally disagree with this as a best practice. Stored procs offer sometimes essential functionality not available in table valued functions.

There are limitations with tvps. You can easily define your output contract with a conditional statement as below

If 1 = 2


Select ....


Where the .... are the field data types and names which will be picked up by ssis metadata. Put this before the 'meat' of your transformation logic and off you go. Sorry but this provides for a much better data source than your suggestion above.

Posted by Anonymous on 22 January 2011

Pingback from  Stored Procedures or Table-valued Functions for SSIS and SSRS? « Sherry's BI Corner

Posted by Jason Strate on 25 January 2011

@Nigel: What functionality of stored procedures are you not able to access with table-valued functions?

Posted by Jason Strate on 8 February 2011

@Nigel: I'm thinking you are looking at the side-effect change restriction in TVFs.  Where you can't insert, update, or delete outside of the function.  Yes, that can be a limitation.

Leave a Comment

Please register or log in to leave a comment.