SSIS: Working with Complex Flat File

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

Donovan MooreConsultantAsked:
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.

Anthony PerkinsCommented:
>>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
rob_farleyCommented:
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

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
Donovan MooreConsultantAuthor Commented:
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
rob_farleyCommented:
Sure. When you do, can you post the question link in here so that I find it?

Rob
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

From novice to tech pro — start learning today.