How to import caret delimited '^' text file using SSIS

Question

How to import caret delimited '^' text file using SSIS

rembersu-432095

Ten Centuries

Points: 1300
More actions
December 22, 2008 at 10:37 pm

#126676

Hi All,
I was trying to create a ssis package to import caret delimited text file the flat file connection manager does not give me the option to select the delimiter Caret '^'. i am able to accomplish this with the bulk insert
with the below syntax
BULK INSERT test.dbo.testload
FROM 'c:\test.csv'
WITH
(
FIELDTERMINATOR ='^',
ROWTERMINATOR = '',
FIRE_TRIGGERS
)
please advise how can i load using SSIS package.
Thanks in Advance.
Rembersu

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

When you create a connection to the source file, using the Flat File Connection Manager Editor', select 'Advanced' - you can type in a caret directly as the column delimiter.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rembersu-432095 Ten Centuries Points: 1300 More actions · Answer 2

Hi Phil ,

when i try to create flat file connection i see only one column in the Advance tab in the flat file connection manager editor. have tried to change the column delimiter to caret but it did not work. please advise.

Thanks & Regards,

Rembersu

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Instead Select the columns link.

you'll See

Row Delimiter {CR}{LF}

Column Delimiter Comma{,} -- Change this to ^

rembersu-432095 Ten Centuries Points: 1300 More actions · Answer 4

Thank you Phil & Ray!

It works now i am to load the text file with caret delimited.

Rembersu