I have a situation with a client, they receive an excel file from a 3rd party as a mail attachement, this file is saved to a particular directory and then imported into SQL using SSIS. The problem occurs however that the 3rd party has an Excel version 4.0 formatting macro embedded in the file, so when the file is opened, it is formatted, and hence "changed". This change, then makes excel want to save the file when working with it in SSIS, and since this process is run as a scheduled task, the process hangs until someone manually opens and then saves the excel file in place.
The SSIS process works if the manual open/save procedure is carried out. My question is, is it possible to automate this in some way? Bearing in mind the file is coming from a 3rd party so the adding of macro's/removing the formatting macro etc. are not feasible solutions at this time.
The reason the file is "unknown" is the file name changes based on time period and data within, the SSIS package accomodates this by using a for loop to run through .xls files in the particular directory these files are stored in.
The SSIS process works if the manual open/save procedure is carried out. My question is, is it possible to automate this in some way? Bearing in mind the file is coming from a 3rd party so the adding of macro's/removing the formatting macro etc. are not feasible solutions at this time.
The reason the file is "unknown" is the file name changes based on time period and data within, the SSIS package accomodates this by using a for loop to run through .xls files in the particular directory these files are stored in.