I am working in SSIS. I have a ragged right, pipe-delimited text file. That is, the number of columns per row is variable. Moreover, each row can be categorized. For rows where column 1 = "A", it should have 5 columns. For rows where column 1 = "B", it should have 3 columns. In addition, the data types for each column is different per record type. For example, column 2 of "A" records is an int, and column 2 of "B" records is a varchar(50).
I am trying to import this text file and land each record type into its appropriate table. For instance, all "A" records must go to the A_table, and all "B" records must go to the B_table.
In my data flow task, I need to take my flat file source to an OLE DB destination. Of course, my flat file source is configured to squish all data into 1 column (because SSIS doesn't allow variable columns in a flat file source). From my investigation of this scenario so far online, most people suggest a script task after the flat file source. However, that won't work for me because, in a script task solution method, I am effectively taking the record type with the maximum number of columns, defining an output for each of these columns, and using this for all records. Because the data types change per record type, this solution won't work for me.
So, my next best guess is to take the flat file source into a conditional split. While I can certainly do so, I don't know how to send my flat file source (which has just 1 column) to the destination tables (which have several columns defined). How do I do this?
