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.
Hello friends, is ther any license restriction if we setup an ETL process from HANA DB to MS SSIS productive usage.?
Regards
Franklin
Hi, i have problems when i invoke the dts remote, if i ejecute works fine, but when i schedule the job oldb sap conexction refuse.
Im using the 32bit driver
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 9:16:15 AM
Progress: 2019-05-16 09:16:16.41
Source: Correlativo
Validating: 0% complete
End Progress
Error: 2019-05-16 09:16:16.43
Code: 0xC0014020
Source: Package Connection manager "SAPHANAPRO32.XXX"
Description: An ODBC error -1 has occurred.
End Error
Error: 2019-05-16 09:16:16.43
Code: 0xC0014009
Source: Package Connection manager "SAPHANAPRO32.XXX"
Description: There was an error trying to establish an Open Database Connectivity (ODBC)
connection with the database server.
End Error
Hi,
I have been able to connect using the ODBC, but the performance is really bad, it takes about 1.5 hours to load 100K records from the server I run from, but if I run it from a local machine it runs fast within 2 mins. Is there something in settings or I can check to make sure the performance is better from server side.
Thanks in advance,
Alex
Hi Sam did you ever get a fix for this ?, I am like you described having issues with how slow the data transfer is.
Thanks for your time.