« SQL-U Day Three Advanced SSIS : Custom destination components | Main | SQL-U Day One Advanced SSIS : Multi-path execution of Data Flow Component »

SQL-U Day Two Advanced SSIS : Sourcing a stored procedure

Welcome to Day Two of SQL-U, Advanced SSIS class.  Please take a pair of safety goggles and rubber gloves.  Today we are going to dissect a specific type of script component; the source component.  Actually it is a subtype of the source being sql based.  First, a close relative: OLEDB Source.

Lying in front of you is the OLEDB Source component.

Note the four choices you have for your source.  You'll want to cut along the Data Access options and select the SQL Command.  This opens a new window where you can put any valid SQL statement.  Let's build the procedure we are going to insert.

This simple procedure selects an empty result set from a temporary table.  If we graft that on to the SQL Command Text section, you should have something that looks like this: 

So far things look good.  Let's see if the OLEDB Source will accept this.

Host rejection.  Seems as though the component is looking for meta data to populate the columns portion of itself.  Problem is, temporary tables are not available until you actually run the procedure.  This problem is systemic which means you cannot have a temporary table anywhere in a select that returns a result set.  Remember that.  If you have a procedure that returns information from static tables, you are free to use temporary tables within the procedure, except as part of a result set.  Using a stored procedure that doesn't have a temporary table is rare, otherwise you could use a view.  To use a stored procedure that has a temporary table in the result set we need to look at the Script Source component.

If you would discard the OLEDB Component, clean your instruments, and take a Script Component out of the jars next to you.


The Script Source component skeleton has 3 methods, which are self-explanatory.  The PreExecute method fires when the component is activated, the PostExecute method fires after all of the records have been sent down the line, and the CreateNewOutputRows is where you build your output and note it too is only called once!  Now let's look at a fully formed Script Source designed to read data from a stored procedure. 

Here we have the PreExecute and PostExecute methods.

In the PreExecute method I need to set up the connection to be able to access the stored procedure.  First you create a connection object to my server/database.  You then create the actual procedure call.  Notice you need to pass your parameters by name.  As a side note, I call all of my stored procedures by parameter.  I prevents nasty issues when defaults are added and no one told me, not that it EVER happens.  When you add the parameters you need to convert the data to proper SQL datatypes and you're done.  Rather simple.  Take a moment to look at it and make sure you understand the structure.

Beneath the PreExecute method you will see the PostExecute method.  This is called after all of the data has been sent and SSIS is finished with it.  All that is done here is a little bit of housecleaning.  If the connection is open, I close it.  If you look close you will see a little structure near the bottom called ComponentMetaData.FireError.  This is a very useful structure, but not required for the component to operate.  You see these now and again in the wild.  It enables the component to log information to the local windows error log as opposed to a nasty error in the window or none at all.

So, now you see how the component starts up and how it shuts down.  We need to dig a little deeper into it to see how it works.


Keep in mind, this method is called only once in the life of a component.  To add multiple records to the output stream, you need to loop over the result set.  The While Loop reads "so long as I have data in my reader, add a row to the output and fill that row".  Let's break that up a bit.

SSIS works by filling buffers from a source and working on those buffers all the way down the line to a destination, where it is consumed.  Each row is a buffer, with one or more columns.  So think of it as a one-to-one relationship rows to buffers.  If the stored procedure housed in this component returns 100 rows, I will create 100 buffers.  I create a buffer by calling "Outputbuffer".AddRow().  Take note of the Engine1ProcBuffer, as that can be anything you like.  I will show you where to change that.  The important note is the method AddRow()

We now need to fill that row and you do so by assigning a value to an output field name.  In this specimen there is one and only one output column and it is called ISBN13.  The formula is very simple:

  1. Call AddRow() method on your output buffer
  2. Assign each output column a value using OutputBuffer.<column_name>=<value>
  3. if there is another row of data, go back to step 1.
  4. Call OutputBuffer.SetEndOfRows()

The last piece of this method is the SetEndOfRows() which tells SSIS the source is now empty.  Now that you've taken apart the insides, let's flip it over and look at the outside for a moment.  You need to tie them together so to speak.  Remember two tags:Engine1ProcBuffer and ISBN13.

Here is the outside of the component.  Pay careful attention to the Inputs and Outputs tab.

 I've named the output buffer Engine1Proc.  I've named a single output column ISBN13.  This ties the inside to the outside!  If you need more than one column, you simply add them here.  If you need an additional output, you create it here.  Before I send you home for the day, I want to show you something about the care and feeding of this component.  

When I was testing this out, I go the following all green canvas, but something was not right.

I was expecting a few hundred rows.  I double-checked the source procedure and it was good.  You can't debug inside a Script Task, so I wasn't quite sure what the issue was.  I was getting no error code (all green and nothing in the event log).  I hopped on over to the Execution Results tab and noticed tucked in under all the other results was this error:

Then it dawned on me.  I built the ISBN13  column too small.  The value returning from the stored procedure is 13 characters, but I made the column 10 characters wide.  It is vital to the survival of your component that you match data types AND sizes correctly between your internal result set and the external columns you are looking to fill.

I hope this in-depth examination of a Script Component : Store Procedure Source has given you the knowledge to build your own.  There are a few pieces you have to remember to connect inside and out and you don't many of the luxuries of the OLEDB Source, but if you can't do it without a procedure as a source this is the only way to go.

If there are any questions, please feel free to contact me using the Contact Link on your right as you exit the page.  Tomorrow's class will be on another variant of the script component, the destination component.  Bring your goggles, a fresh pair of gloves, and I'll see you all back here tomorrow.  

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (3)

  • Response
    Response: review
    SQL-U Day Two Advanced SSIS : Sourcing a stored procedure - Journal - SSIS - SQL Server Tidbits
  • Response
  • Response

Reader Comments (2)

Why not just add this to the front of the stored procedure?

If 1=2
SELECT 1 ColA, 1 ColB

March 20, 2012 | Unregistered Commentermerl

Two reasons. First, that only works if you have access to the procedure (what if it is a third party or you aren't allowed to modify it). Second, it would still be less efficient, since SSIS would have to call it 5 times for meta-data.

April 29, 2012 | Registered CommenterJosef Richberg

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):