Post reply

Support of JET OLEDB

  • October 2, 2013 at 1:51 pm

    #277748

    All,

    We are planning to upgrade from SQL Server 2005 to SQL Server 2012.

    1. We have many SSIS packages. What is the best apporach to migrate them?

    2. We have many SSIS packages using JET OLEDB provider. How to make this work in SQL 2012?

    Thanks in advance.

    Regards,

  • October 2, 2013 at 2:21 pm

    #1655328

    I have always done this manually because of the types of SSIS packages I have dealt with did not work well with the Visual Studio conversion wizard. That said, here's a good SSC article on the topic: Upgrading Packages to SSIS 2012[/url]

    Regarding the Jet oledb provider:

    I have not used this before but my understanding is that you would use ACE, the Microsoft Access Database Engine 2010 Redistributable.

    If you need JET OLEDB because you are grabbing data from Access I would consider moving that data to SQL Server; JET OLEDB is crap IMHO.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • October 2, 2013 at 2:28 pm

    #1655332

    Thanks very much Alan.

    Management don't want to move Access data to SQL Server. It would be great if you could help me in using JET OLEDB in SQL 2012.

    Regards,

    Paul

  • October 2, 2013 at 2:55 pm

    #1655337

    pawana.paul (10/2/2013)

    Thanks very much Alan.

    Management don't want to move Access data to SQL Server. It would be great if you could help me in using JET OLEDB in SQL 2012.

    Regards,

    Paul

    It's not advertised as such because MS wants us to use SSIS but the ACE drivers are basically the replacement for the JET drivers, not just Access loads.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 2, 2013 at 3:07 pm

    #1655343

    Thanks Jeff.

    I tried to find ACE Drivers details but not able to find much. Please let me know the web links where I can gain more inforamtion.

    Regards,

    Paul

  • October 2, 2013 at 7:27 pm

    #1655364

    pawana.paul (10/2/2013)

    Thanks Jeff.

    I tried to find ACE Drivers details but not able to find much. Please let me know the web links where I can gain more inforamtion.

    Regards,

    Paul

    Gosh. There's an absolute shedload of information on Google about it.

    Since you're mostly interested in using it with SSIS, try searching for "SQL SERVER ACE DRIVERS SSIS" . It looks really good stuff starts on the second page where you'll find links like the following...

    http://tsqlday.blogspot.com/2012/07/ssis-reading-excel-xls-data-in-64-bit.html

    I am a little concerned though... I thought SSIS didn't need such trickery as using driver code to load data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2013 at 12:26 am

    #1655404

    Jeff Moden (10/2/2013)

    pawana.paul (10/2/2013)

    Thanks Jeff.

    I tried to find ACE Drivers details but not able to find much. Please let me know the web links where I can gain more inforamtion.

    Regards,

    Paul

    Gosh. There's an absolute shedload of information on Google about it.

    Since you're mostly interested in using it with SSIS, try searching for "SQL SERVER ACE DRIVERS SSIS" . It looks really good stuff starts on the second page where you'll find links like the following...

    http://tsqlday.blogspot.com/2012/07/ssis-reading-excel-xls-data-in-64-bit.html

    I am a little concerned though... I thought SSIS didn't need such trickery as using driver code to load data.

    SSIS needs providers to get to the data.

    For Excel and Acces: Jet or ACE OLE DB providers.

    For Oracle: Oracle OLE DB provider

    For DB2: Microsoft DB2 OLE DB provider

    and so on...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 3, 2013 at 12:27 am

    #1655405

    pawana.paul (10/2/2013)

    Thanks Jeff.

    I tried to find ACE Drivers details but not able to find much. Please let me know the web links where I can gain more inforamtion.

    Regards,

    Paul

    Better brush up your google skills.

    I entered "ace ole db" in Google and the first hit was the download page.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply