Thursday, June 11, 2015

Looping Through a Result Set with the ForEach Loop

In today's blog post, I am going to show you another use for the ForEach Loop container in SSIS. Most of the time, this container is used for looping through a group of files, however, there are many different uses for it.

Let's say you have a need to take some rows from a source and aggregate some values. Normally, something like that is done in the data flow, but it can also be done in the control flow.

First, create 5 variables. Pay close attention to the data types.

In the control flow of an SSIS package, add an Execute SQL Task and open the editor. Set the 'Result Set' property to Full result set. Create an OLE DB connection to the AdventureWorks2012 database. Add the following T-SQL  statement.

SELECT Name, SafetyStockLevel, ListPrice
From Production.Product
Where ListPrice < 20
AND ListPrice > 0

This query just pulls a small set of data from the Product table.

Click the 'Result Set' option to the left. Set the Result Name to 0 and for the Variable Name, select objProductList. This variable will hold the results returned by the query. Click OK to close the editor.

Next, add a ForEach Loop container and connect the Execute SQL  task to it. Open the editor and click Collection. In the enumerator drop down menu, select Foreach ADO Enumerator. In the ADO Object Source Variable drop down menu, select objProductList and for the Enumeration Mode, select Rows in the first table.

Open Variable Mappings. In the drop down menu for the first variable, select intStock, then for the next one, select intListPrice. Click OK to close the editor.

Add an expression task inside the ForEach Loop. In the expression editor, use the following expression...

@[User::intTotalValue] = @[User::intStock]* @[User::intListPrice] +  @[User::intTotalValue]

This expression will multiply the number of stock with the list price value of each item and then add that to the value of the intTotalValue variable. Click OK to close the editor.

To test this out, right click the ForEach Loop and select 'Edit Breakpoints'. Select the last option; Break at the beginning of every iteration of the loop.

Now, execute the package. The package will stop and a watch window should open at the bottom of the screen. If it doesn't, click Debug > Windows > Watch 1. In the watch window, add the names of the variables you want to monitor. Keep in mind that the variable names are case sensitive here.

Each time the execution is continued, the variable values will change and the value of intTotalValue will increase. The interesting part about this is that you won't see the final value of intTotalValue. I actually drove myself insane trying to find out what was wrong with the expression, but there is nothing wrong at all. If there is a need to check the final value of the variable, a script task can be use to display the value.

In the Script task editor, select intTotalValue as the ReadOnlyVariable. Then, click Edit Script.

In the script editor, use the following script.


Close the script editor and then click OK to close the script task editor. Remove the breakpoint from the ForEach loop and execute the package. You should see a message box that displays the final value of intTotalValue.

And there you have it. Again, this is something that is normally done in the data flow, but in case you ever encounter a need to do it at the control flow level, you know that it can be done.

Thursday, June 4, 2015

Scheduling Doc xPress with Windows Task Scheduler

A few weeks ago, I posted a tutorial on scheduling a Doc xPress snapshot with a SQL agent job. Today, I will show how this is done with the Windows task scheduler.

For the most part, the steps are similar. The first thing to do is to create that XML request file. From inside a Doc xPress solution, click the 'button that reads 'Create Snapshot Request File'.

This will prompt you for a name and location to which to save the request file. The snapshot request file contains all the information about your solution. I have renamed mine and saved it to a location on my C: drive.

Now, I can create and schedule a task. Open Windows Task Scheduler and select the option to create a new task. On the general tab, give the task a name. Add a desired description and choose the appropriate security options. I have set mine to run whether or not I am logged in, with the highest privileges and configured for my current OS.

Next, click on the 'Triggers' tab. Here, the task can be put on a schedule. The frequency of this task really depends on the environment and how often it changes. I would set it up to run weekly, just to start.

Then, open the 'Actions' tab and click New. Make sure that the selected option in the Action drop down menu is 'Start a program'. In the 'Program/script' field, enter the file path of the Doc xPress executable, surrounded in double quotes, followed by the full path and file name of the snapshot request file, also surrounded by double quotes. The command should look like this...

"C:\Program Files (x86)\Pragmatic Works\Pragmatic Workbench\DocxPress.exe" "C:\Snapshots\LocalEnvironment.xml"

So the Action window should look like this...

Click OK to close the action window. Click OK again to close out the Task Editor. That's all there is to it. As you can see, similar to using the SQL agent.