Checksum Transformation in BIML

Case
Last year we (colleague Marc Potters and me) created a custom Checksum Transformation for SSIS. We use it a lot to compare records from two sources. Instead of comparing a whole bunch of columns in a very large (unreadable and unmaintainable) expression we just compare the hash of both records.

Someone asked me if it was possible to add this custom transformation via BIML. The documentation and examples for custom transformations in BIML are a little limited and of course different for each one.

Solution
Make sure you install the Checksum Transformation. This BIML script uses version 1.3 of the Checksum Transformation in SSIS 2012 and BIDS Helper version 1.6.4. If you use an other version of Checksum or SSIS, then the ComponentClassId, ComponentTypeName and TypeConverter properties will probably have a different GUID or PublicKeyToken. By creating a package manually and viewing the source code you can find the correct values.

 
  
  
 
 
  
   
    
     
      
       SELECT AddressLine1, AddressLine2, City FROM Address
      
      
      
       
        
        
        
        0
        Salt123
        
        |
       
       
        
        
         
          
          
          
         
        
       
       
        
         
          
          
          
         
        
       
      
      
     
    
   
  
 

Some browsers don't show capitals in the xml above, but you can download the BIML Script here.


Note 1: If you want to use a variable for the Salt, then you need to know the GUID of the variable. Create a variable in BIML, but with a GUID and use this GUID as Salt_Variable. See step 3 of this blog post.
Note 2: Don't change the names of the InputPath and OutputPath. The transformation is expecting these names.

The process cannot acces the file 'ssisproject.ispac' because it is being used by another process.

Case
I want to run an SSIS 2012 package but I'm getting an error:

ispac file in use by other process

System.IO.IOException: The process cannot access the file 'c:\folder\ssisproject.ispac' because it is being used by another process.
     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
     at System.IO.File.Delete(String path)
     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

Solution
You have probably already restarted SSDT, but that didn't help. Open the Task Manager (Ctrl + Shift + Esc) and locate DtsDebugHost.exe under Processes and end that process (could be multiple times, end them all). Now try running the package again.

Task Manager, End SSIS Debug Host

This happens when SSDT/Visual Studio crashes during runtime. If you kill SSDT then the SSIS Debug Host will still be active locking the ISPAC file.