SSIS basics, reading a file that's both tab delimited and character delimited

I'm trying to read a file that's output from Sybase, which is both tab delimited and with a delimiter, here:
revenue-sybase-output.png
I was able to fiddle with the connection ("Ragged Right"), then I tagged the columns myself, here:
Sybase-columns.png
I was able to read this file and write it as-is to another output file, so far so good.

But
1. is "Ragged Right" and my own tagging of the fields the right way to set up this connection ? How come it can't "see" the tabs or the char delimiter ? I tried to set it up as both delimited with " ~ " and then fixed width, but that didn't work.

2. for my output file, I had to copy the input file and go through the same process of "Ragged Right", then I tagged the fields. But it's confusing to me, why do I have to set up my output file ahead of time ? or do I ?
LVL 1
Alaska CowboyAsked:
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 HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>which is both tab delimited and with a delimiter, here:
Ewwwww.   Any chance you can contact the source of this file and tell them to end it with a single delimeter value?

SSIS can only handle a single delimeter value, not more than one.   So, you may be forced to import this file into a table with a single column, then use T-SQL to correctly parse it given multiple delimeter characters into your final target table.

Good luck.
0
Alaska CowboyAuthor Commented:
Jim,

ok thanks. I was able to fiddle with it and set the columns per my second screen shot.

But I'm curious on my 2nd question, when setting up my destination file, it has to exist ? I was kind of thinking it would just be created as is.

I'm new to the nuances of an ETL tool (I've been doing ETL in Oracle stored procedures). It just seems odd to me that the destination file has to exist, if I understand this correctly. Which means, when moving to Prod, I have to move a dummy output file there (?)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But I'm curious on my 2nd question, when setting up my destination file, it has to exist ?
Nope nope nope nope nope.  

Set the DelayValidation property of the connection to True.
Set the ValidateExternalMetadata property in the data flow task, destination to False.

There is conflict on this as Microsoft once declared doing the opposite as a 'best practice', but that does not account for files whose names are dynamic and will require SSIS coding to handle.

If anyone gives you cr*p on this, send them my way.
0

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alaska CowboyAuthor Commented:
Also, I'm exporting the data from Sybase, and that's how it comes out, using the function "bcpout_tilde". I tried to spool the file by just writing a query and do comma separated, but I don't know how to do that, and a quick google search didn't work.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can't help you on the Sybase aspects.  I recommend either we add the Sybase zone to this question, or asking a new question in the Sybase zone on it.
0
Alaska CowboyAuthor Commented:
>>>>But I'm curious on my 2nd question, when setting up my destination file, it has to exist ?
>>Nope nope nope nope nope.  
- ok, that's good to know, I'll set "delay validation" and "ValidateExternalMetadata " on Fri.

I'll create a Sybase question for that.

Thanks !
0
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 Development

From novice to tech pro — start learning today.