This article will go through steps to Execute SQL command or Stored Procedure and Export Data to CSV file. These Steps can be automated using SQL Server Agent Job.
Follow link to see SQL Server Agent Job creation Steps
Installing AWS CLI
Step 1:
- Create new SQL Server Agent Job
- Add First Step to Delete old files from Export Location
EXEC master..xp_cmdshell 'del C:\Users\Public\DataExports\Product_*.csv'Step 2:
Add 2nd Step to SQL Agent Job that will Run Query or Stored Procedure and Export Data to CSV in a local Folder
DECLARE @FileName NVARCHAR(100)
DECLARE @RepDate NVARCHAR(10)
SELECT @RepDate = CONVERT(varchar(8), GETDATE() ,112)
SELECT
@FileName = 'Product_' + @RepDate + '.csv'
PRINT @FileName
DECLARE @SQL nvarchar(800)
SELECT
@SQL = 'bcp "EXEC [Sales]..[Product]" queryout "C:\Users\Public\DataExports\' + @FileName + '" -S ' + @@SERVERNAME + ' -T -c -C 65001 -t"|"'
EXEC master..xp_cmdshell @SQLStep 3:
Add 3rd Step in SQL Agent Job to Push data to AWS S3 Bucket. BAT file referenced in below command needs to be placed on SQL Server. This BAT File will have S3 Target Location and Profile Information.
Job Type: Operating System (Cmd Exec)
cmd.exe /c "C:\Users\Public\DataExports\S3AWSCLI-Product.bat > C:\Users\Public\DataExports\log_Product.txt"BAT File Content (S3AWSCLI-Product.bat)
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
aws s3 cp C:\Users\Public\DataExports\Product_%CUR_YYYY%%CUR_MM%%CUR_DD%.csv s3://s3-folder/sales/ --profile S3AWS --acl bucket-owner-full-control Create AWS Profile
Other Useful Links
Call API End Point from SQL Server Stored Procedure – Simplyfies
Database Interview Question and Answers (Part-1) – Simplyfies
Simplyfies 