How to execute a series of stored procedures using scala in databricks

[Sha_1890]

I am working in a migration project, where lift and shift method is used to migrate SQL server DB from onprem to AZure Cloud. There are a lot of stored procedures used for integration in On prem. Now here in On prem , to process the XMl file and execute the same procedures pointing to the cloud Db I need to write a code in Databricks. I have code to execute a single stored procedure using Scala, as I am new to coding in python/scala I couldn't find the right method to execute the procedures.

The below code is what I have used to execute one procedure- sample

%scala

val username = "xxxxx"

val pass = "xxxxx"

val url = "jdbc:sqlserver://***.database.windows.net:1433;databaseName=***"

val table = "SalesLT.Temp3"

val query = s"EXEC sp_truncate_table '${table}'"

val conn = DriverManager.getConnection(url, username, pass)

val rs = conn.createStatement.execute(query)

I have a requirement to execute some 10 stored procedures in series. Looking forward for your suggestions.

[-werners-]

So the above code works but you want to do this 10 times (for different SPs)?

[Sha_1890]

yes , I want to execute the list of procedure in sequential order.

[-werners-]

In a databricks notebooks, cells are executed sequentially

So you could create a cell per SP and execute those.

But I don´t get why you need Databricks/scala for this.

[Sha_1890]

The existing stored procedures in On prem has the integration part in Procedures which is then executed by .exe file to process the incremental data. so here in cloud instead of .exe file databricks is used to execute the procedures which is not possible through Python in a simple way. So I used Scala to execute the procedure.

[-werners-]

I see, but for that kind of use case I would use Data Factory.

As there is no data processing happening on Databricks itself, I see no added value in using it.

Calling SPs can be done in ADF, no scala needed.

[Sha_1890]

yes right,but the only concern here in using ADF is cost. Databricks is likely to be cheap than the ADF is what business has agreed and they want this logic to be implemented in Databricks.

[-werners-]

I am not sure if databricks will be cheaper.

ADF is pretty cheap if you do not use the Data Flow functionality.

[Noopur_Nigam]

Hi @shafana Roohi Jahubar​ I hope that your queries are answered. Please let me know if you have more doubts.