Expressions

When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.

Am I correct? If so... why not?

Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())

I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:

(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)

Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)

Any thoughts?

Hi Jason,

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

As for why the expression language is not .net -- there is a fairly long history there. Note that the existing language is very C-like, and is optimized to work efficiently with DataFlow data types. The dataflow engine itself is native code, not .NET, and the expression language is tightly integrated with the type system.

I hope that helps to answer your questions. Do not hesitate to ask if you have further questions.

Thanks
Mark

|||

Thanks Mark,

Will take up your suggestion and submit feedback.

I am surprised (and a bit disheartened) to learn that the dataflow engine is not dotNET. Microsoft has spent a lot of time and publicity pushing .NET. (Hell, it was even integrated into the SQL engine...)

Authoring packages is done in Visual Studio 2005 which is PRIMARLY a dotnet development environment. (Lets not go down the road of asking why VS itself is not written in dotnet...)

Most of the tasks SEEM to be written in dotNET... (unless they are just PIA wrappers...)

Yet...

You guys (and Microsoft as a whole) does not seem fully utilize your own products... Microsoft Office is still plain old C++ (or C-like), VS is plain old C++ (or C-like), and the SSIS core engine...

(I apologize if this puts you on the spot Mark.)

|||

Not using our own products? Remember we own the best-selling c++ tools too! ;-)

One key reason why our engine is native code is that, through native code, we can write our own code to manage memory. One of the benefits of .Net is precisely that memory is managed for you - which is a real boon until you need to get down and dirty at the system level. .Net has indeed been widely marketed - but not as a replacement for native code in all places.

BTW, the excellent Steve Texeira has an interesting blog on his role as a C++ program manager which includes many insightful posts on such issues, such as this one: http://blogs.msdn.com/texblog/archive/2006/01/04/509428.aspx

I would never regard C++ as "plain old" - and again, Steve Tex has an good outlook on that too: http://blogs.msdn.com/texblog/archive/2006/01/04/509396.aspx

Donald

BTW - our designer, the UI bits, is indeed written in managed code. But the underlying components and tasks of SSIS are mostly native code.

|||

Thanks for responding Donald.

Of course, C++ is not "plain old"... I labelled it that way to emphasize what I perceived as a push for .NET technologies from Microsoft yet a reluctance to fully board the bandwagon...

We still use C++ here in our shop, but for server side process where speed is an issue.

|||

Mark Durley wrote:

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

While we're on the subject of the usability of expressions - a slight digression.

I dislike the fact that sometimes you have to write horribly long, clunky expressions to achieve some things - particularly around concatenation. Jason gives a great example regarding datetimes in this thread.

A really nice feature would be to allow us to build up these long concatenations bit by bit in the same Derived Column component. The way I envisage this happening is by referencing other derived columns in the same component.

I've requested this feature at the feedback center but the site is down at the moment so I can't link to it!!

-Jamie