Data integrity and quality controls
Run these commands to ensure the files updated show todays date.
To ensure that the upload was successful, follow these directions.
Compare the Notepad++ count to the Total listed at this page: http://isccresources.pb.com/sbrc/pbsmart/reporting_dump_compare.php. The Total Count should always be higher than the Notepad++ count. The reason the Total Count should alwasy be higher is because the Total Count is the combination of the three uploaded files, whereas the Notepad++ count only counts the mustdump.txt file.
Run the SSIS files using PowerShell to update the database
Paste these commands into PowerShell.
If the SSIS FTP_job file is successfully processed, cmd.exe or PowerShell should give a success message.
The default location of the DTEXEC.EXE file is %systemroot%/Program Files/Microsoft SQL Server/100/DTS/Binn. This location can change when SQL Server is upgraded. There also can be challenges with the spaces in this directory. So, I have copied the Binn directory to S:\\SBRC\\SQL\\automationjobs\\Binn. I have also placed a copy of the SSIS FTP_job file & rita_db_pending_sap_orders file in the S:\\SBRC\\SQL\\automationjobs\\Binn directory. Run this command in cmd.exe or PowerShell.
Create a PowerShell Script so that the jobs can be run automatically using Windows Task Scheduler
First, PowerShells script execution policy needs to be configured to allow the running of scripts. Run this command in PowerShell: set-executionpolicy remotesigned.
Next, create the PowerShell script files.
Use these commands to manually run the PowerShell scripts.
Schedule a Task using Task Scheduler
- In Task Scheduler, in the right panel, select Create Task. The Create Task pop-up box appears.
- In the Create Task pop-up box, on the General Tab, give the Task a Name (ex. Daily FTP Site Update)
- On the General Tab, bullet Run whether user is logged on or not
- On the Triggers Tab, click New, and create a Daily Schedule
- On the Actions Tab, click New and click the Browse button. Go to this directory: C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\ and select the powershell.exe file.
- On the Actions Tab, enter this in Add arguments: .\\daily_ftp_update.ps1
- On the Actions Tab, enter this in Start in: C:\\Users\\A299JJC\\Documents\\
- On the Conditions Tab, remove the check-mark from Stop if the computer switches to battery power.
To verify that the task run, in Task Schedule, select the task, and click the History tab.