Toad World® Forums

[TDP 3.8] [Automation] [Copy File Activity] Variable Behavior Problems

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:

  1. #Date# - SQL, formatted at MMDDYY
  2. #Mth# - SQL, formatted as MM MTH (e.g. 09 SEP).
  3. #Yr# - SQL, formatted as YYYY.’
  4. #RG# - String, set to B
  5. #BV# - String, set to D
  6. #Vendor# - String, set to Est
  7. #SP_Path# - String, set to \\SP_Serverr\SP_Region\SP_Site\C\Work\#Vendor#\#Yr#\#Mth#
  8. #SP_Mask# - String, set to #RG##BV##Vendor##Date#.xlsx
  9. #FS_Path# - String, set to \\FS_Server\FS_Dept\Reports\#Yr#\C\#Vendor\#Mth#
  10. #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:

TDP 3.8 Automation - Copy File w Variable Mask.png

Move File to Archive:

TDP 3.8 Automation - Move File w Variable Mask.png

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?

Hi N.B.,

I will try to simulate at my side and let you know whether it is reproducible in the latest version too and whether it is a bug or not.

Thanks for a great example too!

Martin

Sorry for later response.

I was able to reproduce at my side using TDP v3.8.

When i used TDP v4.0 and beta versions of newer versions this bug did not occur there. Could you please try in later versions?

Thanks!

Martin

TDP 4.0.1 patch was release so you might as well upgrade to the latest.

I thought 4.0.1 was still in Beta?

Is there any sort of tracker for submitted QATs as to what the status might be?

We don’t have a public tracking system. But our release notes are accurate. To find out what is in TDP 4.0.1 you can open up whatever version you have of TDP and go to Help | Release Notes. You usually need to press F5 to refresh. The current release notes list all the bug fixes. If you have a specific bug number you want to know the status of please let me know and I can look it up.