none
Excel load via SSIS

    Question

  • Hi, I need help in loading a spreadsheet into SQL Server table and its a SQL Server 2005 instance using SSIS 2005. The spreadsheet we get has multiple column headers for each state. How to make a dynamic SSIS package. For instance

    



    • Edited by ione721 Monday, November 19, 2012 4:43 AM Insufficient information
    Monday, November 19, 2012 12:17 AM

All replies

  • Hi,

                  Try unpivoting in Excel itself and then import the data (or)

    like below [after importing data as it is from Excel excluding Date column alone]

    DECLARE @TMP TABLE (Store VARCHAR(20),TotalSales INT,TotalSales1 INT,TotalSales2 INT)
    INSERT @TMP (Store,TotalSales ,TotalSales1 ,TotalSales2) SELECT 'Illinois',10,20,30
    INSERT @TMP (Store,TotalSales ,TotalSales1 ,TotalSales2) SELECT 'New England',50,100,200
    DECLARE @TEMP TABLE(NUM INT,Store VARCHAR(20),TotalSales INT,[Date] DATE)
    INSERT @TEMP (NUM ,Store,TotalSales)
    SELECT Row_Number() OVER(Partition by Store order by Store) NUM,Store,price AS TotalSales FROM
    (SELECT Store,TotalSales,TotalSales1,TotalSales2 FROM @TMP) P
    UNPIVOT
       (price FOR Sales IN 
          (TotalSales ,TotalSales1 ,TotalSales2)
    )AS unpvt
    UPDATE @TEMP SET [Date] = '10/1/2012' WHERE NUM = 1
    UPDATE @TEMP SET [Date] = '10/8/2012' WHERE NUM = 2
    UPDATE @TEMP SET [Date] = '10/2/2012' WHERE NUM = 3
    SELECT * FROM @TEMP


    Thanks & Regards, sathya


    Monday, November 19, 2012 3:35 AM
  • Thanks SathyanarrayananS for your response. I am looking at something where we do not have to change anything manually.

    Regards


    • Edited by ione721 Thursday, April 04, 2013 9:08 PM insufficient
    Monday, February 11, 2013 3:49 PM
  • Could somebody please look into this.

    Thanks.............

    Thursday, April 04, 2013 9:06 PM