Click here to monitor SSC

SSIS Excel Data import problem

Last post 03-15-2012, 5:58 AM by marchvey. 3 replies.
Sort Posts: Previous Next
  •  04-30-2007, 6:39 PM Post number 24722

    SSIS Excel Data import problem

    Hi,

     

    I have an excel 2007 file with data in certain cells like A20, B13 etc.

    I would like to import/read specific excel cell data (like A20, B13 etc)  into separate rows of a database table using SSIS Packages.

    Any idea as to how this can be achieved?

     

    Cheers

    Sam

  •  05-01-2007, 3:55 AM Post number 24745 in reply to post number 24722

    Re: SSIS Excel Data import problem

    Ah. there's a problem here. 'While you can use an Excel Connection Manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007'. (BOL).
    Robyn and I didn't include a direct READ of cells using OLE automation in our article SQL Server Excel Workbench, but you should be able to adapt the code pretty easily. I know there is something slighly wicked in using OLE automation in a production system, but there are ways around the security issues.

  •  05-01-2007, 1:16 PM Post number 24766 in reply to post number 24745

    Re: SSIS Excel Data import problem

    Hi Phil,

    After posting this topic, I was able to access excel data using OPENROWSET function as mentioned in your article with some help from BOL.

    here is the query to access Excel 2007:-

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=D:\DATA_SOURCE\Data2.xlsx', 'SELECT * FROM [Sheet1$D3:D3]');

    But then this is using SQL.

    If I dont want to use the SQL approach in SSIS , then is it possible to achieve the result using SSIS packages.

    Cheers

    Sam

     

     

     

     

     

  •  03-15-2012, 5:58 AM Post number 106724 in reply to post number 24766

    Re: SSIS Excel Data import problem

    Which wizards are you referring to?  As far as I know, every wizard in
    the product produces SSIS packages, not DTS packages.  DTS appears to be
    available only for running legacy packages -- you cannot edit DTS
    packages or do much at all with them in 2005.
View as RSS news feed in XML