Custom SSIS Component: Multiple Expressions Task

To change the value of a variable during runtime you need an expression or a Script Task. The expression isn't very flexible and the Script Task requires .Net knowledge. That's why Microsoft introduced the Expression Task in SSIS 2012. It enables you to set the value of one variable during runtime. Downside is that it isn't available for 2008 and you can only set one variable at a time.

Multiple Expressions Task

We, me and my colleague Marc Potters, have created a new cool task to set the values of multiple variables in a single task. You can a either use a value or the Expression Builder.

Enter a value or use an expression



Or use the SSIS Expression Builder

You can also create variables within the task via a context menu.

Create variables

Other features of version 0.1:
- Clearly shows faulty expressions, but you can close the editor without losing it.
- Create new rows to set the value of existing variables.
- Delete rows via the context menu
- Indicator to show that an expression is used


Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup. Restarting SQL Server Data Tools is not necessary.

How add the task the the toolbox (2008 only)
When you open a SSIS project in Visual Studio/Bids, right click the toolbox and choose "Choose Items...". After this Visual Studio will be busy for a couple of seconds!

Right click toolbox

When the Choose Toolbox Items window appears, go to the SSIS Control Flow Items and search for the newly installed Multiple Expressions Task and select it. Click ok to finish.

Choose Toolbox Items




















Now the new task will appear in the toolbox. Ready to use! Have fun.

New task added



Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom task.

BIML: An error occurred while parsing EntityName

Case
I'm creating an SSIS package with BIML and I want to add an expression in my Derived Column with an Logical AND &&.

    
        ISNULL([Column1]) && ISNULL([Column2])
    

But when I check the BIML Script for errors with the 'Check Biml for Errors'-option in the context menu, I got an error: An error occurred while parsing EntityName

An error occurred while parsing EntityName

When I replace it with an Logical Or || it works without errors. What's wrong?

Solution
An XML document doesn't like an ampersand (&). You have to replace it by & or enclose it with CDATA.

    
        ISNULL([Column1]) && ISNULL([Column2])
    



    
        ISNULL([Column1]) <![CDATA[&&]]> ISNULL([Column2])
    

Now you can build the Biml Script without errors.

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