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.

3 Comments

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

  1. Former Member

    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) 
  2. Former Member

    Hello friends, is ther any license restriction if we setup an ETL process from HANA DB to MS SSIS productive usage.?

     

    Regards

     

    Franklin

    (0) 

Leave a Reply