Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to connect Flat File using OLE DB Source

Answered how to connect Flat File using OLE DB Source

  • Wednesday, May 02, 2012 1:40 PM
     
     
    Sign In to Vote
    0

    hi ,

    as after reading so many artical i got to know that OLE DB is capable of accessing any data source (Relation DB or Non Relation DB like text file, excel spredsheed, etc)

    but i dont know how to use SSIS OLE DB for accessing Text file or spreadsheet, ai can do access excel or text using there dedicated source provided in SSIS but i want to use OLE DB specifically only.

    please let me know how can i achieve this. any valuable ref link would be highly appreciable.

    many many thanks in advance.

    Regards, Shanu

     

All Replies

  • Wednesday, May 02, 2012 1:49 PM
     
     Proposed Answer
    Sign In to Vote
    0

    /*

    • For files that have an Excel 2003 or earlier format, the Excel connection manager, uses the Microsoft Jet OLE DB Provider.

    • For files that have the Excel 2007 or later format, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. This provider is installed automatically with the 2007 Microsoft Office system. If the 2007 Office system is not installed on the computer on which Integration Services is running, you have to install the provider separately. To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the components on this Web page, 2007 Office System Driver: Data Connectivity Components. For more information on the file formats that Excel 2007 supports, see File formats that are supported in Excel.

    • */

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, May 09, 2012 8:30 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Shanu,

    Besides Uri's professional suggestion, you can use Microsoft Jet 4.0 OLE DB Provider to connect to Flat file. For more detail steps, please refer to:
    http://stackoverflow.com/questions/7626971/ssis-flatfile-acces-via-jet 


    Thanks,
    Eileen

     
  • Wednesday, May 09, 2012 8:34 AM
     
     
    Sign In to Vote
    0

    Besides Uri's professional suggestion, you can use Microsoft Jet 4.0 OLE DB Provider to connect to Flat file. For more detail steps, please refer to:
    http://stackoverflow.com/questions/7626971/ssis-flatfile-acces-via-jet 

    It's indeed possible - I've done it before - but it is quite a hassle because you need to make sure a .INI file is present that lays out the metadata of the flat file.

    I would stick with the flat file connection manager in SSIS, as you can run that one in 64-bit.

    MCTS, MCITP - Please mark posts as answered where appropriate.