OpenQuery VS SSIS

Question

Hi All,

I am working on a query which should insert data into sql server table from source SAP HANA. we have a virtualization tool to write queries on SAP HANA and this query is created as a view in the virtualization tool.

Now in SSMS, I call this view using open query to insert the data (around 100 million rows). The view on the virtualization tool to generate the result for 100 million rows it takes around 1 - 1.5 hrs and when I call this view using open query to insert the data into the sql server table it looks like it reads on virtualization tool side but it does not write/insert the data even after 5 hrs. I cannot tell whether it reads first and then it writes that data into the table on sql side. I had to stop the query and run couple of times when there was no progress.

I tweaked the open query a little and made that to execute in a loop based on parameter, now it writes the query instantaneously but the read becomes slower for every iteration (100k rows) and it takes around 1 -2 mins, at this pace for all the 100 millions it might take more than a day.

Bottom line, using open query I realized it takes a lot of time to insert data into sql server table.

Then I tried to work on a data flow in SSIS (I have used both OLEDB Source with open query as SQL command and ODBC source to directly connect to the published view on Virtualization tool) and both seems to work at the same pace, to insert 15 million records it takes around 5 hours.

Can you please help me if there is a possible approach I can take to insert the data into sql server from this virtualization tool and looking at this huge amount of data (100 Million records) my expectation is to load the data in 2hrs - 3 hrs. also please let me know if this can be achieved.