Blog Post

SQL University SSIS : Named Pipes -- Consumer

,

SQLU_Tag.png

Welcome to the third and last day of the Named Pipes seminar.  Today we will get into the consumer portion of the framework.  We will focus on getting a piece of information from the producer to the consumer and then moving some of that information into the Data Flow.

The basic layout of a consumer will be a self-contained entity.  The theory is you build it to not only interact with the producer, but to work on that single unit of work that will be passed to it.  Here is the basic layout of a consumer.  All you need to do is encapsulate all of your work in a For Loop, which controls if you have work to do.  To keep things neat, I wrap everything inside a SequenceContainer.

Consumer_outside.png

We are going to use the For Loop like a while loop. So long as there is work to be done, the loop will be valid.  Here is how we do that:

For_Loop.png

 The variable workflow_1 is set to 1 when created.  This validates the loop and as you will see, the Script Component will have control over that.  Yes, I said Script Component.  Many might be tempted to use a Script Task outside of the Data Flow component and in previous incarnations I did just that, but realized that is a waste.  I had to create a variable for each piece of information I wanted to retrieve from the pipe, for each of these consumers.  In my original InvoiceArchive system I had 8 'consumers' (they are really engines and do not use producer/consumer model as I have it here) with 7 variables each.  I wanted to slim this down and decided to have the DataFlow itself pull the values (through a Script Component).  There is a downside to this, which I will get to later.

Here is a peek inside the simplistic DataFlow object.  

Consumer_dataflow.png

Now we move into the Work 1 SourceScript Component - Source. Once you've set-up your script to be .Net 3.5 and imported the System.IO.Pipes and System.IO libraries, we are off to the races.

You need to create an output column so you can pass the filename down the data path and remember to include the workflow_1 variable as read/write so you can interact with it.

Consumer_Loop.png

What is done here is very similar to what we do for the producer.  We create a pipe object, this time a NamedPipeClientStream connecting to the "testpipe" pipe on the local server (".").  We will then wait 600 ms.  Remember when I stated in yesterday's class how we need a way to let the consumers know if there is no more data; this is part of that mechanism.  After 600ms a System.TimeoutException is thrown, which is a good thing.  As you will see, this gives the component the ability to check and see if we were waiting too long or there really is no work to be done.  If there is no time-out or error, I read a single line of information, which I know to be the full path filename and pass it down the flow as the FileName variable.  If we do have an error, I do some additional checking to see if the consumer should end or try again for more data.

Consumer_error_handling.png

Remember if it takes longer than 600ms to connect to the producer, a System.TimeoutException is thrown.  That out_of_band variable is how the producer tells the consumers there is no more work.  Only the producer can modify that variable, which starts out as a 1.  If there is another error, then I assume I have no work to do and set my variable to 0, which will fail the For Loop on the next pass.

There is one GOTCHA to be aware of.  The Data FlowTask will run even if there is no data.  Remember, it is valid for the consumer to be unable to connect to the producer.  In the simple example that I have, the Import Column will pass through without issue.  Make sure any other custom components you might have or other objects within the workflow can handle that.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating