Automate Access database to SQL server data load

Hi Pratim. Sure, I can explain how to push Access DB data in CSV format to Blob storage and how to automate the process.

1. Export Access DB data to a CSV file: To automate the process of exporting data from Access to a CSV file, what I would do if you was you, is to use a scripting language like PowerShell or Python what you prefer is this would be easy and fast. I think roughly the script would look something like this: # set up the connection string to the Access database $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\access\database.accdb;Persist Security Info=False;" # create a connection to the database $conn = New-Object System.Data.OleDb.OleDbConnection($connectionString) $conn.Open() # create a query to select the data you want to export $query = "SELECT * FROM your_table;" # create a command object to execute the query $cmd = New-Object System.Data.OleDb.OleDbCommand($query, $conn) # create a data adapter to retrieve the results of the query $da = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) # create a dataset to hold the results $ds = New-Object System.Data.DataSet # fill the dataset with the results of the query $da.Fill($ds) # export the dataset to a CSV file $ds.Tables[0] | Export-Csv -Path C:\path\to\your\exported\file.csv -NoTypeInformation # close the connection to the database $conn.Close()
2. Upload the CSV file to Blob storage: To automate the process of uploading a CSV file to Blob storage, you can use the Azure PowerShell module or the Azure CLI. I would prefer PowerShell for this task # set up the connection string to your Blob storage account. $connect_str = "DefaultEndpointsProtocol=https;AccountName=<your_account_name>;AccountKey=<your_account_key>;EndpointSuffix=core.windows.net" # set up the container and file names $container_name = "<your_container_name>" $file_name = "<your_file_name>.csv" # create a BlobServiceClient object to interact with your Blob storage account $blob_service_client = New-Object Microsoft.Azure.Storage.Blob.BlobServiceClient($connect_str) # create a container client to interact with the container $container_client = $blob_service_client.GetContainerReference($container_name) $container_client.CreateIfNotExists() # create a BlobClient object to upload the file to Blob storage $blob_client = $container_client.GetBlockBlobReference($file_name) # upload the file to Blob storage $blob_client.UploadFromFile("path/to/your/exported/file.csv") You can save this script as a .ps1 file and run it using Windows Task Scheduler or any other automation tool. Note that in both cases, don't forget you will need to replace the placeholders in the code with your own database and storage account information. I hope this helps? If you have any queries let me know?