SSIS: loading vertical-bar delimited files


I am loading pipe (vertical bar) delimeted text files into SQL Server tables.
The actual data shouldn't have | character , however, it happens from time to time.  And the file cannot be loaded unless we manually correct the data.
I would appreciate any ideas on how to improve loading process.

Thank you in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
It will always have to be a manual process to cleanup the data. And it is an RBAR (Row By Agonizing Row) process.

But a possibility would be to load the data into a staging table that is just a an identity column and a large text column. Then have a sproc go through each row and count the number of pipes. If the count is right move it to the final table. Any counts that are off, either hold in the staging table or move to another table for editing.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Yeah, nasty business being *mostly* correct.

Depends really on just how often it happens. If getting an error in your SSIS process and then having to fix manually is an occasional inconvenience, or (hopefully) a rarity, then manual inspection is the best method.

Programmatically it becomes a challenge to recognise a wayward pipe. It will be able to count, but the "fix" is harder to achieve programmatically.

Would have to start relying on things like the nth instance being a possible included pipe rather than a delimiter. And then the size of where it should be included and if contextually that would still make sense. And the number of possible occurrences.

If I was to tackle the problem, and assuming it was just in the one column, then would load into a staging table as jimpen suggests and load the preceding columns and then from the right hand side working back so that the only column left was the one containing the rogue pipe. E.g. process columns 1,2,3,4 then 9,8,7,6 leaving the last one 5.

That doesn't take care of multiple columns having the problem, but would take care of multiple pipes within the one column so long as that one column was always the only one of concern.

Does that make sense ?

It means custom T-SQL to break apart the pipe delimited rows.

There would be a bit of overhead attached, so time might be a concern as well as log activity (no longer a bulk load process) and possibly tempdb.
Rainer JeschorCommented:
just some additional  questions:
- which SQL Server version are you targeting/using?
- Do you have the chance to change the generation of the flat file to implement text qualifiers?

Meaning from this
1|Just a text with an additional | separator inside|2013-10-21
to get that
1|"Just a text with an additional | separator inside"|2013-10-21
(using any other unused character to encapsulate entries which contain the delimiter character)

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Somewhat redundant with the above posts>

I had that happen on a project once where emails were stored in SQL columns, and many email signatures had pipes in them such as My Name | My Title | My Phone number.

We were able to go back to the client and convince them to do a tilde ( ~ ) delimeted file, and problem solved.  Hilarity ensued when they first balked and we asked them which Magic 8-ball we should use to interpret the pipes in signature blocks vs. delimeter pipes.

The additional five grand estimated by me to pull this off was icing on the cake.  Anytime you can pit your own sales executives against clients who treat everything as fixed-bid, it is well worth the effort.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So quasar_ee, how's it going?  Lots of good advice here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.