Azure Data Storage pipeline to purge SQL table -
i wondering if can create azure pipeline , have run proc purges azure sql table.
i still new concept of data factories , see not, data factories have pipelines copy on data either blob azure sql/on premise sql or other way around.
i trying write data factory pipeline purge old records in azure sql db , hoping if can point me in right direction. can still use azure data factory ?
my suggestion use azure automation instead of adf schedule stored procedure execution. find examples here , here. below code need implement on azure automation:
workflow name-of-your-workflow { write-output "job start before inlinescript" inlinescript { write-output "job start" # create connection master db $masterdatabaseconnection = new-object system.data.sqlclient.sqlconnection $masterdatabaseconnection.connectionstring = "data source=your-database-server-name.database.windows.net;initial catalog=your-database-name;integrated security=false;user id=your-database-username;password=your-database-password;connect timeout=60;encrypt=false;trustservercertificate=false" $masterdatabaseconnection.open() write-output "connection open" # create command $masterdatabasecommand = new-object system.data.sqlclient.sqlcommand $masterdatabasecommand.connection = $masterdatabaseconnection $masterdatabasecommand.commandtext = "your-procedure-name" write-output "database command text assigned" # execute query $masterdatabasecommand.executenonquery() write-output "executing query" # close connection master db $masterdatabaseconnection.close() write-output "connection closed" } write-output "work end - after inlinescript" }
to learn more azure automation, please click here.
hope helps.
Comments
Post a Comment