Mark says at http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/b309a7e3-afa3-49a7-9863-22679d146db0/that the ssis Derived Column Transform can replace tabs with empty strings using the Replace function. Does anybody have an example of what/how the tab char gets typed as a param to this function?

Hi,I haven't tried this butSQL Server recognises \t as a tab sequence so I wonder if you could use it.CheersJeffSQL Server MVP WARDY IT Solutions, Solutions Architect

The following solutions work in SQL Server 2008 T-SQL. Can you try it in SSIS? Let us know what happens. DECLARE@Stringvarchar(64)='NewYorkCityis'+char(9)+'onManhattanisland' SELECT@String, REPLACE(@String,CHAR(9),''), REPLACE(@String,0x09,'') GO /*Results NewYorkCityisonManhattanisland NewYorkCityisonManhattanisland NewYorkCityisonManhattanisland */Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

thanks guys, but I'm looking for an expression (or maybe vb script) based solution because this is the kind of thing I like to do in the bufferinstead ofthe db engine.I did see a partial regular expression solution somewhere yesterday, baked into a vb script. It removes hex zero from each row. It might have some promise if extended somewhat, not sure yet. I'll also be looking for what expression represents hex 09, perhaps because thederived column transform could be enlisted to do this for me.

Don't know if this will help, but there are a couple "Regex" transforms freely available:One from Microsoft:RegexTwo from SQLIS.com: RegexCleanand RegularExpressionTransformation

No problem.The original question was in relation to using the Derived Column Transformation Task and how tostrip out tabs.CheersJeffSQL Server MVP / WARDY IT Solutions, Solutions Architect

Can you try the following expression in the Derived Column editor: REPLACE(colOmega,"\x0009","") colOmega is your input column.Let us know if works.Can you also try "\t" ?Thanks. Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

sorry Jeff, when you said SQL Server, I thought you were talking about T-SQL, not the transform component's expression language. I'll try your's and SQLUSA's ideas and post results here. I'll also look at Todd's stuff. The sad thing here is that my flat file has over 130 columns coming in. I'm starting to think/hope that it will be really nice if all columns are in some kind of .net collection that a foreach loop can act on from inside a vb script.

Hello, I'm experiencing the same issue where I cannot get the Derived Column tranformation in SSIS 2008R2 to recognize the Hex(9) or "\t" expressions for a TAB value. The Flat File Destination only recognizes both as literals, i.e. 9 or \t, respectively. And the Derived Column transformation even accepts Hex(9) as a legitimate function but transposes the integer argument (9) to 9 instead of TAB. Has anyone successfully created a TAB value within SSIS transformations? Thanks, Ben

Hi Ben. Should we unmark this as answered? It sounds like you are saying that you tried REPLACE(colOmega,"\x0009","") and it did not work. I cannot recall why I never revisited this post.

This solution worked for me. I was dealing with a vertical tab (New Line Character) that I had to remove: -- CODE -- TRIM(REPLACE(ColumnName,"\r\n"," ")) It's important to note what kind of data you're working with: Vertical tab, Horizontal tab etc. I imported the data as is into SQL, and then using the ASCII T-SQL function determined what character I was dealing with. I then found the following link explaining what the ASCII value was that I found (13), however it didn't tell me how to deal with it in SSIS: http://www.csgnetwork.com/asciiset.html I then found the solution on MSDN but closed the site before I could mark the person's answer as correct. The following link explains literals in SSIS (I found this by Googling it): http://msdn.microsoft.com/en-us/library/ms141001.aspx I hope this helps.