▼

Saturday, 6 July 2013

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.




2 comments:

  1. Unknown3 June 2014 at 06:18

    It appears, at first look, that your muti-variable component freezes when used in a for each file container. The second iteration stops at the multi-variable component.

    ReplyDelete
  2. Matthew Lehn16 April 2015 at 15:12

    I've had a similar issue using a ForEach loop. The task worked the first time, but on the second time, it didn't work. It throws an error for each expression one at a time (with like 30 sec in between). The error was "The expression for variable '[variable name]' failed evaluation. There was an error in the expression." I know this is definitely not the case as (1) it works the first time, and (2) the package works using the standard Expression Task.

    ReplyDelete

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.