h1

SSIS For Loop Containers Part 2

March 30, 2012

    In the continuation from my previous post on For Loop Containers I’ll give you a little more practical example of using the container.

    A “Watcher” package is used quite often for checking for the existence of a file in a folder or for a row to be inserted into a table. Lets set one up. Here’s what its going to do:

    1. Check for new records in a source table.

    2. If there are new records in the table, then rows will be inserted.

    3. If there are not any new records, the package will enter into a wait loop, and “sleep” for 1 min

    4. then iterate through the loop again.

    5. Exit the loop once the rows are inserted.

      1. First lets set up the variables we are going to need for this project.

        image_thumb[20]

        Next lets set drag a For Loop container on to the design pane and configure it. Unlike the first For Loop we set up we are going to leave the InitExpression and the AssignExpression blank, because as you remember from earlier they are optional. In the EvalExpression enter this:

        @dtMaxDestinationLoadDate >= @dtMaxSourceLoadDate

        That is all we have to do here. So far this is even easier to configure then the first one, but hold on it’s the use of the control flow that adds the complexity. We will be using a couple of execute SQL tasks to populate our two variable used in the EvalExpression.

        Here is the create Script for the Source and Destination Tables

        CREATE TABLE [dbo].[ForLoopSource](

        [ID] [int] IDENTITY(1,1) NOT NULL,

        [Name] [varchar](50) NULL,

        [LoadDate] [datetime] NULL

        ) ON [PRIMARY]

        CREATE TABLE [dbo].[ForLoopDestination](

        [ID] [int] IDENTITY(1,1) NOT NULL,

        [Name] [varchar](50) NULL,

        [LoadDate] [datetime] NULL

        ) ON [PRIMARY]

        I’ll leave it to you to populate the tables with data, but there are some name generator websites out there that you can get pretty big datasets from if you need them, or you can just pull the data from Adventure Works.

        Drag in an Execute SQL Task and configure your connection. Then enter this code:

        SELECT

        ISNULL(MAX([LoadDate]),‘1/1/1900’)MaxSourceLoadDate

        FROM

        [Demo].[dbo].[ForLoopSource]

        Once you’ve entered the query make sure to set the result set to single row, and then map the result set to the dtMaxSourceLoadDate variable. Then click ok.

        image_thumb[22]

        image_thumb[24]

        Do the same for the next Execute SQL Task but set up for the dtMaxSourceLoadDate.

        SELECT

        ISNULL(MAX([LoadDate]),‘1/1/1900’)MaxDestinationLoadDate

        FROM

        [Demo].[dbo].[ForLoopDestination]

        The next step is to set the conditions that determine which path the package should take. We do this by adding expressions on the precedence constraints.

        image

        When the expression for the constraint on the left @dtMaxSourceLoadDate>@dtMaxDestinationLoadDate evaluates to true, the package will proceed to the data flow task and load the new rows into the destination table.

        When the expression for the constraint on the right @dtMaxSourceLoadDate<=@dtMaxDestinationLoadDate evaluates as true, the package will proceed to the script task which puts the loop in a wait status before it returns to the top and iterates through again.

        Its in this script task that we use the third variable that we set up at the beginning intWaitTime.  Here is the VB.net for the script task

        Public Sub Main()
                Dim sec As Double = Convert.ToDouble(SnippetParameter(“User::intWaitTime”,””,””))
                Dim ms As Int32 = Convert.ToInt32(60 * (sec * 1000))
                System.Threading.Thread.Sleep(ms)
              Dts.TaskResult = ScriptResults.Success
              End Sub

        By making a variable to configure this instead of hard coding the value in the script task gives us the option to later on configure that variable in a table with package configurations. Once the wait is over the package loops again, until the destination table has a date that is greater then the max date in the source table.

        Inside the dataflow you source query should look like this.

        SELECT        Name, LoadDate
        FROM            dbo.ForLoop
        Where LoadDate > ?

        with the parameter mapped to @dtMaxDestinationLoadDate.

        I hope this helps you all get a better grasp on how For Loops can be used to enhance the functionality of you SSIS packages.

        Until Next Time…

      Advertisements

      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s

      %d bloggers like this: