I'm trying to set up a task to import data from a single text file, record by record, into either of two possible SQL Server tables.
The text file contains two types of records whose layouts differ signficantly -- each record type is identified by the value located at a specific position in the text. If the value at position 1 is 'A', for example, then the record is of type 'A' and I need to map its columns to table 'A' and insert to table 'A'; if the value at position 1 is 'B', then the record is of type 'B', and I need to map a different column layout to the structure for table 'B' and insert to table 'B'.
Is there an efficient way to meet these requirements using a Data Driven Query task? If so, how would I go about it? Or would I be better off writing a separate application to split the single text file into two and perform two separate imports?
Any suggestions will be greatly appreciated....