Sunday, September 1, 2013

SSIS trim leading zero from a string


Today I was checking a question on one of the forums about trimming a leading zero from a string, and the string has characters, also has middle and ending zeros, so the methods that comes to your mind quickly won't work !!  If you're thinking about converting to INT then to a string again !

so I thought about a script component that uses the .NET (String.TrimStart Method) you can read about it more here : http://msdn.microsoft.com/en-us/library/system.string.trimstart.aspx

here's the input scenarios that I came with and the output results....



Steps:

1-I created a flat file (TXT) with punch of rows to test
2-Only one output from my flat file for simplicity, named Col0

3-script component (transformation Type), using Col0 as input.



4-created an output column Col0Cleaned
 



5-added the above highlighted line... and that's' it!

 

Please let me know if you have any scenarios that this solutions couldn't fix!
package and solution can be downloaded from here....


Hope that helps ......

 

SSRS Strip Domain from User!UserID

In SSRS, if you want to be fancy and display the USER NAME in the header or footer of your reports, like what I do.  SSRS uses Domain\UserName format... So someone this morning was asking in the forums about how to strip the domain out.

So I provided two solutions...


="Welcome " & MID(User!UserID,InStr(User!UserID,"\")+1, Len(User!UserID))

="Welcome " & =right(User!UserID, InStr(StrReverse(User!UserID),"\")-1)

Hope that helps someone...