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.