none
Importing Data from Access To SQL

    Question

  • Hi,

     

    Am recieving Daily report which orginized in  rows in excel sheet, I created Access database to orginize the data and I created Macros + Queries to read from the Excel Sheet and write the Data in the Access database then generate the report in a nice way.

     

    My question is : How to implement such thing in SQL database?

    I imported all the tables from Access to SQL, now I want to shift to SQL instead of Access.

     

    Please Advice.

     

    Regards,

    Tuesday, July 27, 2010 7:04 AM

Answers

  • Hi ba7ranya,

    I agree with Olaf to import an MS Access database into SQL Server please do the following; -

    Start; -
    1) Create a new SSIS Solution.
    2) Select your package.
    3) Drag a Control Flow Item "Data Flow" to your "Control Flow" pane.

    Set up your connection manager for your MS Access database; -
    1) Right click your mouse in the connection manager pane.
    2) Select "New OLE  DB Connection Manager".
    3) Click the new button.
    4) From within the look up box labelled "Provider:" select "Microsoft Jet 4.0 OLE Db Provider" or similar.
    5) Browse to the MS Access database you want to import.
    6) Click "OK"
    7) Click "OK" to confirm the "Configure OLE DB Connection Manager" settings.

    Set up your connection manager for your SQL Server table; -
    1) Right click your mouse in the connection manager pane.
    2) Select "New OLE  DB Connection Manager".
    3) Click the new button.
    4) Type in the name of your SQL Server, e.g. (local)
    5) Select your SQL Server database name; - e.g. AdventureWorksDW
    6) Click on "Test Connection" button.
    7) Click "OK" if test is successful.

    Tie the import process together within the data flow.
    MS Access data source; -
    1) Drag an "OLE DB Source" component to the "Data Flow" task.
    2) Right click this component and select "Edit"
    2) Select the name of the MS Access connection manager.
    3) Select the name of the table you wish to import.
    4) Click "OK".

    SQL Server destination; -
    1) Drag an "OLE DB Destination" to the "Data Flow" pane.
    2) Select the "OLE DB Source" component again.
    3) Drag the green arrow from the "OLE DB Source" component to point to the "OLE DB Destination"
    4) Right click the "OLE DB Destination"  component and select "Edit".
    5) Click on "New" if you wish to create a new SQL Table.
    6) Over type "OLE DB Destination" with a meaningful table name.
    7) Click "OK"
    8) Click the "Mappings" tab to check the mappings from the MS Access table columns to the SQL Server table columns are OK.


    Execution; -
    1) Click the green right pointing arrow to start debugging.
    2) On succesful execution the components will light up green.

    I hope the above helps!

    Here is a further SSIS tutorial; - http://msdn.microsoft.com/en-US/library/ms169917(v=SQL.90).aspx

    You could also try using the import / export wizard especially if you have lots of these MS Access tables to import to SQL Server, you can re-use packages you create using this wizard; - http://msdn.microsoft.com/en-us/library/ms141209.aspx

    Further link on using OLE DB Source; - http://msdn.microsoft.com/en-US/library/ms139767(v=SQL.90).aspx

    Kind Regards,

    Kieran.


     


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    • Marked as answer by ba7ranya Thursday, July 29, 2010 11:49 AM
    Tuesday, July 27, 2010 7:57 AM
  • Hi again,

    1) Try converting your MS Access 2010 database to MS Access 2007.
    1.1) I havn't got MS Access 2010, but in MS Access 2007; -
    1.1.1) you can get this by clicking on the Office button,
    1.1.2) select Save AS, select MS Access 2007 as a different file name or your original.
    2) Try "Provider:" select "Microsoft Office 12.0 Access Database Engine OLE DB Provider and and see what you get.

    The following link does intimate that "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity; -  http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    I've done a bit of research on the web for you. I can find any "OLE DB Provider" for Access 2010. Maybe one doesn't exist yet. I'd recommend using the MS Access 2007 file format from within your MS Access 2010 application, you may notice no loss of functionality.

    I hope this helps,

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    • Marked as answer by ba7ranya Thursday, July 29, 2010 11:49 AM
    Wednesday, July 28, 2010 9:25 AM

All replies

  • Hello,

    You could use SSIS = Sql Server Integration Services to load data from the excel sheets, transform and load them to a Sql Server database. Such a SSIS package can be started scheduled by Sql Server Agent, so that you don't need to handle the ETL process manually.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Tuesday, July 27, 2010 7:13 AM
  • Thanks for your prompt replay,

     

    is it going to do it auromatically or manually?

    Tuesday, July 27, 2010 7:53 AM
  • Hi ba7ranya,

    I agree with Olaf to import an MS Access database into SQL Server please do the following; -

    Start; -
    1) Create a new SSIS Solution.
    2) Select your package.
    3) Drag a Control Flow Item "Data Flow" to your "Control Flow" pane.

    Set up your connection manager for your MS Access database; -
    1) Right click your mouse in the connection manager pane.
    2) Select "New OLE  DB Connection Manager".
    3) Click the new button.
    4) From within the look up box labelled "Provider:" select "Microsoft Jet 4.0 OLE Db Provider" or similar.
    5) Browse to the MS Access database you want to import.
    6) Click "OK"
    7) Click "OK" to confirm the "Configure OLE DB Connection Manager" settings.

    Set up your connection manager for your SQL Server table; -
    1) Right click your mouse in the connection manager pane.
    2) Select "New OLE  DB Connection Manager".
    3) Click the new button.
    4) Type in the name of your SQL Server, e.g. (local)
    5) Select your SQL Server database name; - e.g. AdventureWorksDW
    6) Click on "Test Connection" button.
    7) Click "OK" if test is successful.

    Tie the import process together within the data flow.
    MS Access data source; -
    1) Drag an "OLE DB Source" component to the "Data Flow" task.
    2) Right click this component and select "Edit"
    2) Select the name of the MS Access connection manager.
    3) Select the name of the table you wish to import.
    4) Click "OK".

    SQL Server destination; -
    1) Drag an "OLE DB Destination" to the "Data Flow" pane.
    2) Select the "OLE DB Source" component again.
    3) Drag the green arrow from the "OLE DB Source" component to point to the "OLE DB Destination"
    4) Right click the "OLE DB Destination"  component and select "Edit".
    5) Click on "New" if you wish to create a new SQL Table.
    6) Over type "OLE DB Destination" with a meaningful table name.
    7) Click "OK"
    8) Click the "Mappings" tab to check the mappings from the MS Access table columns to the SQL Server table columns are OK.


    Execution; -
    1) Click the green right pointing arrow to start debugging.
    2) On succesful execution the components will light up green.

    I hope the above helps!

    Here is a further SSIS tutorial; - http://msdn.microsoft.com/en-US/library/ms169917(v=SQL.90).aspx

    You could also try using the import / export wizard especially if you have lots of these MS Access tables to import to SQL Server, you can re-use packages you create using this wizard; - http://msdn.microsoft.com/en-us/library/ms141209.aspx

    Further link on using OLE DB Source; - http://msdn.microsoft.com/en-US/library/ms139767(v=SQL.90).aspx

    Kind Regards,

    Kieran.


     


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    • Marked as answer by ba7ranya Thursday, July 29, 2010 11:49 AM
    Tuesday, July 27, 2010 7:57 AM
  • Hi again ba7ranya,

    You can execute a package manually within the SSIS development tool Business Intelligence Development studio (BIDs).

    You can also upload the SSIS package to your server create a scheduled job to execute the package on a regular basis. So to do this you need to import the package into SQL Server then set up your scheduled job.

    Link for importing the SSIS package into SQL Server ; - http://www.sql-server-performance.com/articles/biz/Importing_SSIS_Package_Using_SSMS_p1.aspx

    Link for setting up the SSIS package as a scheduled job; - http://it.toolbox.com/blogs/coding-dotnet/ssis-as-job-25068

    I hope this helps.

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Tuesday, July 27, 2010 8:01 AM
  • Olaf and Kieran ,

    Many thanks for ur support.

     

    Tuesday, July 27, 2010 10:25 AM
  • Hi again,

     

    I reached these steps :

    Tie the import process together within the data flow.
    MS Access data source; -
    1) Drag an "OLE DB Source" component to the "Data Flow" task.
    2) Right click this component and select "Edit"
    2) Select the name of the MS Access connection manager.
    3) Select the name of the table you wish to import.
    4) Click "OK".

     

     

    But an error saying  :" microsoft jet database engine: unrecognized database format"

     

    am using MS Access 2010 and MS SQL 2008 R2 Standard edition

    Tuesday, July 27, 2010 12:05 PM
  •  microsoft jet database engine: unrecognized database format

    For a MS Access 2010 database you should use the "Microsoft Office 12.0 Access Database Engine OLE DB Provider", not the old Jet Engine Providers.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Tuesday, July 27, 2010 12:17 PM
  • Hi,

     

    Again compatibility issue, even after installing  "Microsoft Office 12.0 Access Database Engine OLE DB Provider",

     

    Regards,

    Wednesday, July 28, 2010 5:52 AM
  • Hi again,

    1) Try converting your MS Access 2010 database to MS Access 2007.
    1.1) I havn't got MS Access 2010, but in MS Access 2007; -
    1.1.1) you can get this by clicking on the Office button,
    1.1.2) select Save AS, select MS Access 2007 as a different file name or your original.
    2) Try "Provider:" select "Microsoft Office 12.0 Access Database Engine OLE DB Provider and and see what you get.

    The following link does intimate that "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity; -  http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    I've done a bit of research on the web for you. I can find any "OLE DB Provider" for Access 2010. Maybe one doesn't exist yet. I'd recommend using the MS Access 2007 file format from within your MS Access 2010 application, you may notice no loss of functionality.

    I hope this helps,

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    • Marked as answer by ba7ranya Thursday, July 29, 2010 11:49 AM
    Wednesday, July 28, 2010 9:25 AM
  • Its fine now i tested it by downgrading to Access 2003, but the problem is that i cannot import the macros.

     

    I have a macro that read from excel and update the access database, what can i do to do same job in sql server?

    Wednesday, July 28, 2010 10:19 AM
  • Hi again,

    Why did you downgrade your database file version to Access 2003 instead of Access 2007. Your macros are more likely to work with Access 2007 than Access 2003 if you developed your macros in Access 2010?

    You can develop an SSIS package to read an Excel spreadsheet into SQL Server.

    I hope this helps.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Wednesday, July 28, 2010 4:59 PM
  • Hi Kieran,

     

    am now developing the package , but my question is, how to access this packages to create visual report .

     

    I created a report from the same database but in different project, now how to integrate the projects?

     

    and after the integration, how do i access them ? from report server?

    or integrate them with the sharepoint? because i have already sharepoint server that running a portal.

     

     

     

    sorry for bothering you but I need to learn these basic issues.

     

    Many thanks for your support,

     

    Regards,

    Thursday, July 29, 2010 8:42 AM
  • Hi ba7ranya,

    It's a pleasure helping you. However your latest addition to this question thread looks like a new question which needs to be posted into the SQL Server Reporting Services forum.

    So please mark the replies that helped you to "Import Data from Access To SQL" as an answer. Then create a new question in the SSRS forum; - http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/threads/

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Thursday, July 29, 2010 10:41 AM
  • Hello moderator type person!

    Since I don't have access rights to move the location of a question thread yet :) Please could you move this question thread from Analysis Services to Integration Services, where the information held within the thread will be better placed to help someone resolve a similar issue.

    Thanks in advance,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Thursday, July 29, 2010 10:43 AM
  • Hello,

     

     

    I created new question there .

     

    thx for ur greate support

    Thursday, July 29, 2010 11:02 AM
  • You are very welcome.

    Please mark the replies that helped you to "Import Data from Access To SQL" as an answer. I'll be able to have another look at your questions at 7pm GMT.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Thursday, July 29, 2010 11:48 AM
  • Helo,

     

    I the following image;

    http://www.m5zn.com/uploads/2010/7/29/photo/072910060745ll1dr0xs7e8glwitt.jpg 

    can i do some filteration (i.e adding delete query) for the data coming from excel befor it have been stored in SQL ?

     

    if yes, how to do it?

     

    Regards,

    Thursday, July 29, 2010 1:25 PM
  • I executed the package (from excel to OLE) but there were errors :

     

    [Excel Source [158]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

     

    [SSIS.Pipeline] Error: component "Excel Source" (158) failed validation and returned error code 0xC020801C.

     

    [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

     

     

    please advice.

    Thursday, July 29, 2010 2:17 PM
  • Hello,

    I have a similar problem, I have a Raport with 3 data sources, and one of them points to a MS Access database and the other 2 points to a SQL database.

    It works fine when i am in BIDS, but when I deploy it, the data source of MS Access database do not work. When I test the data source connection in http://localhost/Reports/ it gives me the message "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

    I have installed:

    "Microsoft Office Access database engine 2007 (Engish)"

    "Microsoft Access database engine 2010 (Engish)"

    "Microsoft Access Runtime 2010"

    Why it give me this message only in IIS and all goes fine in BIDS?

    Tuesday, November 09, 2010 5:55 PM