Monday 1 July 2013

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

5 comments:

  1. Hello,

    I've got a few questions:

    1. In your second part, is it possible that you forgot to close the input (line 28)?
    2. When I fixed it, I'm getting a warning regarding to the first line:
    Someting like the global element 'http://schemas.varigence.com/biml.xsd:Biml' wasn't declared.

    When I'm using the BIML.xsd in the zip file which is posted here http://bidshelper.codeplex.com/wikipage?title=Manually%20Configuring%20Biml%20Package%20Generator
    I'm getting more than 100 warnings e.g. that the global element 'http://schemas.varigence.com/biml.xsd:Biml' has been declared.

    Generating the package is not possible with these warnings. Do you have an idea how I can fix my problem?

    Regards, Chris

    ReplyDelete
    Replies
    1. It is there, but the javascript viewer messed up the output:
      <input outputpathname="SQL - Truncate Color.Output" />

      Will try to fix it or make a download available.

      Which version of BIDSHELPER are you using and for which SSIS version? I'm using BIDSHELPER 1.6.3.0 and SSIS 2012.

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. You don't need specify <# on each c# line
    You can make blocks like
    <#
    string ..="";
    foreach (file in files)
    { #>
    Cleans up the code

    ReplyDelete
    Replies
    1. Thanks. You're correct. Will change that soon.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...