Task Factory–File Properties Task

My last video on the Task Factory Compression Task was well received, so I thought I’d build on it with a video on the File Properties Task. It’s a cool little tool that will grab any of the various attributes associated with a file. If you want to see more about Task Factory, you can head on over to my employers website, Pragmatic Works.com

PASS Summit 2011 Community Choice–I need your help!

Throwing out  call for help from all my friends in the tech community. My “Project Juneau” (next version of “Data Dude") session is up for a community choice slot at this falls SQL PASS summit. Five of the twenty sessions in the list will be picked for slots at this falls PASS Summit.

I would appreciate your help in getting selected! I know there are a lot of great names on the list, and so it’s a tough choice, but if you’ve seen my sessions on Data Dude (Visual Studio Database Projects) you’ll know what a powerful tool it is, and how the changes in the next version will make it even more so.

While you are there, you should also consider sending a vote for a few of my friends and co-workers. Mike Davis and Adam Jorgenson are doing a “SSIS vs T-SQL: Loading a Data Warehouse” session, and  Jorge Segarra (the infamous SQLChicken) is up for “Policy-Based Management in a Nutshell”.

You can vote at: http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx 

Deadline for votes is July 20th, so as the old saying goes “day don’t delay, vote today!”

If you are already a PASS website member voting only takes a few seconds. My session is easy to find, it’s the very first one on the list.

If you’re not a PASS member it’s quick and easy to join. Totally 100% free, just fill in the form and not only will you be able to vote for sweet little old me, but have access to great training videos and other information. And just because you may be a .Net developer don’t overlook the usefulness of good SQL Server resources. Understanding the power of SQL Server can make a huge difference in the performance of your application.

And if all that wasn’t enough to convince you, then I’ll resort to a shameless plea. It just so happens today is my birthday, and a vote for my session would make a great gift. You don’t even have to wrap it!

Revisting the Outlook Save All Attachments Macro

Back in 2007 I created a post on an Outlook Macro to save all attachments for an e-mail. Some people were having a problem downloading the file (not sure why as it worked for me) but to help out thought I’d repost the code in full.

Note I have not tried this with Outlook 2010, so use at your own risk.


Option Explicit


Public Sub SaveAttachments()

  'Note, this assumes you are in the a folder with e-mail messages when you run it.
  'It does not have to be the inbox, simply any folder with e-mail messages
  
  Dim App As New Outlook.Application
  Dim Exp As Outlook.Explorer
  Dim Sel As Outlook.Selection
  
  Dim AttachmentCnt As Integer
  Dim AttTotal As Integer
  Dim MsgTotal As Integer
  
  Dim outputDir As String
  Dim outputFile As String
  Dim fileExists As Boolean
  Dim cnt As Integer
  
  'Requires reference to Microsoft Scripting Runtime (SCRRUN.DLL)
  Dim fso As FileSystemObject
    
  Set Exp = App.ActiveExplorer
  Set Sel = Exp.Selection
  Set fso = New FileSystemObject

  outputDir = GetOutputDirectory()
  If outputDir = "" Then
    MsgBox "You must pick an directory to save your files to. Exiting SaveAttachments.", vbCritical, "SaveAttachments"
    Exit Sub
  End If
    
  'Loop thru each selected item in the inbox
  For cnt = 1 To Sel.Count
    'If the e-mail has attachments...
    If Sel.Item(cnt).Attachments.Count > 0 Then
      MsgTotal = MsgTotal + 1
      'For each attachment on the message...
      For AttachmentCnt = 1 To Sel.Item(cnt).Attachments.Count
        'Get the attachment
        Dim att As Attachment
        Set att = Sel.Item(cnt).Attachments.Item(AttachmentCnt)
        outputFile = att.fileName
        fileExists = fso.fileExists(outputDir + outputFile)
        Do While fileExists = True
          outputFile = InputBox("The file " + outputFile _
            + " already exists in the destination directory of " _
            + outputDir + ". Please enter a new name, or hit cancel to skip this one file.", "File Exists", outputFile)
          'If user hit cancel
          If outputFile = "" Then
            'Exit leaving fileexists true. That will be a flag not to write the file
            Exit Do
          End If
          fileExists = fso.fileExists(outputDir + outputFile)
        Loop
        
        'Save it to disk if the file does not exist
        If fileExists = False Then
          att.SaveAsFile (outputDir + outputFile)
          AttTotal = AttTotal + 1
        End If
      Next
    End If
  Next
  
  'Clean up
  Set Sel = Nothing
  Set Exp = Nothing
  Set App = Nothing
  Set fso = Nothing
  
  'Let user know we are done
  Dim doneMsg As String
  doneMsg = "Completed saving " + Format$(AttTotal, "#,0") + " attachments in " + Format$(MsgTotal, "#,0") + " Messages."
  MsgBox doneMsg, vbOKOnly, "Save Attachments"
  
  Exit Sub
  
ErrorHandler:

  Dim errMsg As String
  errMsg = "An error has occurred. Error " + Err.Number + " " + Err.Description
  Dim errResult As VbMsgBoxResult
  errResult = MsgBox(errMsg, vbAbortRetryIgnore, "Error in Save Attachments")
  Select Case errResult
    Case vbAbort
      Exit Sub
      
    Case vbRetry
      Resume
      
    Case vbIgnore
      Resume Next
      
  End Select
    
End Sub

'Found this code in a google groups thread here:
'http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_thread/thread/7187886c3c83a570/c278a2753e9e7ceb%23c278a2753e9e7ceb
'or http://shrinkster.com/l0v
Public Function GetOutputDirectory() As String
 
  Dim retval As String 'Return Value
  
  Dim sMsg As String
  Dim cBits As Integer
  Dim xRoot As Integer
  
  Dim oShell As Object
  Set oShell = CreateObject("shell.application")

  sMsg = "Select a Folder To Output The Attachments To"
  cBits = 1
  xRoot = 17
  
  On Error Resume Next
      Dim oBFF
      Set oBFF = oShell.BrowseForFolder(0, sMsg, cBits, xRoot)
      If Err Then
        Err.Clear
        GetOutputDirectory = ""
        Exit Function
      End If
  On Error GoTo 0
  
  If Not IsObject(oBFF) Then
    GetOutputDirectory = ""
    Exit Function
  End If
  
  If Not (LCase(Left(Trim(TypeName(oBFF)), 6)) = "folder") Then
    retval = ""
  Else
    retval = oBFF.self.Path
    
    'Make sure there's a \ on the end
    If Right(retval, 1) <> "\" Then
      retval = retval + "\"
    End If
  End If
  
  GetOutputDirectory = retval
  
End Function


Task Factory–TF Compression

In previous posts I’ve been working through some of the Task Factory components. As many of you know I went to work for Pragmatic Works earlier this year. Since then I’ve been learning all our tools, and since there’s no better way to learn than by teaching am creating blog posts about these tools.

Today I thought I’d look at the TF Compression Task. They say a picture is worth a thousand words, not sure what that converts to in term of video but I feel it must be a lot. So without further delay, here is my video showing how to use the TF Compression Task. You can also find it directly on You Tube at http://bit.ly/tfcompression

Task Factory Null Handler

Nulls. Seems like they are the universal headache among all data based projects. Trying to clean up nulls in SSIS is certainly doable, but not without some work with expressions. An equal headache to null values are blank values. In most cases, you’ll either want these converted to a null, or some specific value. Because null handling is such a common task Task Factory has a transformation specifically designed for working with nulls, the Task Factory Null Handler.

There are three options for handling nulls / blanks within the Null Handler.

  • Convert Null to user defined value
  • Convert blank value to null
  • Convert blank value to a user defined value

Let’s take a look and see just how it works. First, we’ll need some sample data to play with. Here’s a small script I created to do just that.

image

It’s only a few rows, enough to test the various modes Null Transform supports. In the first row, we’ll be converting a null date to a defined value, specifically the date 9999-12-31. In the second row, we’ll convert a null text field to a user defined value, "Was Null”. In the third row, we’ll convert blank text to user defined text. In the final row, we’ll convert a blank to a null.

Now that we have our data in place, it’s time to setup the SSIS package. Create a new package and drop a data flow task on it. Start with a standard OLEDB source, and hook it to the database and NullTestSource table.

Next, drag the Task Factory Null Handler on the display surface, and hook it to the source. When you open it, you’ll see a list of the input columns, to the right they will all be set to ‘No Action”. Expand the drop down for the action beside the SomeDate column. Change the action to “Convert NULL value to user defined value”.

Now click the drop down beside the value. In the Parameter Type drop down are three options, Direct Input, Column, and Variable. These are the various choices you have for providing a replacement value. For this exercise, we’ll just use the Direct Input. Change the replacement value to 12/31/9999.

 

image

Repeat the steps for SomeText1, only for it’s replacement value use “Was Null”. For SomeText2, select “Convert blank to null” for the action. No other configuration is required for this action. Just leave the SomeAction column set to “No Action”. It’s merely present to tell us what action the row is performing.

Time to test. Place a Task Factory Terminator Destination below the Null Handler. Place a data viewer inline with it and run the package. Your results should be:

SNAGHTML6ac505

Well, this looks pretty good, but it’s not quite right. For the SomeText1 column, we wanted to check for two conditions, first if it’s null we wanted it converted to a value, second if it was blank we wanted it converted to a different value. In the first null transform we handled the conversion of null to a specific value, but now how to also check for a blank in the column and replace it? No problem, all we need to do is insert a second Null Transform between the first one and the Terminator destination.

Inside it, beside the SomeText1, select the action “Convert blank value to a user defined value”. For the user defined value, enter “Was Blank”. Now hook it to the destination, and place a data viewer on it. Now we should see the correct results.

SNAGHTML75abcb

There we go. The Null Transform makes dealing with blank and null values very easy and straightforward.

Task Factory Terminator Destination

I’ve been exploring some of the many controls included with Task Factory since I came to work for Pragmatic Works. One that I think is useful but underappreciated is the simple Terminator Destination. This is an extremely handy tool to use during development. Let’s take a look at a common scenario.

terminator1base

As you can see from the above screen shot, we’re in the process of developing a package. We’ve worked out the logic in the left branch, and are now working on the right. We have a derived column transformation called “Do some more complicated stuff”. It’s got some complex logic, and we’d like to look at the output before we write it to our target destination. That means having a destination after the derived column transform so we can put in a data viewer. So what are our options?

One of the most traditional is the row count, as seen below.

terminator2rowcount

While this seems pretty simple, it required a few setup steps. First, we had to create a temporary variable to hold the results of the row count. Then we had to open up the row count and assign the variable to the row count transformation. Sure, this seems simple, and I suppose it is. But it does take a few minutes to setup, and I can’t tell you how many times I’ve opened up a package to find variables that aren’t being used for anything. Most times these were temporary row count variables that never got removed. That meant a lot of time tracking down what those were used for, and extra testing to ensure there removal doesn’t cause issues. Suddenly those few minutes add up.

An alternate solution might be the script task. With the script task, there’s no variables to be created.

terminator3script

It looks pretty straightforward, but before you can use the script task you have to open the task, then create a script, then save it, and finally close the component. Again not a huge time investment, but very annoying to have to go through all those steps just to have a temporary destination.

A better, and simpler alternative is the Terminator destination that comes with Task Factory.

terminator4terminator

To use the Terminator, you simply drop it on the design surface, then hook it to the derived column transform above it. Add the data viewer and your done. No variables, no configuring anything, you don’t even have to open the control to edit anything on it.

I’m surprised at just how nice development has been using this tool. Dropping the destination on the package is a “thought free” task. I don’t get distracted, having to stop and think to create a variable, or remember to open and edit a script. Since I don’t have to think about it, it means my concentration remains where it needs to be, on the calculations I’ve created in the derived column transform.

Start using this little transform during your development, I think you’ll find it to be one of the most pleasant transforms you’ll ever use.

Disclaimer: Although I said it in the initial paragraph, just to be clear I do work as a consultant for Pragmatic Works, and Task Factory is one of their products.

Follow

Get every new post delivered to your Inbox.

Join 93 other followers