Tuesday, 9 August 2016

How to connect Microsoft SSIS with SAP HANA

SSIS (SQL Server Integration Services) is a component of the MS SQL Server which can be utilized for various data migration tasks. This blog provides the step by step process with screenshots to implement a connection between SAP HANA and MS SSIS to perform data transfer.


Tools Required

  1. HANA Studio.
  2. MS Visual Studio 
  3. Business Intelligence tools for Visual Studio   
  4. HANA Client

Sample Data Source

Create a csv file with few records.

How to connect Microsoft SSIS with SAP HANA

Steps

1. Install the tools in following sequence.
  • Visual Studio
  • BI tools for Visual Studio
  • HANA Client

2. Open the Visual Studio and create a data source connection to HANA.

How to connect Microsoft SSIS with SAP HANA

Select SAP HANA as the data source.

How to connect Microsoft SSIS with SAP HANA

Provide the HANA server details.

How to connect Microsoft SSIS with SAP HANA

If the connection is successful, we will be able to view the HANA schemas in Visual Studio.

How to connect Microsoft SSIS with SAP HANA

3. Create a destination table in HANA database using HANA studio under the desired schema.
    Sample table: PRODUCTS
    Columns: MATNR, SPRAS, MAKTX, MAKTG

4. Verify whether the table can be accessed from Visual Studio.

How to connect Microsoft SSIS with SAP HANA

5. Create a new SSIS project to load data from a csv file to HANA database.

How to connect Microsoft SSIS with SAP HANA

How to connect Microsoft SSIS with SAP HANA


6. Drag ‘Data Flow Task’ to the ‘Control Flow’ and rename it.

How to connect Microsoft SSIS with SAP HANA

7. Double click on the data flow task – ‘Fetch Product Data’ and it will take us to the ‘Data Flow’ screen

How to connect Microsoft SSIS with SAP HANA

8. Drag ‘Flat File Source’ into ‘Data Flow’.

How to connect Microsoft SSIS with SAP HANA

9. Double click on the entity ‘Flat File Source’ to upload the csv file.

How to connect Microsoft SSIS with SAP HANA

10. Click on the ‘New’ button and load the file.

How to connect Microsoft SSIS with SAP HANA

How to connect Microsoft SSIS with SAP HANA

11. Go to ‘Columns’ tab and verify the format.

How to connect Microsoft SSIS with SAP HANA

12. Go to ‘Advanced tab’ and provide names to the columns and if required, adjust the output column width.

How to connect Microsoft SSIS with SAP HANA

13. Preview the data and click ‘OK’.

How to connect Microsoft SSIS with SAP HANA

14. Click on ‘Columns’ to view the input columns.

How to connect Microsoft SSIS with SAP HANA


15. Register the ODBC connection through device manager (Operating System).
      Click on Windows – start button and search for ‘ODBC.

How to connect Microsoft SSIS with SAP HANA

16. Select the 32-bit version (based on Visual Studio version) and create a System DSN.

How to connect Microsoft SSIS with SAP HANA

17. Select the ‘HANA database ODBC 32 bit’ driver and click on Finish button.

How to connect Microsoft SSIS with SAP HANA

18. Provide the server and port no of HANA database and click on ‘Connect’ button.

How to connect Microsoft SSIS with SAP HANA

19. Provide the user credentials of HANA and click on ‘OK’ button.

How to connect Microsoft SSIS with SAP HANA

20. Popup message will get displayed if the connection is successful.

How to connect Microsoft SSIS with SAP HANA

21. Click the ‘OK’ buttons and close the dialog boxes.

22. Go back to the Visual Studio and drag ‘ODBC Destination’ to ‘Data Flow’.

How to connect Microsoft SSIS with SAP HANA

23. Connect the ‘Flat File Source’ to ‘ODBC Destination’.

How to connect Microsoft SSIS with SAP HANA

24. Right click on ODBC destination and select ‘Edit’.

How to connect Microsoft SSIS with SAP HANA

25. Click on ‘New’ button.  

How to connect Microsoft SSIS with SAP HANA

Again click on ‘New’ button.

How to connect Microsoft SSIS with SAP HANA

26. Select the data source created earlier and provide the user credentials.

How to connect Microsoft SSIS with SAP HANA

27. Test the connection and click on ‘OK’ button.

How to connect Microsoft SSIS with SAP HANA

28. Click on ‘OK’ button.

How to connect Microsoft SSIS with SAP HANA

29. Select the data access mode and destination table in HANA.

How to connect Microsoft SSIS with SAP HANA

30. Go to the ‘Mappings’ section and verify the source and destination mappings.

How to connect Microsoft SSIS with SAP HANA

31. Click on ‘OK’ button and navigate back to the ‘Data Flow’

How to connect Microsoft SSIS with SAP HANA

33. Successful data transfer will be indicated by a green icon.

How to connect Microsoft SSIS with SAP HANA

34. Click on the ‘Progress’ tab to view the log.

How to connect Microsoft SSIS with SAP HANA

35. Verify the imported data from HANA studio.

How to connect Microsoft SSIS with SAP HANA

Source: scn.sap.com