Step 1 - Run SQL Script to create table to hold file configuration values ( eg. file name, path)
Step 2 - Create a SQL Agent job with a step of type Powershell and copy and paste the powershell script into the step
Step 1 - Run SQL Script to create table to hold file configuration values ( eg. file name, path)
Step 2 - Create a SQL Agent job with a step of type Powershell and copy and paste the powershell script into the step
*********** SQL SCRIPT TO CREATE TABLE TO HOLD FILE CONFIGURATION *************** SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.ltConfigFile( ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY, ProcessName varchar(100) NOT NULL, FilePath varchar(500) NOT NULL, FileName varchar(500) NOT NULL, FileNameExtension varchar(10) NULL, ArchivePath varchar(500) NULL, HeaderRow1 varchar(max) NULL, HeaderRow2 varchar(max) NULL ) ON [PRIMARY] GO INSERT ltConfigFile VALUES ( 'ETL_TEST', 'C:\temp\', '<<YYYY_MM_DD>>_TestCSVFile', 'csv', 'C:\temp\Completed\', 'This is a test csv file', 'Column1, Column2, Column3' ) GO SET ANSI_PADDING OFF GO ********************************************************************************* *************** POWERSHELL SCRIPT TO CREATE CSV FILE **************************** $processName = "ETL_TEST" $dbName = "[ENTER DB NAME HERE]" # Get current date [datetime] $date = Get-Date # Get file configuration $config = Invoke-SqlCmd -database $dbName -Query "SELECT * FROM dbo.ltConfigFile WHERE ProcessName = '$processName' " $filePath = $config.FilePath $fileName = $config.FileName -replace "<<YYYY_MM_DD>>", $date.ToString("yyyy_MM_dd") $fileNameExtension = $config.FileNameExtension $headerRow1 = $config.HeaderRow1 $headerRow2 = $config.HeaderRow2 $path = $filePath + $fileName + "." + $fileNameExtension # Create CSV file and append header rows $fso = new-object -comobject scripting.filesystemobject $file = $fso.CreateTextFile($path,$true) $file.WriteLine($headerRow1) $file.WriteLine($headerRow2) # Call stored procedure and append rows to CSV file $sql = "EXEC [ENTER STORED PROC NAME HERE] " Invoke-SqlCmd -database $dbName -Query $sql | ForEach-Object { $file.WriteLine( $_[0].ToString() + "," + $_[1].ToString() + "," + $_[2].ToString() ) } $file.close() *********************************************************************************