SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RE: SSIS or Import


RE: SSIS or Import

Author
Message
lmeinke
lmeinke
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 199
Hello all,

I have to figure out a way to analyze the EDI (electronic data interchange) files concerning ASN (advance ship notices) from our vendors. I'm not really sure where to start. I can identify the lines I need by the first series of letters in each line. Here is a sample:

ISA~00~ ~00~ ~01~929222446 ~01~042653634G ~090923~1407~U~00401~000006502~0~P}
GS~SH~929222446~042653634~20090923~1407~6502~X~004010}
ST~856~65020001}
BSN~00~CS-0171895~20090923~14045500}
DTM~011~20090923}
HL~1~~S}
TD1~CNT~1~~~~G~100~LB}
TD5~~2~PKUP~L~PICK UP}
TD3~TL~~010458}
REF~VR~131456}
N1~SF~XXXXXXXX XXXXXX~92~131456}
N3~2000 SOUTH MAIN BLVD.}
N4~XXXXXXXX~XX~XXXX}
HL~2~1~I}
LIN~0001~BP~0601759~VP~77417}
SN1~~72~EA}
PRF~110051897}
PID~F~~~~AC20 BLU XXXXXXX .8M PLN-PLN 24}
CTT~2}

I have tried desparately to import this into some type of understandable structure with the Import Wizard to no avail. Sorry to pose such a general question but I'm just stuck.

Thank you,
Lonnie
matt stockham
matt stockham
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 3178
I doubt you can do it with the import wizard, maybe with SSIS ... the problem is that each line has different segments so it doesn't map readily to a single table. Then consider that the same 3 character identifier has different meanings based on it's relative position in the file.

What exactly are you attempting to do? Something like http://www.softshare.com/software/edinotepad/ might help you in terms of analysis.
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 1479
Parsing the file in .NET (VB, C#) may be another solution - are there any specs about working with those files?
Animal Magic
Animal Magic
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3642 Visits: 13752
I work with a similar file structure with Banking systems. the way i do it is via SSIS. i import the file into a staging table which stores the whole row in one column (using flat file import - ragged right), hopefully you have a line feed at the end so the import can tell the start of a new line.

Then, knowing what each of the header characters mean on the rows you probably have a data definition document from the vender, so for example you know a row starting with ABC has the item id in chars 4-9. I just do a select statement using substring to dump the data into a table (seperate table for each line type) so that you have the data split into columns.

That way you will end up with multiple tables that hold your import information. At the start of my procedure i get a new importbatchid and add this to each table so that i can link them up.

Hope you can kind of understand this. if you want more info let me know.

John
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 1479
Performance might be better if you parse your file in .NET according to the data definition document.
AmolNaik
AmolNaik
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 1234
Few years back when i was working on SQL Server 2000, i had a client requirement to import EDI files into our database, i had then used ActiveX vbscript wihtin the DTS to parse and load the file.

Using SSIS, you now have 2 options:-
1) Use .NET script to parse and load the contents to a tables.
2) Use Script component within the Dataflow task to do parsing. Google on script component and you'll find plenty of resources.

Compare the performances of both the methods and choose the best one.

All the best

Regards,
Amol

Amol Naik
lmeinke
lmeinke
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 199
Hello All,

I just wanted to thank everyone for their input to this point. I am able to import the data and query out the pieces I needed. I'm going to have to "hold" on the SSIS or .net parsing for now because to this point I've only received a few sample files. I need to get a larger single dump to determine what the input file structure will ultimatley be.

Thank you again....and please stay tuned.

Lonnie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search