CODEWITHSUNDEEP
parsingssisflat-filestrip
Even Mien
Even Mien

Reputation: 45868

SSIS: Strip Characters from Flat File

What's the best way to strip out characters from flat files in SSIS? In my case, I need to remove all quotes from the file before processing.

EDIT:
How can I run an executable against some files from SSIS? Can I somehow use the source connection as an input or would I have to pass in the file names as parameters?

Upvotes: 1

Views: 15869

Answers (6)

Darryl Paul
Darryl Paul

Reputation: 1

These are solutions, but I believe there is easier funcitonality built right into the Flat File Connection Manager.

On the ver first tab, General, you will find the first text box under the Format sectin, called Text Qualifier. Enter one Double Quotation (or a single quote if all columns are wrapped in single quotes) here and click on preview.

This should solve your issue!

Upvotes: 0

river0
river0

Reputation: 494

Since it's something that you have to do for all the fields on your files, I'd recommend doing it as a first step of the process and not as an operation in the transformation workflow.

You can code your own .NET script and embed it on a Script task. You can also call a third party tool or component via an Execute Process Task.

For instance if you have access to cygwin unix command-line, something like this should do the work:

sed s/\"//g data1.txt

You can call an executable via the mentioned "Execute process task" component, and you can parametrize its inputs by setting expressions on the component's attributes. Those expressions can be based on input variables that might be configured via configuration files. (this is just one of the many ways that SSIS provides to achieve something like this)

Upvotes: 1

deeps_rule
deeps_rule

Reputation: 339

I did using Derived Column Transformation

e.g. If I want to Replace ', " " , # in ssis using derived column, I would write 

Replace(Replace(REPLACE(name,"$"," "), "'", " ")," ","")

But I feel that, if 1 or 2 columns needs to be filtered out, then this approach is good. If it involves more, then go ahead with Script task

Upvotes: 1

ajdams
ajdams

Reputation: 2314

Both unclepaul84 and Ryan Fonnetts' solutions would work but personally I go more towards unclepaul's because I found that I know have multiple files that need quotes stripped out and I can use the same transformation code for every one (which is nice).

Upvotes: 0

unclepaul84
unclepaul84

Reputation: 1404

The easiest way to do this would be to create a "Transformation" script component and use code to strip the quotes.

Upvotes: 2

rfonn
rfonn

Reputation: 2221

If I understand your question correctly, you would like to remove any quotes from any of the column values that are in your text file? If this is the case, you would use a derived column transformation. You would select Replace "column_name" in the Derived Column Name drop down. You would then populate the expression property with the following code: REPLACE( "\"", [column_name] , "")

Hope this helps.

Upvotes: 0

Related Questions