Category: Uncategorized

Upload files and folder into Azure Blob Storage using Powershell

Recently, I have had to automate a process to generate csv files and upload them to a certain folder in Azure blob storage. Here is the powershell code that does just that. After some research online, I have put together couple of solutions into one that worked well for me.

In order for this to work, you will need to install Azure powershell module on your machine. This solution assumes you are authenticating using account name and storage account key

#Upload csv files to Azure Blob Storage
$ErrorActionPreference = "Stop"

$acct = "storage-account-name" #Storage Account Name
$key = "storage-account-key" #Storage Account Key
$ContainerName = "container-name" #Container Name
$containerdirectory = "subfolder path within the container"
$localfilepath = "local-file-directory"

#create a context for communicating with azure storage
$ctx = New-AzStorageContext -StorageAccountName $acct -StorageAccountKey $key -Protocol Https
$container = Get-AzStorageContainer -Name $ContainerName -Context $ctx

$container.CloudBlobContainer.Uri.AbsoluteUri

if ($container) {
#use Set-AzStorageBlobContent to upload file
    $filesToUpload = Get-Childitem -Path $localfilepath -Filter "*.csv" 

        ForEach ($x in $filesToUpload) { 
            $targetPath = $containerdirectory+($x.fullname.Substring($localfilepath.Length)).Replace("\", "/")

            Write-Verbose "Uploading $("\" + $x.fullname.Substring($localfilepath.Length)) to $($container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $targetPath)"
            Set-AzStorageBlobContent -File $x.fullname -Container $container.Name -Blob $targetPath -Context $ctx -Force:$Force | Out-Null
            }
        }

Load excel files into a table on SQL server database

$File = "\\NFS01\SQLBackup\DATA_SQL.xlsx"
$Instance = "SQLSERVER01"
$Database = "dbname01"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = "table_name"

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -Truncate -Table $tablename

}

Update an existing database mail account using T-SQL

EXEC msdb..sysmail_update_account_sp @account_name = 'SMTP_database_mail',            -- sysname
                                     @email_address = N'altersemail@example.com',            -- nvarchar(128)
                                     @display_name = N'Database Mail',             -- nvarchar(128)
                                     @replyto_address = N'altersemail@example.com',          -- nvarchar(128)
                                     @description = N'Primary database alerts profile',              -- nvarchar(256)
                                     @mailserver_name = 'smtp.office365.com',         -- sysname
                                     @port = 587,                       -- int
                                     @username = 'altersemail@example.com',                -- sysname
                                     @password = 'SecurePassword',                -- sysname
                                     @use_default_credentials = 0, -- bit
                                     @enable_ssl = 1

List Redshift tables, views and their owners

SELECT n.nspname AS schema_name
 , pg_get_userbyid(c.relowner) AS table_owner
 , c.relname AS table_name
 , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END 
   AS table_type
 , d.description AS table_description
 FROM pg_class As c
 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
 LEFT JOIN pg_description As d 
      ON (d.objoid = c.oid AND d.objsubid = 0)
 WHERE c.relkind IN('r', 'v') 
ORDER BY n.nspname, c.relname ;