I have an instance where I save an Excel file into a folder with a long file path. I then use the "Zip/Unzip" activity to zip the Excel file and, while the operation is successful, the zip file name contains every single folder in the path which makes the name too long (the file doesn't open) and exposes my directory structure. Is there a way to zip the Excel file without having its zip name contain all the folder hierarchy?
You may have to share your automation job task definitions, because I cannot reproduce. Or should say, I created a quick 2=-step automation job that does a Select-to-File (from SQL Server to create an Excel File) and that file is fed to the next step which Zips the file with a simple name. See snap below.
I'm using zip tool too, but works fine, even with long paths...
Is there any blanks in folder names ? Have you try to reach the receive folder from the button ? Have you try to quote the entire path ?
You can try to make it in two steps, start with archiving in a local short path, then use "move file" to put it in the right place...
In the "Select to File" activity, under the "Export to file" area, I have "\a\b\c#Excel_Report_Name#.xlsx" which is set automatically from the "Export options" window. Once I click on the "Export options" button, a new dialog window opens up and I'm asked for the "File Name and Path". I use the file locator (...) and pick the directory for the Excel file to be generated and then add the file name variable: "\a\b\c#Excel_Report_Name#.xlsx. In the "Zip/Unzip Files" activity, I'm able to pick the Excel variable name ("#Excel_Report_Name#") in the "Source files" field. If I log the value of #Excel_Report_Name# it shows the short version (ex: "My Report.xlsx") without any directory path attached but when the Zip utility zips the Excel file I get the name inside the zip to be "\a\b\c\My Report.xlsx" which is not what I want.
Any ideas about why my setup above (using variable names for files) produces a long filename when it's zipped? Both, the zip file and the Excel file are created Ok with their normal name and placed correctly in the specified directory. It's only that the zip file contains the Excel file which now has the file name be the entire folder path plus the file name itself: "\a\b\c\ExcelFileName.xlsx" instead of just "ExcelFileName.xlsx". As you see from the above picture, under "Source files" I used "Add File(s)" button and picked the #Excel_Report_Name#.xlsx file.
I think you might be misunderstanding the issue (which I share), or maybe I am!
If you open your "SimpleName.zip" in explorer, I think it will contain nested folders including "Shared Folders", "Dell host", "Gary_Data", and finally "Automates", and the ExportDimChannel.xlsx will be inside the "Automates" folder.
My issue is I don't want to expose the entire server's folder structure every time I send a zip file, I want purely the output file to be present in the zip.
Is this possible?
Edit: Just noting that I currently work around the issue by mapping my extracts folder to a drive letter, and saving the extract to the mapped drive root, preventing Data Point from including any nested folders in the zip.
Ah, I see. I don't believe Quest exposes an option in Toad's Zip task to mask or eliminate the folder path of the zipped file.
One alternative is to use your own zip utility, assuming there is an option to change/hide the folder path of the zipped file/s, and then call the zip utility via Toad's command line task (e.g. the "Run Program" task in the System Activities section.)
Another alternative is one similar to what you mention, which I like also... "soft-linking" or mapping your real folders to a virtual location. Thanks for sharing, jabs. This could prove useful for other readers facing the same requirement.
Have you tried checking Use relative paths in Automation settings?