SSIS ETL, Lectura MS Access

https://www.mssqltips.com/sqlservertip/2656/simple-step-by-step-process-to-import-ms-access-data-into-sql-server-using-ssis/

Getting Started

In this demonstration we are going to create a Table named Customer in MS Access and then import the table to SQL Server using SSIS.

1. Let's start the MS Access. Go to the Windows Start Menu > All Programs > Microsoft Office and click Microsoft Access 2010 
   
   
   
2. In Microsoft Access 2010, click the Browse icon.
    
   
3. In the File New Database, specify the file name and the path. In this instance the file name will be customer.accdb and it will be stored in the c:\ drive and press OK. 
   
   
4. In Access, press the Create button. 
5. In the second column double click in the header and type FirstName. 
6. In second row enter a First Name.
    
   
7. Add some more data to the table.
    
   
8. Press the Save icon and in the Save As Window, type Customer and press OK.
   
9. You have just created an Access Database named customer.accdb with a table named customer. Close MS Access. 
10. Let's start the SQL Server Business Intelligence Development Studio to import the table into SQL Server. Go to the Windows Start > All Programs > Microsoft SQL Server 2008 R2> SQL Server Business Intelligence Development Studio.
     
    
11. In the Business Intelligence Development Studio, select File > New > Project
     
    
12. In the New Project Window, select Integration Services Project and specify a name and location for the project.
     
    
13. From the Toolbox, drag and drop a DataFlow Task to the Design pane. 
14. In the design pane double click the DataFlow Task.
     
    
15. In the Data Flow tab, drag and drop a OLEBD Source and Destination to the design pane.
     
    
16. Click on the green arrow and drag it to join the OLEDB Source and Destination.
     
    
17. In the OLE DB Source Editor window, press the New... button to create a new connection.
     
    
18. In the Configure OLE DB Connection Manager, press New... again.
     
    
19. In the Provider combobox, select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider. 
20. In the Server or file name type the path of the Access database. In this example it is in the c:\Customers.accdb (verify the step 3 to review). 
21. Check the Blank Password option and press OK.
     
    
22. In the Configure OLE DB Connection Manager, press OK 
23. In the OLE DB Source Editor, in the Name of the Table or view select the table created in Access (created in step 8).
     
    
24. Double click the OLEDB Destination.
     
    
25. In the OLE DB Destination Editor Window, in the OLE DB connection manager, press the New... button.
     
    
26. In the Configure OLE DB Connection Manager, press New...
     
    
27. In the Connection Manager make sure that the Provider combobox is using the Native OLE DB\SQL Server Native Client 10.0 
28. In the Server name, type the SQL Server Name. 
29. In the select or enter Database Name, select the SQL Server database where you want to store the Access data and press OK. You can choose any database. In this instance, a database named test is used.
     
    
30. In the OLE DB Destination Editor Window, go to the Name of the table or View combobox and press the New... button.
     
    
31. In the Create Table, type this and press OK:
     

    CREATE TABLE [Customers](
    [ID] int,
    [FirstName] nvarchar(255)
    )
    

    
    
    
32. In the OLE DB Destination Editor Click on the Mappings page and press OK.
     
    
33. Now the project is ready to start importing the data from MS Access to SQL Server. Now, press the start debugging icon to start importing the data.
     
    
34. To verify that everything worked, open SQL Server Management Studio and verify the data is in the database used for the import. In this case, the test database was used (verify this from step 29). 
35. Finally query the table to verify the data was successfully imported.
     
    
36. Congratulations! You have created an SSIS project to import data from MS Access to SQL Server.