Move data from locally installed SQL Server to Azure SQL in Nifi

Question

Hi,My task is to move data from my locally installed SQL server (16.0) to Azure SQL (12.0) hosted on my Azure subscription. So far, I have used ExecuteSQL processor to run my select query from SQL Server and it returns me the rows successfully. I have created a Database Polling Connection Service which connect to my SQL Server.After fetching the rows, I am using PutDatabaseRecord processor to insert the fetched rows into Azure SQL but its showing me error that "use statement is not supported to switch between databases". Note that the Database Polling Connection Service is different for PutDatabaseRecord where I have mentioned connection string of my Azure SQL.Please if anyone can give feedback on this issue and suggest any workaround.

steven-matison · Accepted Answer

@Kiranq No, you do not have to use the USE statement.  Some things to consider:Make sure each DBCP is configured correctly with the right schema or namespace if possible in the confIguration or connection string. You can also make sure the schema or namespace above your table is included in the processor configuration.   For example in PutDatabaseRecord you will find Schema Name in the properties. It could also be done in the sql statement without a specific use statement like select * from default.tablename; assuming your user has access to this default namespace with the tablname.

Kiranq · Answer

Thanks. It was an issue with the connection string. It was pointing to another database while my processor was inserting into another database.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.