Mixing BIML with .Net code

Case
Recently I had to stage about 150 tables from a source database. I like creating SSIS packages, but not 150 times the same boring stage package. Is there an alternative?

Simplified version of my staging package (times 150)

Solution
You can use BIML to create an SSIS package and when you combine that with some .Net code, you can easily repeat that for all you tables. For this example I want to copy the data from all database tables on my source server to my staging server. The tables are already created on my staging server and they have the exact same definition as my source server.

1) Install BIDS Helper
First install BIDS Helper which is an add-on for BIDS/SSDT. Then start BIDS/SSDT and create/open an SSIS project. Now you can right click the project and choose Add New Biml File. This will add a .biml file in the Miscellaneous folder.

Add New Biml File

2) BIML Script
This is the basic BIML Script that creates one staging package for the color table. It has a truncate table command in an Execute SQL Task and a Data Flow Task to fill the table. See this for more examples.

    
    
        
        
        
        
    

    
        
        

            
            

                
                
                    Truncate table Color
                

                
                
                    
                    
                        
                            
                        
                    

                    
                        
                        
                            SELECT Code, Name FROM Color
                        

                        
                        
                            
                        
                    
                
            
        
    

Now you can right click the BIML Script and generate the SSIS color staging package. It will automatically appear in the SSIS project.


Right Click and choose Generate SSIS packages

3) Adding .Net code
By adding some .Net code to your BIML code, you can create a more dynamic script. For this example I will use C# code, but you can translate it to VB.Net if you prefer that language. You can add .Net code between <# and #>, but note that adding that to BIML code could mess up the formatting within Visual Studio. It's even worse to show it on a webpage. So see screenshot and then download the code.

Screenshot, because the mixed BIML and C# code isn't readable in HTML























Download Biml Script here.

Now you can right click the BIML Script and generate the SSIS staging packages for all source tables.

4) Master package
Now you need a master package for all the new staging packages. You can use a Foreach Loop in your master package to loop through all child packages. Or you can use BIML to create the master package:

Master package example 1: loop through SSISDB

Download Biml Script here

Master package 2: loop through project folder on filesystem

Download Biml Script here

Also see: An introduction to BIML