Automate File transfers between local folders and SFTP site.

The intended reader for this document is the IT department/person for your district.   However, every effort was given to allow a non-technical person to complete it successfully.


Prepare environment – application and folders


Note: Depending on your district’s network security, you may need to have a windows’ administrative rights login to set this up n the local computer or Server.  The machine must be left on, but can be logged off/locked.


Here is the folder structure and script files to be created on destination machine (to which files will transfer files to/from ftp):

  • C:\"Program Files (x86)"\WinSCP – folder contains binary files of portable WinSCP application – do not create this, WinSCP installation will add this files and file path. When installing, use default settings.
  • C:\Data\Scripts – create folders that will contain batch scripts to run FTP transfer and scripts with commands for FTP
  • C:\FTP – transfer folder to which files will be transferred from/to ftp site (if you wish you can add import and export subfolders here (C:\FTP\Import and C:\FTP\Export) or use existing path were you would like to host the files locally
  • C:\Data\Scripts\DropStudentData.cmd  and C:Data\Scripts\GrabExport.cmd– batch files with command which starts WinSCP with certain parameters (details to follow)
  • C:\Data\Scripts\DropStudentDataScript.txt and C:\Data\Scripts\GrabExportScript.txt - text files which contains commands for WinSCP. It is passed to WinSCP as a parameter by the previous .cmd scripts/files (details to follow)

Download WinSCP from here (http://winscp.net/eng/download.php).  Unzip and Install. Use default settings during installation.

STEP ONE: To place student file from local folder to ftp site 


A) Create Connection profile in WinSCP. 

First, we need to define a connection profile in WinSCP. The connection profile will be used later in the script files to establish a connection with the FTP server. In order to define connection profile run winscp.exe from C:\Apps\WinSCP folder and populate WinSCP Logon window with all details about connection as per screenshot:



Click on New Site to highlight, fill appropriate fields.  Before you save, click in Advanced… dropdown



Under directories option, add directories under remote and local fields and OK to exit. (In this example, we will be getting the dropping the student.txt file located in the STUDENT folder)


Local Directory: Use newly created C:\FTP folder (or if subdirectoried, C:\FTP\Import) or full pathname of folder the SIS will be placing the Student.txt file. Click OK. 



Click the arrow next to save and select Save As……




NOTE: No spaces in the “Site name”, WinSCP dislikes spaces…

 

In Save as… window name your session – in this example PrimeroEdgeImport (the name will be used later on in the DropStudentData.txt to open connection) and make sure that Save password checkbox is ticked. We want to remember password in connection profile (WinSCP stores passwords in encrypted form). Otherwise we will have to entre clear-text password in the script files file.



Once connection profile is completed and Saved, WinSCP will show it on the list in WinSCP Login window. To make sure that all details are correct, highlight the connection you just created and click the Login button. WinSCP will attempt to establish connection using parameters just created.





Note: If connection is initiated for the first time and the FTP server has been defined as “SFTP”, you may be prompted for acceptance of certificate presented by the ftp server. In that case, a warning window will pop-up with all details of the certificate (as on screenshot below – you can see default, system-generated certificate which has been assigned to FTP site on ftp Server). Accept certificate by clicking [Yes].   WinSCP will store information about certificate in its configuration file for future use.


NOTE: It is important to establish a connection to the SFTP server at least once before transfer task should be scheduled. WinSCP will record the acceptance of the certificate and will not request it later. Also, this will allow to test all has been properly configures up to this point.




Once the certificate is accepted and other connection details are fine we should see two panels with files. One panel shows files on our computer (in specified folder, C:\FTP in this example), second panel shows files on remote FTP server folder (in this example, it is empty). That indicates that we can establish connection successfully and all parameters are entered correctly to connection profile. If not, revisit the previous steps.



B) Creating the command and script files 


NOTE: make sure the known extensions are NOT hidden and show full path in windows Folder Options.





Also, check Internet Explorer > Tools > Options > Advanced Tab to have the “Use Passive FTP” checked.



Create a new text file in the C:\Data\Scripts folder.  Change file’s extension from .txt to .cmd


DropStudentData.cmd <--- filename


Confirm the icon has changed to gears and does not look like a notepad when changing the extension from .txt to .cmd



Right click and Edit:


Add this text in the file: C:\"Program Files (x86)"\WinSCP\winscp.com /script=C:\Data\Scripts\DropStudentDataScript.txt  - And Save, Close.

Note: This last path (C:\Data\Scripts) would be the location of the batch and scripts files 



Create the second file in the same folder and name it DropStudentDataScript.txt  ß File Name (to “PUT” or drop student file to ftp site)


Add the following text to the file and save it:




It should look like this:

 

Confirm it will work by opening a CMD window and type CD C:\Data\Scripts (or whatever path the script is located) and hitting enter then type DropStudentData and hitting enter. 


It should look something like this.  Check for errors.  If there are errors, check your previous steps

 

Step TWO:  Grabbing PrimeroEdge Eligibility Export File from ftp


A) Create connection profile in WinSCP


If you do not need this, skip to Step Three - scheduling your tasks.


NOTE: Do NOT import the export file into your systems until your specialist has given you the go ahead.  The export file is NOT accurate until we have completed the one-time eligibility import from your legacy system into PrimeroEdge.


First we need to define connection profile in WinSCP. That connection profile will be used later in the script files (get -script.txt) to establish connection with the FTP server. In order to define connection profile run winscp.exe from C:\Apps\WinSCP folder and populate WinSCP Logon window with all details about connection as per screenshot




Click on Advance…

 

Under directories option, add directories under remote and local fields and OK to exit. (In this example, we will be getting the StudentExport.txt file located in the Export folder, you could have C:\FTP\Export or an already existing Path your POS or SIS grabs the file)


Local Directory: Use newly created C:\FTP folder (or if subdirectoried, C:\FTP\Export) or full pathname of folder the SIS or POS will be grabbing the Student.txt export file


Click on OK and once all details are filled. 



Then, click the arrow next to Save and Save as…

 


NOTE: No spaces in the name, WinSCP dislikes spaces…


In Save as… window, name your session – in this example PrimeroEdgeExport (the name will be used later on in the GrabExportScript.txt to open connection) and make sure that Save password checkbox is ticked. We want to remember password in connection profile (WinSCP stores passwords in encrypted form). Otherwise we will have to entre clear-text password in GrabExportScript.txt file.


Once connection profile is saved WinSCP will display in the WinSCP Login window. To make sure that all details are correct highlight connection you just created and click Login button. WinSCP will attempt to establish connection using parameters just created.



Note: If connection is initiated for the first time and FTP server has been defined as “SFTP”, you may get prompted to accept a certificate presented by the ftp server. In that case, a warning window will pop-up with all details of the certificate (as on screenshot below – you can see default, system-generated certificate which has been assigned to FTP site on ftp Server). Accept certificate by clicking Yes.   WinSCP will store information about certificate in its configuration file for future use.


NOTE: It is important to establish a connection to the SFTP server at least once before transfer task should be scheduled. WinSCP will record the acceptance of the certificate and will not request it later.




Once the certificate is accepted and other connection details are correct, we should see two panels with files. The left panel shows files on our computer (in specified folder, C:\FTP in this example), second panel shows files on remote FTP server folder (in this example, the StudentExport.txt file inside the Export Folder). That indicates that we can establish connection successfully and all parameters are entered correctly to connection profile. If not, revisit the previous steps.



As we have connection profile created and saved in WinSCP configuration we can proceed to next step in which we will create some scripts as preparation to automated transfers.


B) Prepare scripts for grabbing export file


Two text files will be added to the C:\Data\Scripts folder:

  • GrabExport.cmd – batch file responsible for starting WinSCP command with appropriate parameters
  • GrabExportScript.txt – list of commands for WinSCP to execute 

NOTE: Both files must be placed in C:\Data\Scripts folder. Source files for the file sync through SFTP is occurring from C:\FTP folder in this example. 


Create a text file and change the extension from .txt to .cmd 

Then right click and Edit

  


Add this text to the document:

C:\"Program Files (x86)"\WinSCP\winscp.com /script=C:\Data\Scripts\GrabExportScript.txt



And Save it. Then create another text file 



The name of the file should be GrabExportScript.txt (to “get” export file from ftp site) 

 

 

Add the following text to the file:




It should look like this:


 

 

Before scheduling the scripts, check to make sure it works and we get expected results.   From Command Prompt you can run it (by typing CD C:\Data\Scripts and hitting enter then type GrabExport and hitting enter) and see if everything is transferred according to the plan.

Here is example output of GrabExport.cmd command ran successfully before scheduling:

Once everything went well we are ready to create scheduled task to trigger file transfer automatically. If test connection failed repeat the previous steps.   This completes the preparation.   Next, we will schedule it to run automatically.

STEP THREE: Creating task in Task Scheduler

In order to schedule file transfer we will use Task Scheduler, which is built-in Windows tool. Task Scheduler is located in Start / All Programs / Accessories / System Tools.


Click on Create Task… in Actions panel on right.


Under the General tab name the task (DropStudentFile2ftp as in this example, or GrabEligibilityExportFromFTP if scheduling grabbing the export file), put some description which explains what this task supposed to do. Make sure that option Run whether user is logged on or not is chosen. That will allow task to run in background.

 


Next click on Triggers tab and click [New]… button. That will allow to create schedule for the task.


Set the New Trigger window in Begin the task to On a schedule. Under Settings, set it Daily to Recur every 1 days.  Schedule the drop to happen between 6:00 PM and 11:00 PM EST (after SIS drops the file in the local folder).  Please, read the note on timings following the screenshots.  Also, make sure that task is Enabled for the student.txt file drop (studentFileDrop2ftp) 

NOTE: Do NOT enable the scheduling of the eligibility export file (StudentExport.txt) until after your district has had the one-time eligibility import completed, but can be setup “Disabled” for now and switch it on when ready.  Your PrimeroEdge Specialist will let you know when to “enable” it 


When you confirm New Trigger with OK button schedule will be added to Triggers list as shown on screenshot bellow:


NOTE: The timings of the file transfers are critical.   PrimeroEdge updates the export file 7 times a day. During the night, it updates at 12:00 AM and 3:00 AM, so the student data should be “dropped” before our updates, and the export file should be “grabbed” after we update. Typically, you will “drop” the student.txt file between 6:00 PM and 11:00 PM EST.  The export file should be “grabbed” between 1:00 AM and 4:00 AM EST.  

This are the times the export file updates:

  1. 12:00 - 12:15 AM EST 
  2. 3:00 – 3:15 AM EST
  3. 6:00 - 6:15 AM EST
  4. 8:30 - 8:45 AM EST
  5. 10:00 - 10:15 AM EST
  6. 2:00 - 2:15 PM EST
  7. 6:00 - 6:15 PM EST

 

In these next steps, I am giving you a side by side look at the setup for 2 different schedules. However you would create two different scheduled tasks (one at a time) - one to drop the student file and one to grab the eligibility export file.

Now we can move to Actions tab. On this tab we will define which command should be triggered by task. Click New… to define command.

 

 

In New Action window indicate Action as Start a program. Then in Settings section in Program/script textbox enter C:\Data\Scripts\DropStudentData.cmd or GrabExport.cmd – depending which one you are creating

I am showing both at the same time, you will have to do one at the time.  (This are the batch scripts which will execute WinSCP with parameters).


Once action is defined click OK button and you should see our command added to action list as on the screenshot below:



All parameters have been defined and now you can confirm task configuration by clicking OK button. 

Windows will request credentials for that task. Please enter username and password for account we want to run this task under (preferable an admin account if you have highly restricted user accounts). In example below I just used domain account.


 

 

After credentials will be entered in Task Scheduler you can see the tasks added with all parameters defined.

Now it is only matter of time to check if task will run accordingly to the schedule we defined and if all files will be transferred correctly.