Toad World® Forums

Automation: Copy File Activity


#1

Using TDA 3.1.0.771

I’m not sure if this is a bug, or working as intended - I’m hoping it’s a bug and one that can be fixed, because I would dearly love to be able to use this :slight_smile:

I’ve got a number of variables set up:

Date: Returns date in MMDDYY format - used in file name.
Month: Returns month in MM MON format - used in file path name.
Year: Returns year in YYYY formation - used in file path name.

In the “Export Script to File” activity, I can use all three of these variables in order export my file and automatically have it create a new folder when the month rolls over, and eventually the year.

However, I cannot use any of these variables in the Copy File activity (which I use to copy files to a local share and then to our SharePoint), but it seems like I should be able to - I mean, when the script is compiled the variables are evaluated, so TDA should then pass them along to every activity that calls for them, so it seems like a bug to me.

Is this by design?


#2

We do dereference variables in source and target file names of copy file activity unless those variables are dataset variables lile Var.Date, Var.Month, Var.Year. Here Var is dataset variable name and Date, Month, Year are column names of a dataset itself. Do you use this scenario? Can you please clarify or send a script example? You can send it to me directly to igor.manokhin@quest.com

Thanks,

Igor.


#3

Igor,

My variables are like this:

#Date# - SELECT TO_CHAR(SYSDATE,‘MMDDYY’) FROM DUAL;
#Mth# - SELECT TO_CHAR(SYSDATE, ‘MM’||’ '||‘MON’) FROM DUAL;
#Yr# - SELECT TO_CHAR(SYSDATE, ‘YYYY’) FROM DUAL;

Those are how they are defined in the “Set Variable” activity.

As I said, when I use them in the “Select to File” activity in the file output path, there is no issue.

However, when I attempt to use them in the “Copy File” activity, they won’t work.

If I use it in the Source file Path, I get the message “Source file does not exist” (example: \server1#Yr##Mth#\File1.txt).

If I use it in the Destination path, I get the message “Destination folder does not exist” (example: \server2#Yr##Mth#).

I understand that variables aren’t evaluated and set until the script executes, but the Copy file activity causes the script to abort before the variables are set - at least, that’s how it appears to be working.


#4

I also want to set my destination directory for copying files. I tried creating the full path with an SQL query:


select ‘\Vahernfile01\netops\National Reporting Archive\National Service Delivery\Daily Reports\OnBoarding_CSM’ || TO_CHAR(sysdate,‘yyyy’) || ‘_Q’ || TO_CHAR(sysdate,‘q’) from DUAL

And also get the error the OP has been getting. Please someone help here by giving a few examples on how to create and use directories in the automation. Thanks.


#5

I have attached a script where I have tested the Oracle scenario that NB posted. The script works for me. I also attached the log file so you can see what that looks like. If the variables are being set properly, and deferenced properly, the copy file will work. If you still cannot get it to work for either of the last two posts, please send me your automation script and log files directly. dpeabody@quest.com

Debbie
Script_FormatPaths.tas (12.7 KB)


#6

I have attached a script where I have tested the Oracle scenario that NB posted. The script works for me. I also attached the log file so you can see what that looks like. If the variables are being set properly, and deferenced properly, the copy file will work. If you still cannot get it to work for either of the last two posts, please send me your automation script and log files directly. dpeabody@quest.com

Debbie
Script_FormatPaths.log (2.38 KB)


#7

Debbie,

The issue is not the variables in the file name - as I’ve said, that works fine.

The issue is the using a variable in the path for the copy statement.


#8

Let me check my script again.

Debbie


#9

I found out we do have a bug here. This bug is only if you have a file name where you are using variables. In this case we are not prepending the full path to the file name and looking for the file locally.

I created CR103649. We will fix asap.

Sorry for my misunderstanding.

Debbie


#10

Hi Debbie,

Yes, I’ve encountered that bug as well, however, that’s not the one I’m specifically encountering here.

If I attempt to use variables in the file path (folder path), TDA will not allow it - here’s an example:

\server1#Yr##Mth#\File1.txt

Note that the variables are not in the file name - they’re in the path to the file. TDA won’t accept it as a valid path.

The fix to the bug you’ve submitted may fix this as well, but I don’t think so, as it appears to be two different behaviors.


#11

It will solve the issue. We pass a path through a function that converts and replaces all of the variables. The current bug is two fold.

  1. We do not pass the full path through that function. If we did it would replace the vars in the path AND file name.

  2. Since we are not using the path we default to using the current path.

The same fix will handle both issues. Yours is the first one.

Debbie


#12

Ah, gotcha. Any idea when it’ll be live? :smiley:

I only ask because we’re generating 500+ files a week from automated reports and it would be a real help to be able to sort them into folders by Month going forward - right now they’re all in one big folder and it takes a while to find what you’re looking for :slight_smile:


#13

We are testing a Beta build to post right now, so I will not be able to catch that one. I can have the fix in the following Beta.

Check back to see in Beta fixes in two 1/2 weeks. CR103649

Debbie


#14

Do you know how to write batch files (.bat files)? You can write an old DOS days copy command with %1, %2, %3 parameters in your batch file and have Toad’s run program call your batch file and pass in your variables on the arguments line of that function using #variable1# #variable2# #variable3#. If it is just a simple copy try using the run program to run Copy and build the from and to locations with variables in the arguments line. The old DOS Copy command can get you there until their fix comes through.


#15

Hi Debbie,

Is there a list somewhere that shows which CR’s are included in any particular release?

I looked at the release notes that Igor linked to in his post, but I just see ST#'s - if they’re the same, I’ll just keep an eye out.

Also, do you happen to know when the upgraded Excel 2010 components will be included? I can’t even go to 3.2 at the moment :frowning:


#16

CRs are the same as ST#.

I am not sure what you are referring to regrading 2010 components. Can you ellaborate? What version are you on? We have support Excel 2010 for awhile. So an upgrade would handle this as far as I know.

Debbie


#17

Ok, good to know.

It’s related to this thread here - http://tda.inside.quest.com/thread.jspa?messageID=135422&#135422 - we had to get a one-off build in order to support Excel 2010 Macros.

So far as I know, the updated 3rd party components haven’t been integrated into the live builds yet, so I’m still back on 3.1.0.771 as it’s something that must work for us.


#18

Actually that fix is in the current Beta if you would like to try. We forgot to mark the CR closed so it hasn’t shown up in the list of fixes.

Debbie


#19

#20

We are testing a Beta build to post right now, so I
will not be able to catch that one. I can have the
fix in the following Beta. Check back to
see in Beta fixes in two 1/2 weeks. CR103649
Debbie

Hi Debbie,

I’m testing the TDP 3.3.932 release from last week or so, but CR103649 wasn’t included in the release notes as being a part of this release. Can you tell me if it was included and just not marked?

I haven’t had a chance to try the copy file activity w/ variables in the path name in this release yet, as we may have run into another Excel bug and I’m trying to hunt it down now.

Message was edited by: N.B.