none
SSIS Source Metadata

    Question

  • Dear All,

    How SSIS OLEDB Source component identifies the datatype of the source file columns automatically ?

    I know we can go and change it but still how it identifies what goes at the backend ?

    Thanks

     


    Anky
    Sunday, May 22, 2011 5:09 AM

Answers

  • The Excel connection manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources.

    http://msdn.microsoft.com/en-us/library/ms139836.aspx

    To work with Excel 2007 files, you have to use the "ACE" provider, which appears in the providers list as "Microsoft Office 12.0 Access Database Engine OLE DB Provider". The Office 12 team took the code for Jet and the Excel driver and updated it to work with the Excel 12/2007 file format (.xlsx). Of course it also works with previous versions of Excel file formats


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    • Marked as answer by Anky_007 Thursday, May 26, 2011 7:41 AM
    Monday, May 23, 2011 10:19 AM

All replies

  • this the behavour of the SSIS engine. when you connect the oledb source , it will look at the datatype of the source RDBMS by default.

    if its a text file then it will suggest the datatype based on teh first top 500 rows.


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
    Sunday, May 22, 2011 5:57 AM
  • I know its a behaviour of SSIS engine but i am curious to know what happens at the backend ?

    How it identifies that datatype is string or integer etc ?

     


    Anky
    Sunday, May 22, 2011 8:06 AM
  • I know its a behaviour of SSIS engine but i am curious to know what happens at the backend ?

    How it identifies that datatype is string or integer etc ?

     


    Anky

    the OLEDB Source probably tries to fetch metadata information from the source database. there are Catalog tables/views in every RDBMS which can be used to fetch metadata information. for example in sql server these table/views exists: sysobjects, syscolumns ....

     


    http://www.rad.pasfu.com
    Sunday, May 22, 2011 8:38 AM
    Moderator
  • but if the source flat file then ?
    Anky
    Sunday, May 22, 2011 11:03 AM
  • I've searched for this answer numerous times (usually after I've clicked the Suggest Type button on the Flat Files Advanced Tab) as it has always puzzled me how SSIS could get is so wrong :-).

    I did read in an article on Excel importing that it's the Excel ISAM driver that determines the types, not SSIS. I wondered if this was the case for the Flat File provider however couldn't find anything on that either.


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Monday, May 23, 2011 4:29 AM
  • It might be similar to SSAS which detects Facts and dimensions in a datawarehouse. Probably, we might need to check which dll is loaded during the detection?
    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Monday, May 23, 2011 5:52 AM
  • I did read in an article on Excel importing that it's the Excel ISAM driver that determines the types, not SSIS. I wondered if this was the case for the Flat File provider however couldn't find anything on that either.


    Isn't the JET provider (or the ACE OLE DB provider for .xlsx) responsible for determing data types for Excel?
    MCTS, MCITP - Please mark posts as answered where appropriate.
    Monday, May 23, 2011 8:17 AM
  • The Excel connection manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources.

    http://msdn.microsoft.com/en-us/library/ms139836.aspx

    To work with Excel 2007 files, you have to use the "ACE" provider, which appears in the providers list as "Microsoft Office 12.0 Access Database Engine OLE DB Provider". The Office 12 team took the code for Jet and the Excel driver and updated it to work with the Excel 12/2007 file format (.xlsx). Of course it also works with previous versions of Excel file formats


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    • Marked as answer by Anky_007 Thursday, May 26, 2011 7:41 AM
    Monday, May 23, 2011 10:19 AM