How to import flat file data into Microsoft SQL Server using Windows PowerShell (part 2)

Home > Search > How-to

Data integrity and quality controls

Run these commands to ensure the files updated show todays date.

  • cd S:\\SBRC\\SQL\\automationjobs\\
  • Get-Item mustdump.txt | Foreach {$_.LastWriteTime}
  • Get-Item mustdump1.xlsx | Foreach {$_.LastWriteTime}
  • Get-Item failedfile.xlsx | Foreach {$_.LastWriteTime}

To ensure that the upload was successful, follow these directions.

  • Right-click the S:\\SBRC\\SQL\\automationjobs\\mustdump.txt file, and select open in Notepad++.
  • In Notepad++, press Ctrl F to bring up the Find pop-up box.
  • In the Find pop-up box, type \ into the search field, bullet Extended and click the Count button.
  • The count will be displayed at the bottom left corner of the Find pop-up box

Compare the Notepad++ count to the Total listed at this page: 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.

  • cd S:\\SBRC\\SQL\\automationjobs\\
  • DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\\mustdump_core_ftp_update.dtsx"
  • DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\ita_db_ftp_mustdump1_update.dtsx"
  • DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\\amy_failed_file.dtsx"

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.

  • In PowerShell ISE, add this line to the Scripts Pane: DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\\mustdump_core_ftp_update.dtsx". Save the file as S:\\SBRC\\SQL\\automationjobs\\mustdump_core_ftp_update.ps1.
  • In PowerShell ISE, add this line to the Scripts Pane: DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\ita_db_ftp_mustdump1_update.dtsx". Save the file as S:\\SBRC\\SQL\\automationjobs\ita_db_ftp_mustdump1_update.ps1.
  • In PowerShell ISE, add this line to the Scripts Pane: DTEXEC.EXE /F "S:\\SBRC\\SQL\\automationjobs\\amy_failed_file.dtsx". Save the file as S:\\SBRC\\SQL\\automationjobs\\amy_failed_file.ps1.

Use these commands to manually run the PowerShell scripts.

  • cd S:\\SBRC\\SQL\\automationjobs\\
  • .\\mustdump_core_ftp_update.ps1
  • .\ita_db_ftp_mustdump1_update.ps1
  • .\\amy_failed_file.ps1


Schedule a Task using Task Scheduler

  1. In Task Scheduler, in the right panel, select Create Task. The Create Task pop-up box appears.
  2. In the Create Task pop-up box, on the General Tab, give the Task a Name (ex. Daily FTP Site Update)
  3. On the General Tab, bullet Run whether user is logged on or not
  4. On the Triggers Tab, click New, and create a Daily Schedule
  5. 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.
  6. On the Actions Tab, enter this in Add arguments: .\\daily_ftp_update.ps1
  7. On the Actions Tab, enter this in Start in: C:\\Users\\A299JJC\\Documents\\
  8. 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.

Add a Comment

We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.

Please enter in the box below so that we can be sure you are a human.