question

@artistlover I am not sure why SSIS is needed, but let's assume it is. In this case, you can load the data into staging table with 2 columns, just like in the spreadsheet. Once the data is in, you can pivot it and insert it into destination table. The only problem is worrisome here is this: if Excel file has only 2 columns then how do you know which row in Excel belongs to which destination row in the table. For example, suppose you have 8 Excel rows: date 2-20-17 vennum 45 name somename add 7777 smith dr date 2-21-17 vennum 46 name somename2 add 7778 smith dr The data above should end up as 2 rows in the destination table. The problem is that there is no way of knowing that, say, row with vennum = 45 has name = somename, ***not*** name = somename2, etc. It cannot possibly be based on the silly assumption that this happens because the Excel row with name = somename is closer to the row with vennum = 45. There has to be some way to identify the islands of rows in Excel. For example, there could be another column with value = 1 in first 4 rows and value = 2 in the next 4 in Excel.

@artistlover Do you have 2 columns in every Excel File, one for name and one for value or you have just one column where both name and value are bunched up together? In any case it is not difficult to import the data to SQL Server. Please provide some details. Thank you.

also, are the fields constant, or do you get different fields with different files? and does each excel file contain just one set of data, or does it contain multiple sets of records?

They do have different columns. Yes one field with labels and second field with data. It appears i have three different types 1. x y z 2. x y w 3 x z' t