asked
Script Component in SSIS to extract % sign
Hi Everyone! I'm trying to upload a flat file that has quotes and percent signs in the data. I have been able to remove the quotes from the data during upload by using the Text Qualifier field and noting the ". But I also have a couple columns which I have the percent sign which I need to extract. I do not see in the Flat File Connection Manager that I can not more that one Text qualifier, so I was adding a script component to cleanse the data prior to upload. I'm using the attached link as my guide: [link text][1] which has been helpful. But I cannot locate the code for using the Row.CleansedData = Row.Name.Replace(*expression entered here*). Does anyone know what the expression should be to remove the % sign from the column using C# or is there another way I should look into extracting this from the column other than running an update on the table after upload? Any information would be greatly appreciated. If I am asking a question that should be in a different forum, please let me know and I will remove and post in a different area. Thank you! [1]:
http://stackoverflow.com/questions/10387550/how-do-i-remove-spaces-while-importing-flat-file-containing-intermediate-blank-l
If all you need to do is remove that percentage sign from your values, you don't really need to implement a Script component. A simple Derived Column Transformation would work fine. With that you'd replace the current value with the result of an expression such as: REPLACE(YourField, "%", "") Ref. [REPLACE (SSIS Expression)][1] [1]:
http://msdn.microsoft.com/en-us/library/ms141196.aspx
@Ken Johnson and Valentino Vranken - Thank you very much for this suggestion. I have not used the Derived Column in the Data Flow Transformations before so this was a new exposure and tool for me to use. This was easier and something I will take note of in the future. Thank you both for your feedback and I have decided to use the Derived Column tool vs. Script Component based on your agreement that this is simpler. This forum has great knowledge and I really appreciate the suggestions and answers that are given. Have a great day and again Thank you so much!
Thanks for your feedback! I usually hook up a Derived Column to the source for the easy conversions. If needed, I connect a Script Transformation to the Derived Column for more complex transformations (such as getting a DateTime out of a part of a filename, for instance).
The Derived Column will allocate additional filed and memory in the data flow buffer and on the other side synchronous Script Component can operate on existing buffer without growing the memory needs. So depending on the amount of data and structure and configuration of the package one or the other can better.
@Pavel Pawlowski - Derived Column is a synchronous, non-blocking transformation just like the Script component. Ref
http://sqlblog.com/blogs/jorg_klein/archive/2008/02/12/ssis-lookup-transformation-is-case-sensitive.aspx In fact, the Script component can be asynchronous, depending on how it is implemented. Ref
http://consultingblogs.emc.com/jamiethomson/archive/2005/07/25/SSIS-Nugget_3A00_-The-difference-between-synchronous-and-asynchronous-script-components.aspx
Replace has two arguments. The first is the string you want to be replaced, the second is the string you want instead of the first argument. If you just want to remove the % character, just replace it with an empty string: Row.CleansedData = Row.Name.Replace("%", String.Empty);
15 People are following this question.
