Convert .csv in SSIS

I have a file called VMPIC.29Oct. This is a new way file is presented to me. Currently, these files looked like NP.29OCT. In fact we are still using current NP.29OCT files. Will slowly change over to new format.

If you looked at attached files you will see that VMPIC.29Oct has all records strung together whereas NP.29Oct has the records showing as 1 line 1 record with embedded commas.

Both files have comma separated values.

In my SSIS package, I need a way to get VMPIC.29Oct to have its records (with commas still embedded in) converted so they look the same way as NP.29OCT, 1 line 1 record with commas.

I would need to do this right after the File Sytem Task 'Copy File' but staying in same SSIS container.
This would have to be done before it goes to next container.

This is what VMPIC.29OCT looks like before I called it .txt so I could attach. It seems when I attached it it does not look the same.
SSIS.PNG
File.PNG
NP.29Oct.PNG
VMPIC.29Oct.PNG
thayduckProgrammer AnalystAsked:
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.

ZberteocCommented:
It seems that the new file doesn't have the CrLf characters at the end of the row or they only have the Lf. This could happen with files coming from UNIX/Linux environment.

I suggest you to download and install the Notepadd++ , which is an excellent FREE product:

http://notepad-plus-plus.org/

and then open the files in it and click on the PI (like in the Greek letter) icon. That will show what invisible characters are in use in the file at the end of the row.

If that is the case you could setup the SSIS to use only Lf as row delimiter or talk to the part that produces the files.
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
thayduckProgrammer AnalystAuthor Commented:
Is there a way to parse out this file in SSIS and create a new file ?
The records start with VMPIC. All data from VMPIC until the next VMPIC would be 1 record.
Then I could pass this on into existing SSIS.
0
ZberteocCommented:
What you can do is to open the file first in Notepad++ I told you about above and then do a search/replace(Ctrl+H) in the file for VMPIC with \r\nVMPIC. In the Replace window you will have to make sure that at the bottom left you choose Regular expression option.

After replace you save the file but make sure you first remove the top empty line at the top that will be created.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

thayduckProgrammer AnalystAuthor Commented:
This job will be executed on remote servers. Don't believe I can have NotePad++ loaded on them.
0
ZberteocCommented:
You can do it locally and then move it back. If not, or if that needs to be an automated process you will have to talk to the people who provide that file and let them know is unusable,
0
Christopher GordonSenior Developer AnalystCommented:
You may be able to parse this file in a DataFlow task using the "Script Component" to create a custom source.  Going this route, you'll be using C# to parse the file using that "VMPIC" as an indicator to end the record and then overriding the CreateNewOutputRows() method in that "Script Component".

This is some work to say the least, especially if you don't have a ton of experience parsing files with C#.

In short, it's not impossible.  From an ROI perspective though, it would be better if you could get a file with row delimeters.
0
thayduckProgrammer AnalystAuthor Commented:
This helped, 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 SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.