A Windows service that generates and sends custom reports using SQL Server and Cron Trigger expressions for scheduling.
I needed a way to periodically generate custom reports by querying a SQL Server and upload the data to a file server.
-
Clone this repository using Git or download a zipped version from GitHub.
-
Install the .Net Core 3.1 SDK if you don't have it already.
-
Open a command line and navigate to the source code folder and run:
$dotnet publish -r win-x64 -c ReleaseIf you don't like publishing the app with so many DLLs and other files in the same folder, you can try publishing as a single file, which will zip all the dependencies together in the EXE and unzip it to a temp folder at runtime (initial startup time will be slower):
$dotnet publish -r win-x64 -c Release /p:PublishSingleFile=true -
Move the generated EXE (and any other generated files) to your folder of preference. There is no installer.
-
Make sure you create the configuration file
config/JobDefinitions.json. See Configuration below. -
Install and start the service by running:
$ SelloutReportingService.exe install $ SelloutReportingService.exe start
-
Check the Windows Event Viewer for log messages from the service and any errors. You can also run the service executable from the command line and see log messages outputted directly to the console by just calling the executable with no arguments.
Create a config/JobDefinitions.json file. Follow the example config/JobDefinitions.example.json file:
[
{
"JobId": "Example Job running every 1 minute",
"CronTrigger": "0 * * ? * * *",
"MsSqlConnectionString": "Server=localhost,1433;User Id=USENAME;Password=PASSWORD;Connection Timeout=10",
"SqlQuery": "EXEC DATABASE.dbo.SomeProcedureThatReturnsOneResultSet;",
"SqlCommandTimeout": 900,
"AlertEmailAddress": "YOU@YOURDOMAIN.COM",
"ReportFileFormat": "CSV",
"ReportFilePath": "OUTPUT_FILE_PATH.csv.zip",
"IsZipped": true,
"UploadDirectives": [
{
"Host": "FTP.YOURSITE.COM",
"Port": 21,
"IsSftp": false,
"PassiveMode": true,
"UseSsl": false,
"UserName": "FTP_USERNAME",
"Password": "FTP_PASSWORD",
"FolderPath": "/in/"
},
{
"Host": "SFTP.YOURSITE.COM",
"Port": 22,
"IsSftp": true,
"UserName": "SFTP_USERNAME",
"Password": "SFTP_PASSWORD",
"FolderPath": "/in/"
}
]
}
]The example configuration file defines one job called Example Job running every 1 minute that runs once every minute,
as defined by the CronTrigger expression 0 * * ? * * *.
See Qartz's Cron Trigger Tutorial
for more information on how to define custom schedules.
The job connects to a SQL Server using the specified connection string, runs the specified query, saves the resultset to a CSV file, which can optionally be zipped, and finally uploads it to one or more FTP and/or SFTP servers.
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.