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
- HANA Studio.
- MS Visual Studio https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx
- Business Intelligence tools for Visual Studio https://www.microsoft.com/en-us/download/details.aspx?id=42313
- HANA Client
Sample Data Source
Create a csv file with few records.
Steps
- 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.
Select SAP HANA as the data source.
Provide the HANA server details.
If the connection is successful, we will be able to view the HANA schemas in Visual Studio.
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.
5. Create a new SSIS project to load data from a csv file to HANA database.
6. Drag ‘Data Flow Task’ to the ‘Control Flow’ and rename it.
7. Double click on the data flow task – ‘Fetch Product Data’ and it will take us to the ‘Data Flow’ screen.
8. Drag ‘Flat File Source’ into ‘Data Flow’.
9. Double click on the entity ‘Flat File Source’ to upload the csv file.
10. Click on the ‘New’ button and load the file.
11. Go to ‘Columns’ tab and verify the format.
12. Go to ‘Advanced tab’ and provide names to the columns and if required, adjust the output column width.
13. Preview the data and click ‘OK’.
14. Click on ‘Columns’ to view the input columns.
15. Register the ODBC connection through device manager (Operating System).
Click on Windows – start button and search for ‘ODBC.
16. Select the 32-bit version (based on Visual Studio version) and create a System DSN.
17. Select the ‘HANA database ODBC 32 bit’ driver and click on Finish button.
18. Provide the server and port no of HANA database and click on ‘Connect’ button.
19. Provide the user credentials of HANA and click on ‘OK’ button.
20. Popup message will get displayed if the connection is successful.
21. Click the ‘OK’ buttons and close the dialog boxes.
22. Go back to the Visual Studio and drag ‘ODBC Destination’ to ‘Data Flow’.
23. Connect the ‘Flat File Source’ to ‘ODBC Destination’.
24. Right click on ODBC destination and select ‘Edit’.
25. Click on ‘New’ button.
Again click on ‘New’ button.
26. Select the data source created earlier and provide the user credentials.
27. Test the connection and click on ‘OK’ button.
28. Click on ‘OK’ button.
29. Select the data access mode and destination table in HANA.
30. Go to the ‘Mappings’ section and verify the source and destination mappings.
31. Click on ‘OK’ button and navigate back to the ‘Data Flow’.
32. Initiate the data transfer by clicking on the ‘Start’ button.
33. Successful data transfer will be indicated by a green icon.
34. Click on the ‘Progress’ tab to view the log.
35. Verify the imported data from HANA studio.
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?
Add both the 32bit and 64bit HANA Client to resolve that error message.