I had a bit of inspiration and so I’ve been working to try and template our basic automation reports that send files externally so that others can implement the same process and only have to change a few variables.
For the most part that hasn’t been a problem and is working well, but where I’ve encountered an issue is when we get to the final two steps of every automation - posting copies of the files sent to the SharePoint site for the internal customers, and moving the files to the archive on the fileserver.
In previous automations, both the copy file mask and the destination have been 90% hard-coded, with only a few variables - such as month, date, year - in use. We use UNC paths to access both the SharePoint and the Fileserver, and I think this is where our issue is.
In my new ‘template’ I’ve created 7 additional variables in addition to our standard 3 - the new ones are bolded:
- #Date# - SQL, formatted at MMDDYY
- #Mth# - SQL, formatted as MM MTH (e.g. 09 SEP).
- #Yr# - SQL, formatted as YYYY.’
- #RG# - String, set to B
- #BV# - String, set to D
- #Vendor# - String, set to Est
- #SP_Path# - String, set to \\SP_Serverr\SP_Region\SP_Site\C\Work\#Vendor#\#Yr#\#Mth#
- #SP_Mask# - String, set to #RG##BV##Vendor##Date#.xlsx
- #FS_Path# - String, set to \\FS_Server\FS_Dept\Reports\#Yr#\C\#Vendor\#Mth#
-
#FS_Mask# - String, set to #RG##BV##Vendor##Date#*
The way this automation is configured, the export and delivery of the file to the vendor happens before posting to the SharePoint and archiving to the Fileshare - none of that has issues and works just fine; it’s the copy file activity where I’m running into issues - this is how I have them configured:
Copy File to SharePoint:
Move File to Archive:
The Log file indicates that both the paths and the masks are being set correctly at startup:
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Workflow Compiled without Errors
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Build completed
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Create Workflow instance
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Started workflow instance
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Begin execution script activities
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Connecting to SQL Server
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “Mth” set to ‘09 SEP’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Connecting to SQL Server
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “Yr” set to 2016
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Connecting to SQL Server
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “Date” set to ‘092716’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “D_ZipFile” set to ‘(TEST) B (D) Daily C Work Files ® (Est) 092716.zip’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “RG” set to ‘B’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “BV” set to ‘D’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “Vendor” set to ‘Est’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “SP_Path” set to ‘\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “FS_Path” set to ‘\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “SP_Mask” set to ‘BDEst092716*.xlsx’
– 9/27/2016 5:21:09 PM: B (D) - Daily C Work Files ® (Est).log: Variable “FS_Mask” set to ‘BDEst092716*’
However, when the automation gets to the File Activities, something goes wrong:
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Export Finished, click (TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx to view file
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Row count variable File_1_RCOUNT set to 8886 rows
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: File to archive: C:\temp(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx: Size: 0 Bytes
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: 1 files to archive
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Start archiving
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Created archive, click (TEST) B (D) Daily C Work Files ® (Est) 092716.zip to view file.
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Variable “D_FTP_Rcode” set to ‘0’
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Condition: “#D_FTP_Rcode# = 0” evaluated to True
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Destination file name: \FS_Server\FS_Dept\Automation\Vendors\Est’\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Destination folder name: '\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: File will be copied and not moved
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Copied file C:\temp(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx to ** \FS_Server\FS_Dept\Automation\Vendors\Est’\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx**
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx to view file.
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: We would copy C:\temp*BDEst092716.xlsx to \SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP.
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Destination file name: ** \FS_Server\FS_Dept\Automation\Vendors\Est’\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx**
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Destination folder name: '\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: File will be moved
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Moved file C:\temp(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx to ** \FS_Server\FS_Dept\Automation\Vendors\Est’\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx**
– 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx to view file.
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: Destination file name: ** \FS_Server\FS_Dept\Automation\Vendors\Est’\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.zip**
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: Destination folder name: '\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: File will be moved
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: Moved file C:\temp(TEST) B (D) Daily C Work Files ® (Est) 092716.zip to \FS_Server\FS_Dept\Automation\Vendors\Est’\FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.zip
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: Copy File finished, click (TEST) B (D) Daily C Work Files ® (Est) 092716.zip to view file.
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: We would move C:\temp*BDEst092716 to \FS_Server\FS_Dept\Reports\2016\C\Est\09 SEP
– 9/27/2016 5:21:14 PM: B (D) - Daily C Work Files ® (Est).log: Done
Despite the log file indicating that the Destination Folder Name is correct (-- 9/27/2016 5:21:13 PM: B (D) - Daily C Work Files ® (Est).log:Destination folder name: ‘\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’) the file path that Toad comes up with is completely wrong (\FS_Server\FS_Dept\Automation\Vendors\Est’\SP_Server\SP_Region\SP_Site\C\Work\Est\2016\09 SEP’(TEST) B (D) Daily C Work Files ® (Est) 092716.xlsx ) - it appears as though Toad is appending the source directory of the automation script (**\FS_Server\FS_Dept\Automation\Vendors\Est **) to the destination file name, which makes no sense - no other Automation has this kind of issue, only this one where I’ve attempted to store the Destination Paths and the File Copy/Move mask in a Variable - and as you can see, Toad correctly sets those variables, and even correctly applies the mask and those paths - but it also appends it’s own directory for some reason.
Has anyone else encountered this? If so, did you find a work around?