Search code examples
sqlexcelssisbusiness-intelligence

SSIS Loading Just The Header From Excel

Using SSIS to load an excel file, but the header info is in a different format than normal.

Instead of looking like this: 

Program Name  Report Date Load Date
-------------------------------------
Info          Info        Info
Info          Info        Info

The actual excel header looks like this : 

Program Name  Info
Report Date   Info 
Load Date     Info

​

If I try to map the columns into a database table it comes messed up and ends up looking like this: 

  Program Name  Report Date Load Date
  -------------------------------------
  Program Name  Info        Null      
  Null          Null        Null
  Report Date   Info        Null
  Null          Null        Null
  Load Date     Info        Null

I don't know what I'm doing wrong here, may it be due to the heading format being displayed horizontally instead of the normal vertical format, if so is any ideas on how to correctly load into my table?

Thanks in advance.

Solution

  • you are looking to transpose row data to column format there is a good example..

    https://blogs.msdn.microsoft.com/philoj/2007/11/10/transposing-rows-and-columns-in-sql-server-integration-services/