Achievements

« SQL PASS Keynote : Day(s) 1&2 | Main | SQL University SSIS : Named Pipes -- Producer »
Wednesday
Sep282011

SQL University SSIS : Named Pipes -- Consumer

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 Sequence Container.

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:

 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.  

Now we move into the Work 1 Source Script 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.

 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.

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 Flow Task 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.

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: eye care
    SQL University SSIS : Named Pipes -- Consumer - Journal - SSIS - SQL Server Tidbits

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

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):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>