question

Noonies avatar image
Noonies 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
ssisscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Valentino Vranken avatar image
Valentino Vranken answered
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
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Noonies avatar image Noonies commented ·
@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!
1 Like 1 ·
KenJ avatar image KenJ commented ·
definitely simpler than a script component
0 Likes 0 ·
Valentino Vranken avatar image Valentino Vranken commented ·
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).
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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.
0 Likes 0 ·
Valentino Vranken avatar image Valentino Vranken commented ·
@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
0 Likes 0 ·
Show more comments
KenJ avatar image
KenJ answered
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);
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Noonies avatar image Noonies commented ·
@Ken Johnson - I did not use String.Empty in the script component but used "" instead which was the issue there. Thanks for your response!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.