Task Factory E-Mail Source

Of all the components in Task Factory, by far the coolest is the E-Mail Source. Using this nifty component you can actually pull your data into SSIS from an E-Mail! In this video you can see the Pragmatic Works tool in all it’s glory. Then you can see that the Task Factory SSIS E-Mail Source, like bowties, is cool.

Task Factory Surrogate Key Transform

When extracting data, you don’t always a natural key handy. Sometimes the nature of the data negates any built in key, or perhaps there’s just not one present. For these situations the Task Factory SSIS Surrogate Key Transform, part of the Task Factory suite from Pragmatic Works, will meet your needs.

At it’s core it’s very simple, just add it to your data flow, provide a column name, seed value and increment value, and away you go. It’s almost so simple you don’t need a video, but our series wouldn’t be complete without including this little gem.

Task Factory Trim Plus Transform

In previous videos, we’ve looked at some of the other data cleansing operations Pragmatic Works’ Task Factory offers, such as address parsing and case correction. Another common data cleansing task is trimming spaces or characters from the start and end of text columns. In this video, we’ll do some data cleansing with Task Factory’s Trim Plus transform.

Task Factory XML Destination

XML has become the defacto standard for information exchange across the web. The Task Factory SSIS XML Destination, one of the many components found in Pragmatic Works’ Task Factory suite, makes it incredibly easy to create XML files from your SQL Server Integration Services packages. In this video, you will see just how simple it is to generate easy to read XML files.

Task Factory Upsert Destination

Inserting and updating records is, without a doubt, the most common operation performed in any SQL Server Integration Services package. What a headache though, having to determine if the record exists, whether to insert it, stage it, etc.

All that goes away with the Task Factory SSIS Upsert Destination component, part of the Task Factory suite from Pragmatic Works. With one simple control you can handle both inserts and updates with ease. This video shows you how incredibly easy it is.

Task Factory Address Parse Transform

Addresses. They are one of the biggest headaches for any ETL developer. Trying to clean up poorly, inconsistently entered addresses can be a real headache. Fortunately the Address Parse Transform, part of the Pragmatic Works Task Factory SSIS suite, is your aspirin. In this video you will learn just how easy it is to clean up all those bad addresses.

 

Task Factory – Replace Unwanted Characters

One of the most common tasks for SSIS developers is to replace characters or words in a column with a new set of characters, or a new word. Sure, you can use a derived column transform, then fiddle around with substrings, but geez what a pain that is. Fortunately Task Factory, a suite of SSIS components from Pragmatic Works, has a great tool to make this quick and easy: The Replace Unwanted Characters transformation. This video demonstrates how easy it is to use.

Disclaimer, In case it’s not obvious I do work for Pragmatic Works.

Task Factory Advanced E-Mail and SMS Task

I’ve been continuing my learning process of my companies Task Factory suite. Building on my previous work with the Compression task (video) and File Properties task (video) I’ve now created a new video about the Advanced E-Mail and SMS Task. After compressing a file, e-mailing it out seemed like a natural thing to do, so this falls in nicely with the previous videos.

 

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