Replace double quotes on SSIS

Question

Replace double quotes on SSIS

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
May 28, 2010 at 9:58 am

#140630

I need your advice. I need ro replace double quotes in SSIS from couple of flat files that we got. I know I can use Derived Column. but these files have more than 150 columns. I think it will be tiring to do it on Derived Column (one by one column/record)
Is anyway to tell SSIS to apply that replace to all the records from the Flat File ???
Regards,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

rwlp.french Old Hand Points: 346 More actions · Answer 1

Did you look at this ? http://www.microsoft.com/downloads/details.aspx?familyid=b51463e9-2907-4b82-a353-e15016486e1d&displaylang=en

Have had a chance to try it yet, might be worth looking at?

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 2

I created a Script in SQL to do it.

I used SSIS to load the FlatFiles into my tables and I run this script.

Declare@TableName varchar(60)

, @min-2 int

, @max-2 int

, @ColumnName varchar(60)

, @SQL VARCHAR(8000)

set @TableName = 'PF_USZ21'

select

--'TableName'=convert(char(50),t.TABLE_NAME)

row_number() over (Order by c.Ordinal_Position) 'Row'

, 'ColumnName' = C.Column_Name

Into#ReplaceColumnData

fromsysindexes i, INFORMATION_SCHEMA.TABLES t

JoinINFORMATION_SCHEMA.columns c on t.Table_Name = c.Table_Name

wheret.TABLE_NAME = object_name(i.id)

andt.TABLE_TYPE = 'BASE TABLE'

andobject_name(i.id) = @TableName

andc.Ordinal_Position between 1 and 10

Order by c.Ordinal_Position

Select@min-2 = Min(Row), @max-2 = Max(Row)

From#ReplaceColumnData

while @min-2 <= @max-2

Begin

select @ColumnName = ColumnName From #ReplaceColumnData where Row = @min-2

SET @SQL = 'Update ' + @TableName + ' Set ' + @ColumnName + '

= Replace(' + @columnName + ', ''"'', '''')'

Exec(@Sql)

set @min-2 = @min-2 + 1

End

drop table #ReplaceColumnData

Matt Taylor's Chain SSC Rookie Points: 34 More actions · Answer 3

Do you have to replace the double-quotes using SSIS? Why not use a text editor (eg TextPad, EmEditor, NotePad++) to replace the double-quotes in the flat files then import them with SSIS without having to handle the double-quotes. If the process needs to be repeatable the text editors mentioned above have macro capabilities, you could also call these via a command line.

Sunil Kanta SSC-Addicted Points: 475 More actions · Answer 4

Hey, Do you have incoming records in the following format,

column1,column2,column3,........columnN

"abc1","xyz1",10001,...........,"pqr1"

"abc2","xyz2",10002,...........,"pqr2"

.....

Then no worries, in your Flat file connection manager you have something called Qualifier, make use of it. In place of <none> enter " and see if it works. I'm sure this will work as it did for me. As simple as that!

-SMK

Regards,
SMK

--------------------------------------------------------------------------

"If you’re not prepared to be wrong, you’ll never come up with anything original."

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 5

omg! that it was pretty simple. I really couldnt find it before 🙂

Thank you so much! it really save me a lot of time

😀

Sunil Kanta SSC-Addicted Points: 475 More actions · Answer 6

Hey, I'm glad that worked for you too......!!;-)

Thats the simplicity of SSIS to handle the complexity of data !!!!!!:hehe:

Cheers!

Sunil

Regards,
SMK

--------------------------------------------------------------------------

"If you’re not prepared to be wrong, you’ll never come up with anything original."

vs7804 SSC Rookie Points: 33 More actions · Answer 7

I tried using the Qualifier which you mentioned in your previous post (i.e) ". I have a column in which only few names have double quotes it din't work for me any idea ?

samprathi.soumya SSC Enthusiast Points: 110 More actions · Answer 8

we can also use substring function under derived column in ssis package, i have used this it was successful.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

vs7804, there was a suggestion earlier to just find/replace in the file before importing, that's one option, or the derived column as suggested just above. Lastly, you could just import the double quotes into the table, then clean it up there, something like:

UPDATE dbo.myTable SET myColumn = REPLACE(myColumn,'"','') WHERE --put your condition here if you don't want to apply to the whole table

That's singleQuote +doubleQuote+ singleQuote in the REPLACE function, hard to see that, hopefully that's apparent.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Sunil Kanta SSC-Addicted Points: 475 More actions · Answer 10

Hey,

Sorry for the late reply.

As others have posted,

1. Using a derived column component will help (posted by Soumya), wherein you have to replace your double quote qualifiers (") for each column going down stream. This will be a row by operation.

2. You can always use a update query (by jcrawf02), after the data is loaded.This will be a bulk update.

Choice is upto you now.

Regards,
SMK

--------------------------------------------------------------------------

"If you’re not prepared to be wrong, you’ll never come up with anything original."