Skip to Content

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 https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx
  3. Business Intelligence tools for Visual Studio   https://www.microsoft.com/en-us/download/details.aspx?id=42313
  4. HANA Client

Sample Data Source

Create a csv file with few records.

/wp-content/uploads/2016/08/img1_1010123.jpg

Steps

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

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

/wp-content/uploads/2016/08/img2_1010124.jpg

Select SAP HANA as the data source.

/wp-content/uploads/2016/08/img3_1010125.jpg


Provide the HANA server details.


/wp-content/uploads/2016/08/img4_1010127.jpg


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

/wp-content/uploads/2016/08/img5_1010126.jpg


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.


/wp-content/uploads/2016/08/img6_1010128.jpg


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

     /wp-content/uploads/2016/08/img7_1010129.jpg

/wp-content/uploads/2016/08/img8_1010130.jpg

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


/wp-content/uploads/2016/08/img9_1010131.jpg

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

/wp-content/uploads/2016/08/img10_1010133.jpg  

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


/wp-content/uploads/2016/08/img11_1010134.jpg


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


/wp-content/uploads/2016/08/img12_1010135.jpg


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


/wp-content/uploads/2016/08/img13_1010136.jpg


/wp-content/uploads/2016/08/img14_1010137.jpg


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


/wp-content/uploads/2016/08/img15_1010138.jpg


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


/wp-content/uploads/2016/08/img16_1010140.jpg


13. Preview the data and click ‘OK’.


/wp-content/uploads/2016/08/img17_1010141.jpg


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


/wp-content/uploads/2016/08/img18_1010142.jpg


15. Register the ODBC connection through device manager (Operating System).

          Click on Windows – start button and search for ‘ODBC.

/wp-content/uploads/2016/08/img19_1010143.jpg


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


/wp-content/uploads/2016/08/img20_1010144.jpg


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

/wp-content/uploads/2016/08/img21_1010145.jpg


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

/wp-content/uploads/2016/08/img22_1010147.jpg


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


/wp-content/uploads/2016/08/img23_1010148.jpg


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


/wp-content/uploads/2016/08/img24_1010149.jpg


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

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


/wp-content/uploads/2016/08/img25_1010150.jpg


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

/wp-content/uploads/2016/08/img26_1010151.jpg


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


/wp-content/uploads/2016/08/img27_1010152.jpg


25. Click on ‘New’ button.  


/wp-content/uploads/2016/08/img28_1010153.jpg


  Again click on ‘New’ button.


/wp-content/uploads/2016/08/img29_1010154.jpg


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


/wp-content/uploads/2016/08/img30_1010155.jpg


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


https://s31.postimg.org/czdmh23fv/img31.jpg


28. Click on ‘OK’ button.


https://s32.postimg.org/pk16uhnwl/img32.jpg


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


https://s31.postimg.org/torxfalor/img33.jpg


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


https://s31.postimg.org/9qfa4c17v/img34.jpg


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


https://s32.postimg.org/ioxhoubs5/img35.jpg


32. Initiate the data transfer by clicking on the ‘Start’ button.


https://s31.postimg.org/yf08n16a3/img36.jpg


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


https://s31.postimg.org/8q0mq2yp7/img37.jpg


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


https://s31.postimg.org/x99k9oki3/img38.jpg


35. Verify the imported data from HANA studio.


https://s32.postimg.org/fx3k9hhth/img39.jpg









To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Jack van der wielen

    When adding the HANA connection I receive the error message
    “The type initializer for ‘Sap.Data.Hana.HanaConnection’ threw an exception”

    I have both the HANA Client, HANA Studio, VS2013 installed and add the references through the Toolbox selection.  Was there anything else that was needed?

    (0) 

Leave a Reply