How to import long strings at different column positions with SSIS? Our provider stores our survey data in the following format


Columnnames for each survey


Example:

|v1         |v2|v3|t1
|CompanyName|v1|v2|v3|t1|    



Data for each person


One dataset for survey1

|1        |2|1|I work in the metal industry 


One dataset for survey2  

|MyCompany|4|2|4|I work as a programmer


For each survey I get an identifier to consume the rest api and get the data in json. That is the way I know which data belong to which columninformation.

RestRequest("/datasources/{identifier}/columns", Method.GET)
RestRequest("/datasources/{identifier}/data", Method.GET)


I consume this with SSIS and my problem is that there are up to 400 columns and there are columns like t1 that can be at any position and hold up to 5000 character strings (t1 is an open question). Within SSIS I could now define each column as string with 5000 in length and set all columns in my stagging table to varchar(max) but this would have huge consequences to the performence. That is why I would like to ask if there is another way to handle this problem.
I would not assume it will have huge consequences to performance. Handling this is what Varchar(Max) is for and it's very unlikely to have any impact.

Regarding your specific question about alternatives however, the only one is to use sparse column sets. This allows you to pack a lot of potential but usually null columns into one actual XML column. Then they only get stored inside if they are actually used. They can be selected from directly using their column name, though if you Select * then you only get the raw XML column.

Microsoft documentation is here: https://msdn.microsoft.com/en-AU/library/cc280521.aspx