How to download Excel files selectively?
I have an SSIS package that needs to search in two different types of excel files, type A and type B, and load the data internally into two different staging tables, tableA and tableB. The formats of these excel sheets are different and correspond to their respective tables.
I thought about it for simple simplicity (for folder paths, file types of type A.xls and typeB.xls can be configured in two different folders). The required excel files will be placed here via another application or manually.
I want my dtsx package to go through the folder and select the last unprocessed file and download it, ignoring the others, and then the postfix filename with "-loaded" (typeAxxxxxx-loaded.xls). "Downloaded" in the filename is how I plan to distinguish between files that have already been downloaded and those that have not yet been downloaded.
I need an advice:
a) How to check this configured folder for the latest file i.e. without '-loaded' in the filename and upload it? .. and then after uploading it, rename the same file in that configured folder marked with '-loaded'.
b) Is this the best approach to this, or is there a better way?
Thank.
source to share
You can do it this way, but it might require some complex string expressions.
eg. create a ForEach loop over the .xls files, inside the loop add an empty script task and then a data stream to load that file. Concatenate them with a priority constraint and make it conditional: the priority constraint expression will check to see if the filename does not end with -loaded.xls. You can either do this in a script task, or just use an SSIS expression while constraining the priority. Finally, add a file system task to rename the file. You may need to create a new filename with a different expression.
It might be easier to create two folders: Inbox for new unprocessed files and Downloaded for files that you processed, and just move the .xls to that folder after processing without renaming. This will avoid the first conditional expression (and the dummy script task) and simplify the configuration of the filesystem task.
source to share