Using Toad Data Point 4.3.0.718 (32 bit) and Toad Intelligence Central 4.3.0.126. Long story, but server we installed TIC on does not have access to the same network file share we do as developers, so TIC will have to work with local file mapping, and I am having trouble getting file path mappings to work between running it from my notebook and publishing it to TIC to run there. Note that these scripts load data files into tables (ETL processing).
Is there any way to stop publishing from forcing the uncheck of Use Relative Path to Find Files in the Settings, or to stop steps like Copy File from converting drive letters (like E:) to UNC paths (\server\share)? I’ve been trying various schemes, but do not seem to be able to have the script work from my notebook to a network file share and then publish it to TIC and have it work there as well.
Publishing Automation scripts rely on a couple of things.
-
All dependent files are embedded in the automation script.
-
All paths must be full paths and not use substitution drives(this is because substitution drives are defined on local pcs)
-
The script must be run under authentication credentials that provide privileges and access the script needs to run on the TIC server.
All of these things are needed so the script can run successfully on TIC server.
What you describe is a situation where the TIC server is on a different domain than what you develop the script on. Is that correct? i have seen this in some shops before. This is not easy to solve. i have seen IT define a user just to be used for running the automation scripts that has the correct privileges. This user can be entered as the one to run the script when published or the user can be entered into the ToadAppServer service and when you publish you use SYSTEM to run the script.
In the end, it sounds like you will need to get with your IT to work out how to get the right privileges needed for TIC to run your automation scripts.
We have a dedicated service account that is running TIC on the server, but the TIC server is on a different VLAN than the file server it needs to access for data files, so we have no way to map a drive to the file server. Good news is someone has set up a SFTP relay so we can get the data files dropped onto the server. Bad news is that no matter what I do, I cannot find a way to write an automation script on my notebook (that connects to the data files via mapped drive letter or UNC path) and then publish it to TIC on the server and have it work without modification. The main issues are spelled out in my first post - I cannot get the file paths in steps to remain as a drive letter path. if I enter E:\TestDir, it remaps to \server\share\TestDir, which will fail on the server. I’ve tried to use bind variables to set the beginning of the file path, but that doesn’t work. I’d try relative pathing but you cannot publish with that checked in Settings.
Did you try using the root_path variable in the settings? There is a set of variables designed to set when you design the script and then can be switched when moving the script to production. This should work for your scenario.
Write all of your output paths as #ROO_PATH#\filename.ext. The define the path for test and Prod. Run our script using Test variables and then just before publishing to TIC change to using Prod variables.
I misunderstood how the ROOT_PATH worked, so thought it was a dead end. Based on your helpful info, my first test with it was to do a simple Copy File with the Settings ROOT_PATH set up appropriately for my test env and the prod server. Ran it successfully set to Test env from my notebook, then set it to Prod env and published it to TIC. Remoted into the server and successfully ran the script there without modification.
Then tried to convert one of my project scripts to use this scheme and ran into a problem. I call a .bat file to move a data file from a staging folder to a processing folder, so that I can get the return code and gracefully handle the case when the file is missing. I set the Program entry of the Run Program step to #ROOT_PATH#\Dev\Control\Move_Acct_File.bat but get the error “Step_Move_File - The directory name is invalid”. If I change the Program entry to N:\Dev\Control\Move_Acct_File.bat, it runs without error. Why is the root path not properly subbing in here? Note that the automation script is in the same directory as the .bat file.
This brings up a bigger question. How do I design an Import Wizard task that can use #ROOT_PATH#? I tried changing the Directory value in the File Directory step of the wizard to use #ROOT_PATH# and it turned red and would not accept it. Hoping you are not going to tell me I have to make two copies of every import to handle the different paths and an If task to choose one over the other based on the ROOT_PATH value.
looks like the Run Activity does not support this. I entered QAT-12442 for this.
The import and export wizard only support variables in SQL not in the paths. I have not had this request before for this activity. I am not even sure we can accommodate this. I’d have to think about it.
Where is the import file coming from? is it a file that is generated during Automation? If so it should be build locally and imported.
Instead of using a Toad Variable you may want to setup and configure some Environment Variables in the OS. To test if it would work before going for your specific location try using %windir% which should point to your Windows Directory. If that works then I would move onto utilizing them like this...
http://www.dowdandassociates.com/blog/content/howto-set-an-environment-variable-in-windows-command-line-and-registry/
John Mc Graw
Data and Reporting Specialist
P 303-866-6974
1575 Sherman Street, Denver CO 80203
john.mcgraw@state.co.us I www.colorado.gov/cdhs
On Mon, Feb 19, 2018 at 9:38 PM, Debbie Peabody bounce-Debbie_Peabody@toadworld.com wrote:
RE: File path issue with publishing automation scripts to Intelligence Central
Reply by Debbie Peabody
The import and export wizard only support variables in SQL not in the paths. I have not had this request before for this activity. I am not even sure we can accommodate this. I'd have to think about it.
Where is the import file coming from? is it a file that is generated during Automation? If so it should be build locally and imported.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
When I use %windir% in the Import Wizard to identify the file I wish to import, the file dialog immediately translates the variable into a path, and I see no way to go back after the fact and change it to use the variable. Afraid that won’t solve my import issue.
Import file is generated elsewhere and deposited on the file server we use via a SFTP process, And unfortunately, I cannot connect to the source system to pull the data myself, so I have to use the data file.
Sounds like I am going to have to maintain two versions of my scripts and imports for now, one for test and one for prod.
Thanks for your help.
Assuming QAT-12442 leads to a change in TDP, any rough idea when we might see that in a new release? I’m guessing we’re too late for TDP 4.5?
So you wouldn't have to do two complete scripts...could you use the IF function to determine which system you're on and run the different imports as needed? I don't see a variable for Environment, but there is one for Root Path that I guess you could use as a proxy.
John Mc Graw
Data and Reporting Specialist
P 303-866-6974
1575 Sherman Street, Denver CO 80203
john.mcgraw@state.co.us I www.colorado.gov/cdhs
On Tue, Feb 20, 2018 at 8:29 AM, hharwood bounce-hharwood@toadworld.com wrote:
RE: File path issue with publishing automation scripts to Intelligence Central
Reply by hharwood
Import file is generated elsewhere and deposited on the file server we use via a SFTP process, And unfortunately, I cannot connect to the source system to pull the data myself, so I have to use the data file.
Sounds like I am going to have to maintain two versions of my scripts and imports for now, one for test and one for prod.
Thanks for your help.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
After you saved your import template did you try and edit the XML to use %windir% by chance?
John Mc Graw
Data and Reporting Specialist
P 303-866-6974
1575 Sherman Street, Denver CO 80203
john.mcgraw@state.co.us I www.colorado.gov/cdhs
On Tue, Feb 20, 2018 at 8:34 AM, hharwood bounce-hharwood@toadworld.com wrote:
RE: File path issue with publishing automation scripts to Intelligence Central
Reply by hharwood
Assuming QAT-12442 leads to a change in TDP, any rough idea when we might see that in a new release? I'm guessing we're too late for TDP 4.5?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
Had not looked at the actual .tim files before, did not realize they were XML. But I already had the thought you mentioned previously, to use additional If logic branches to handle the two file path scenarios, and have already started modifying my scripts.
Ok, found another issue. Trying to check to see if ROOT_PATH is set to my test path (N:\TESTDIR) or production path (E:\TESTDIR). Here is what help says to do:
How to Use a String Variable in a Comparison Expression
When comparing a string variable to a string value in an expression, always surround the variable name and the string value with single quotes, for example:
‘#myvar#’ = ‘Dog’
So I created my If branch tests as ‘#ROOT_PATH#’ = ‘N:\TESTDIR’ and ‘#ROOT_PATH#’ = ‘E:\TESTDIR’
When I run my script, it errors on the test with these messages:
Are_we_in_Test_or_Prod_1 - Make sure variables are additionally enclosed in single quotes if variables represent character value (’#variable#’)
mismatched input ‘’ expecting ‘(’ line 0:-1
Try setting a variable for #ProdDir# and #TestDir# and comparing variable to variable. They made me do that for a date a while back and it helped resolve a lot of things.
John Mc Graw
Data and Reporting Specialist
P 303-866-6974
1575 Sherman Street, Denver CO 80203
john.mcgraw@state.co.us I www.colorado.gov/cdhs
On Tue, Feb 20, 2018 at 2:07 PM, hharwood bounce-hharwood@toadworld.com wrote:
RE: File path issue with publishing automation scripts to Intelligence Central
Reply by hharwood
Ok, found another issue. Trying to check to see if ROOT_PATH is set to my test path (N:\TESTDIR) or production path (E:\TESTDIR). Here is what help says to do:
How to Use a String Variable in a Comparison Expression
When comparing a string variable to a string value in an expression, always surround the variable name and the string value with single quotes, for example:
'#myvar#' = 'Dog'
So I created my If branch tests as '#ROOT_PATH#' = 'N:\TESTDIR' and '#ROOT_PATH#' = 'E:\TESTDIR'
When I run my script, it errors on the test with these messages:
Are_we_in_Test_or_Prod_1 - Make sure variables are additionally enclosed in single quotes if variables represent character value ('#variable#')
mismatched input '' expecting '(' line 0:-1
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
I can try to schedule QAT-12442 for TDP 4.5. It should fit in.
Thanks, Debbie.
John, thanks for the suggestion, but still could not get the comparison to work. Tried both with single quotes around the variables and without, still get the error I reported earlier in this thread. Even tried building the comparison using the Expression builder.
I also tried to use the #TestPath# and #ProdPath# to populate the root path in the Settings task so that I only define it in one place, but the Settings task does not seem to recognize a variable in those text boxes. Debbie, a suggestion is to make it possible to find out what is selected in the Settings task Selected Environment drop down later in the script (a variable like ROOT_PATH? SELECTED_ENV?).
Anyone have any idea how to get this string comparison to work? If I cannot get this to work I cannot use the If branching.
hi hharwood,
For the error “mismatched input ‘’ expecting ‘(’ line 0:-1” you’ve received - could you try escaping the backslashes? i.e. Instead of ‘N:\TESTDIR’ write ‘N:\TESTDIR’
Martin
