LEFT Function in SSIS Expressions

There are a number of small changes to Integration Services in SQL Server Denali that might not be immediately apparent. One of these is the new LEFT function in the SSIS Expression language.


This new function provides a simpler way to get the first part of string expression - prior to Denali, you’d use the SUBSTRING with an offset of zero.

Helpful Denali links:

Comments (7)

  1. ArthurZ says:

    If LEFT is on the list of additions, will RIGHT be also added?

    Any more other new SSIS Expression Language additions?

  2. Jamie but not Thomson says:

    Wow, really??? a left function??? that's just what I asked to santa claus this christmas….  Come on after 6 years with the same functionallity all we are getting in the next version is a Left function and a couple of improvements on variables and deployment that we should have since years ago. There's a lot of functionallity requests on the "Microsoft Connect" site that are really more important than this. I know this is not the place to complain but I'm dissapointed, and now that the face of Microsoft's BI Donald farmer is out I'm feeling that I should learn another BI suite. I haven't take a look at Denali yet so I shouldn't be complaining about 😛 but as I have read in blogs like this there won't be any surprising additions…  ohhh wait… there will be an "undo" button yeahh!!!

  3. I'd highly recommend that you download the Denali CTP and give it a try. The first CTP contains a number of new pieces of functionality, as well as productivity enhancements, like the undo/redo support you mentioned.

    LEFT is a small piece of new functionality, and while by no means release defining, actually came in as a Microsoft Connect request. We draw a lot of our improvement ideas from Connect – I blogged about four cases (of many) back in August.


    We're still early in the release cycle, so you can expect to see even more improvements in the CTPs leading up to RTM.


  4. James aka BI Monkey @ http://www.bimonkey.com says:

    Like Jamie i'm similarly unimpressed. The SSIS function library is poor and given it's based on .NET this is pretty inexcusable as that function library is vast.

    Further, the inability to provide user-defined functions (something that's even in Cognos Data Manager 7) is a very annoying hole. Just because you *can* do something in a script component doesn't mean you should have to. I raised a Connect about this which got a good number of votes (14: connect.microsoft.com/…/extend-function-library-and-allow-user-defined-functions-for-ssis-derived-column-transformation).

    I understand the need to add shiny new capability (Projects, SSDQS) but also there is a strong need to improve the underlying product as well, not just make it look pretty.

  5. Hi James – I agree this would be great functionality to have in SSIS. It's actually something that we've looked at on a number of occasions.

    The SSIS Expression engine is actually a native implementation (C++), and not based on .NET. This allows it to perform well with both the SSIS runtime and pipelines, which are also native (C++, COM heavy). Believe me, if it weren't a major undertaking to 1) extend the library, 2) allow user defined expressions and 3) maintain backcompat, we would have done it a while ago.

    Because this sort of extensibility can currently be done with a script component, it lowers the priority of the feature. Yes, it would be nice to have, but we don't have unlimited resources. If we did this feature, we'd have to cut something else. Does it make sense to cut functionality from Denali to provide another way to do something that can already be done?

    I think you'll be happy to see that there are many productivity and usability enhancements in Denali — that was our main focus after the first CTP went out. Once you see what we've done, I'd be more than happy to debate why feature X was added/improved instead of adding user-defined functions.

    If you run into me at a conference, I'd be more than happy to explain the nitty gritty details of the Expression Engine, and tell you all about the tough choices we have to make to get new versions of the product out the door on time.

  6. James aka BI Monkey @ http://www.bimonkey.com says:

    Hi Matt, thanks for the explanation around the expression engine.

    Also I do get the fact that the SSIS development team is not infinite! I have taken a look at CTP1 and do appreciate the features you've added.

    My concern is that there are a few holes in the product that have been present since day 1 which despite universal developer community consensus (e.g. the awful SCD component, the confusing Pivot UI) don't seem to be getting addressed. This is how a product starts showing its age.

    I do have a big love for SSIS and hope there is more goodness to come in the usability stakes

    Cheers, James

Skip to main content