Solved

Convert  .csv in SSIS

Posted on 2014-10-29
7
182 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint Destination SSIS 2 37
asp.net figure out error 3 20
SSIS GUID Variable 2 30
Nested forach loop to linq 3 24
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 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