Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RE: SSIS or Import Expand / Collapse
Author
Message
Posted Wednesday, September 30, 2009 3:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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
Post #796041
Posted Wednesday, September 30, 2009 10:08 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:15 PM
Points: 750, Visits: 3,157
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.
Post #796125
Posted Thursday, October 1, 2009 1:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
Parsing the file in .NET (VB, C#) may be another solution - are there any specs about working with those files?
Post #796171
Posted Thursday, October 1, 2009 4:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:31 AM
Points: 999, Visits: 13,473
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
Post #796238
Posted Thursday, October 1, 2009 5:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
Performance might be better if you parse your file in .NET according to the data definition document.
Post #796264
Posted Friday, October 2, 2009 11:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
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
Post #797116
Posted Monday, October 5, 2009 2:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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
Post #798187
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse