Solved

Convert  .csv in SSIS

Posted on 2014-10-29
7
181 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:thayduck
  • 3
  • 3
7 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 40413163
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
 

Author Comment

by:thayduck
ID: 40413179
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40413246
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:thayduck
ID: 40413263
This job will be executed on remote servers. Don't believe I can have NotePad++ loaded on them.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40413279
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40415838
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
 

Author Closing Comment

by:thayduck
ID: 40440755
This helped, thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question