OK, understood. Unfortunately, I do not have time today to help any further. If no one else answers in the meantime, I will try to get back to you in...

Are there embedded carriage returns in that file?

Have you set a text qualifier? (")

As packages usually run within the context of an unattended process, message boxes within them should be avoided (unless developing or debugging).

I notice you mentioned users running this. For them...

Also, how is the value of ColumnType determined?

OK, that helps.

So you are looking for a routine which imports only the first row of a spreadsheet/CSV file, transposes that row of data into separate rows and adds them...

• This reply was modified 4 years, 7 months ago by Phil Parkin.

How are you displaying the popup? You need to display it from Excel, not from within the package.

The columns in the spreadsheet do not appear to correspond to the columns in your screenshot, making this a bit of a puzzle.

Why not just hit 'TRANSPOSE' in the spreadsheet,...

Please give an example of what you...

I just played around with this scenario for an hour and I was unable to come up with a solution which works in all cases. Very irritating.

Automating an export to...

Apologize Phil.

I posted in the other post first but realized it is not the correct place for it as that is discussion not relate to Coding perspective. so I...

As you are looking for a SQL Server solution, rather than SSIS, Jeff's solution is worth...

I appreciate the response, but unfortunately, this didn't work.  The first file, with the "dummy" data loaded great.  I then changed the setting on the Excel Source step to...

It's been a while since I last worked on this issue, so I may have misremembered the 'solution'.

After your package has run against the dummy Excel file with the wide...

I haven't fully tested this, but it should get you started:

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    entity VARCHAR(50)
   ,col VARCHAR(50)
 ...

• This reply was modified 4 years, 7 months ago by Phil Parkin. Reason: Removed unnecessary columns from select

For those who enjoy the additional challenge of working on the same question in multiple parallel threads, this question is also posed here.