We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SSIS: Working with Complex Flat File

Medium Priority
896 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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Donovan MooreConsultant

Author

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.
Rob FarleyConsultant
CERTIFIED EXPERT

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

Rob
CERTIFIED EXPERT
Top Expert 2012

Commented:
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.