SSIS Expression Connect Items

2014-04-02
Sometimes you deal with a problem for so long that you forget how difficult or counter-intuitive the solution is.  This is the case for two items that I added to connect, today.

First, is a need for a format function in the SSIS expression language for datetime.  How often do we write some crazy thing like this:

(DT_WSTR, 4)DatePart("Year",@[User::Variable])
+ RIGHT("0" + (DT_WSTR, 2)Datepart("Month",@[User::Variable]),2)
+ RIGHT("0" + (DT_WSTR, 2)Datepart("Day", @[User::Variable]),2)

If all we needed was an expression to stick the date and time into a file name, this is super verbose and unreadable way to go about it.  Frankly, it's kind of embarrassing when you stick it next to .NET which has a method to provide an easy and elegant solution.

The second, is for the trim function to trim all unreadable characters, not just char(10).  I frequently see the results of copy and paste doing kooky things in an application.  Tabs, carriage returns, line feeds and non-breaking spaces make their way into our data.  This raises all sorts of problems downstream.  So I would like the trim function to do what the .NET function does - get rid of every invisible character.  Heck, you can destroy wingdings if you like too, but I just REALLY want trim to trim.

Please votes for these items:
https://connect.microsoft.com/SQLServer/feedback/details/845538/format-function-for-datetime
https://connect.microsoft.com/SQLServer/feedback/details/845548/improve-trim-function-in-ssis

Thanks
Mark

Labels: ,