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: Connect, SSIS