**UPDATE: I don't know if this will help, but after doing some research I think this is what you would call a parent child or master detail flat file. Here's a link to an article that seems to explain exactly what I need to do, but it's way over my head in terms of understanding exactly what to do. http://www.sqlis.com/54.aspx
First, a couple of important bits of information. Until last week, I had never touched SISS, and therefore, I know very little about it. I just never had the need to use it...until now. I was able to convert my first 3 flat files to SQL2005 tables by right clicking on "SISS Package" and choosing "SISS Import and Export Wizard". That is the extent of my knowledge! So please, please, please be patient with me and be as descriptive as possible.
I've attached to files to this question. The first attachment is the flat file specifications and the second one is a sample single line flat file similar to what I'm dealing with (the real flat file is over 2 gigs).
As you can see, each entry could have any number of records and multiples of some of the record types, with one exception, every entry must have a "01" record and can only have one "01" record. Oh, and each record has a length of 400.
I need to get this information into a SQL 2005 database so I can create a front end for accessing the data. Originally, I wanted one line for each account and have null values listed for entries that don't have a specific record. Now that I've looked at the data again, that doesn't look like a good idea. I think a better way to do it would be to create 5 different tables, one for each record type. However, records 2 through 5 don't have anything I can make a primary key. So here are my questions...
1. Is it possible to make 5 tables from this one file, one table for each of the record types?
2. If so, can I copy the Account number in record 01, position 13-19 in each of the subsequent record types (that way I could link the tables as needed)?
3. Can this be done using the SISS Import and Export Wizard to create the package? If not, I'm going to need some very basic step by step instructions on how to create the package.
4. Is SISS the best way to do this conversion or is there another program that would be better to use?
Assuming this can be done, I really need someone to either walk me through the process or give me link to another site that will. I'm even willing to accept a reference to a book I need to purchase with a VERY VERY good description.
I know this is a huge question and I appreciate the help of anyone who boldly decides to help me! Thank you in advance and I welcome anyone's suggestions!