UNLOAD DATA FROM SQL SERVER AND LOAD AS A CSV FILE TO SFTP USING AZURE FUNCTIONS
In this blog, we’ll learn how to use Key Vault to connect to SQL Server and unload the data, then convert the data table to a CSV file, and upload a file to an SFTP server. The following steps guide users through doing this.
- Obtaining Key Vault credentials
- Connecting and retrieving the data from the SQL Server table
- Save the Data Table as a CSV File
- Connecting to SFTP and uploading the file
Here the code has been developed in Visual Studio code using C#, which includes an interactive debugger, so the user can step through the source code, inspect variables, view call stacks, and execute commands in the console.
STEP 1: Obtaining Key Vault credentials
Install the below packages from the NuGet package for authentication
· Microsoft.Azure.Services.AppAuthentication
· Microsoft.Azure.KeyVault
Below code helps to access the Key Vault :
static readonly AzureServiceTokenProvider tokenProvider = new AzureServiceTokenProvider();static readonly KeyVaultClient keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(tokenProvider.KeyVaultTokenCallback));
To get the secret and read it, use the code below.
· DataSource — Host name of the SQL server where to connect.
· InitialCatalog — Name of the database that is to be used.
· Set IntegratedSecurity — False.
· Give UserID and Password only when Integrated Security is false.
keyVaultClient.GetSecretAsync — Helps us to get the secret from key vault
NOTE: Before deploying, there is no mandatory to give access policy in the key vault for the function app.
string DataSource = "enter Secret Identifier of your secret";var Data_Source = keyVaultClient.GetSecretAsync(DataSource).Result;var Data_Source_secret = Data_Source.Value.ToString();string InitialCatalog = " enter Secret Identifier of your secret ";var Initial_Catalog = keyVaultClient.GetSecretAsync(InitialCatalog).Result;var Initial_Catalog_secret = Initial_Catalog.Value.ToString();string IntegratedSecurity = " enter Secret Identifier of your secret";var Integrated_Security = keyVaultClient.GetSecretAsync(IntegratedSecurity).Result;var Integrated_Security_secret = Integrated_Security.Value.ToString();string UserID = " enter Secret Identifier of your secret ";var User_ID = keyVaultClient.GetSecretAsync(UserID).Result;var User_ID_secret = User_ID.Value.ToString();string SQLPassword = " enter Secret Identifier of your secret ";var SQL_Password = keyVaultClient.GetSecretAsync(SQLPassword).Result;var SQL_Password_secret = SQL_Password.Value.ToString();
STEP 2: Connecting and retrieving the data from SQL server table
SQL SERVER
The SQL Server is a relational database management system from Microsoft. The system is designed and built to manage and store information. The system supports various business intelligence operations, analytics operations, and transaction processing.
The code below assists in connecting to a SQL server via ‘dbconnection.connect()’. Then, using ‘new DataTable(),’ a new table will be created. The data is then retrieved using the SQL adapter and stored in the ‘da’ variable, which is then populated into the table. Following that, it will invoke the ‘TOCSV’ method. ‘sftpupload’ is then called once it has been successfully completed.
using system.Data.SqlClient;using System.Data;system.Data.....SqlConnection dbConnection = new SqlConnection("Data Source=" + Data_Source_secret + ";Initial Catalog=" + Initial_Catalog_secret + ";Integrated Security=" + Integrated_Security_secret + ";User ID=" + User_ID_secret + ";Password =" + SQL_Password_secret);try
{
// connect to SQL Server
dbConnection.Open();
log.LogInformation("Connected to sqlserver");var table = new DataTable();var da = new SqlDataAdapter("select * from tablename ", dbConnection);//fill the data to the tableda.Fill(table);log.LogInformation(table.ToString());ToCSV(table, @Path.Combine(Path.GetTempPath(), "Output.csv"), log);sftpupload(log);}catch (Exception ex)
{
log.LogInformation(ex.Message);
}finally
{
dbConnection.Close();
}
STEP 3: Save the Data Table as a CSV File
Once the data was collected, the data table was transformed to a CSV file, with null values replaced with empty values, and then written to a temporary folder since SQL Server and SFTP authentication differed, making direct uploading impossible.
public static void ToCSV(this DataTable dateable, string filepath, ILogger log){
using (StreamWriter sw = new StreamWriter(filepath, false))
{
try
{
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
sw.Write(dtDataTable.Columns[i]);
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}sw.Write(sw.NewLine);
foreach (DataRow dr in dtDataTable.Rows)
{
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
string value = dr[i].ToString();
if (value.Contains(','))
{
value = String.Format("\"{0}\"", value);
sw.Write(value);
}
else
{
sw.Write(dr[i].ToString());
}
}
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
log.LogInformation("completed writing");
}catch (Exception ex)
{
log.LogInformation(ex.Message + "-- ToCSV");
}finally
{
sw.Close();
}}}
STEP 4: Connecting to SFTP and uploading the file
SFTP:
SFTP (SSH File Transfer Protocol) is a secure file transfer protocol. It runs over the SSH Protocol. It supports the full security and authentication functionality of SSH. SFTP has pretty much replaced legacy FTP as a file transfer protocol and is quickly replacing FTP/S. It provides all the functionality offered by these protocols, but more securely and more reliably, with easier configuration.
SFTP was connected using key vault credentials to open and read the file, then put the data in a variable called ‘uplfilestream’ and upload a file from the temporary folder to the SFTP destination. The filename contains the filename as well as the current timestamp.
public static void sftpupload(ILogger log){// getting credentials from keyvaultstring username = " enter Secret Identifier of your secret ";var user_name = keyVaultClient.GetSecretAsync(username).Result;var user_name_secret = user_name.Value.ToString();string password = " enter Secret Identifier of your secret ";var user_password = keyVaultClient.GetSecretAsync(password).Result;var user_password_secret = user_password.Value.ToString();string hostname = " enter Secret Identifier of your secret ";var host_name = keyVaultClient.GetSecretAsync(hostname).Result;var host_name_secret = host_name.Value.ToString();using (SftpClient sftpClient = new SftpClient(host_name_secret, 22, user_name_secret, user_password_secret)){try{log.LogInformation("File is download to temporary folder");var dt = DateTime.Now.ToString("yyyy-MM-dd-HH.mm.ss");// connect to SFTPsftpClient.Connect();log.LogInformation("Successfully connected to SFTP");using (var uplfileStream = System.IO.File.OpenRead(Path.Combine(Path.GetTempPath(), " Output.csv ")))// upload the file to SFTPsftpClient.UploadFile(uplfileStream, @"/upload/OutputSQL" + dt + ".csv", true);log.LogInformation("Successfully uploaded the file from temporary folder to SFTP");
Note: Once the file has been uploaded, the file from which we read should be closed, or else it will remain open until the user closes it. When the user runs again, it will cause a conflict.
As a result, we have unloaded the data from the SQL server and saved it as a CSV file in a temporary folder before moving it to an SFTP destination.
Finally, the data table will be converted into a CSV file when this function app runs. It can be either run manually or set any trigger for automatic run.
About Us
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.
Website : https://bi3technologies.com/
Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies
