Mapping multiple layouts from a working SQL table - SSIS

Question

I have a flat file as an input that has multiple layouts:

Client# FileType Data
------- --------  --------------------------------------
Client#1FileType0Dataxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
Client#1FileType1Datayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy  
Client#1FileType2Datazzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 
Client#2FileType0Dataxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

My PLANNED workflow goes as follows: Drop Temp table -Load SQL temp table with columns Client#, FileType, Data and then from there, map my 32 file types to actual permanent SQL table.

My question is, is that even doable and how would you proceed? Can you, from such a working table, split to 32 sources? With SQL substrings? I am not sure how I will map my columns from the differing file type from my temp table, what 'box' to use in my workflow.

Answer 1

What you are describing is a very reasonable approach to loading data in a database. The idea is:

• Create a staging table where all the columns are strings.
• Load data into the final table, using SQL manipulations.

The advantage of this approach is that you can debug any data anomalies in the database and that generally makes things much simpler.

The answer to your question is that the following functions are generally very useful in doing this:

• substring()
• try_convert()

This can get more complicated if the "data" is not fixed width. In that case, you would have to use more complex string processing. In that case, recursive CTEs or JSON functionality might help.