Solved

SSIS: Working with Complex Flat File

Posted on 2009-07-08
5
788 Views
Last Modified: 2013-11-10
I am very new in working with SSIS.  I would like to create a package that would take a series of flat files and convert some of the information into a new delimited flat file.  Flat file to flat file; I don't need to import into or extract out of a database.

Below is a small sample of what the source file looks like.  As you can see, this file is broken down into sections; there are header records (noted by '[' and ']'), and detail rows underneath the header record.  However, some header records contain data that corresponds with the header record itself.  For example, the 'ECONV' header record contains a 'GLLoadEnt' record with detail following it.  The rows beneath this header record correspond with the 'GLLoadEnt' record.  Theoretically, there could be multiple 'ECONV' header records, and the rows following each header record all correspond with the preceding header.

Columns are delimited using the '!' character.

I have written VB.NET code that takes this flat file and can import the information into a database, but I am researching the possibility of using SSIS.  I would appreciate any advice as to my approach.  If this is going to be too complex for SSIS, I would want to know that.

Any and all help would be greatly appreciated!
[ORGANIZATION]

Geographic          !Geographic Consolidation                !MAXIMUM             !Geographical        

Function            !Function Consolidation                  !MAXIMUM             !Function            

LC                  !Local Currency                          !MAXIMUM             !LCTop               
 
 

[ECONV=GLLoadEnt           !GL Load for Entities                    !MAXIMUM             !L]
 

100                                                           !EastSales.Input                          

110                                                           !EastProduction.Input                     

120                                                           !EastAdmin.Input                          

200                                                           !WestSales.Input                          

210                                                           !WestProduction.Input                     

220                                                           !WestAdmin.Input                          
 
 

[ENTITYLIST=AllEnt              !All Entitities w/o Subs                 !MAXIMUM             !D]
 

ORG=<ALL>

ENTITY=<ALL>

ALLDEP=Y

IMMDEP=Y

PARENT=Y

BASE=Y

JOURNAL=N

ELIM=N

INTERCO=N

DUPLICATE=N

CODE=<ALL>

CURRENCY=<ALL>

SUBSTRUCTURE=<NONE>

SUBENTITY=<ALL>

CHART=<ALL>

CONSOL=<ALL>

TRANS=<ALL>

Open in new window

0
Comment
Question by:Donovan Moore
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24810627
>>If this is going to be too complex for SSIS, I would want to know that.<<
Yes it is.  You would have to virtually duplicate the code you have in your VB.NET code and there would be no advantage to that.
0
 
LVL 14

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24820149
You'd need to have a data flow which pulled the data out in a long string per row, and then have a Script Component containing your transformation like what you have in VB.Net now.

Although - this could be okay, because you'd end up not needing your VB.Net. Because you'd use the Script component, most of your logic would just go straight across, only changing the way that you output the rows from the component (ie, SomeOutputBuffer.AddRow() instead of pushing it into a database). You can have a large number of different outputs too, which may suit your needs if required.

There are few things you would want to consider... such as telling the data that it's sorted (even though it's not). That will stop SSIS from using parallelism. You should also make sure that the Output of your Script component isn't sync'd to the Input, so that you can control the Output from your component better.

So I would consider SSIS a nicer option than using VB.Net to populate a database before just pulling it straight out.

Rob
0
 

Author Closing Comment

by:Donovan Moore
ID: 31601333
Thanks for the advice.  I think I might go in another direction with this...I'm going to open a new question that outlines a new approach, and I will need honest feedback.
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24854929
Sure. When you do, can you post the question link in here so that I find it?

Rob
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24855049
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now