I usually receive 10 files daily and I would like to check if those 10 files exists. If any of the files missing then I would like to send an email with the missing file name. I know how to do this for one file but not sure I can do this for multiple files. I wrote the below
vb.net code for a single file. If (File.Exists(CStr(Dts.Variables("fileLoc").Value))) Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure Can someone please share an example on how I can do this for multiple files and send the missing file name? Thanks for the help
You can use the ForEach loop container to solve your problem. The ForEach loop container will iterate through each file present in the path specified. Your IF condition is perfect, just have a counter set for every Success you get and for every Failure you get, setup a small temporary table to store the list of filenames missing. If the counter = 10 at the end of the operation, you have all your files, If counter < 10, send an email for the list of missing files obtained by scanning the temporary table.
Ok not the easiest but straight forward 1. create a table which holds the file paths 2. create an ADO Object variable 3. Add an execute SQL task and query the table. Load the full result set in to the object 4. Add a foreach loop and loop over the ado object 5. Pass the output of the loop to a variable 6. Pass that variable in to your script task I do something similar in this example:
http://www.hyperbi.co.uk/dynamically-importing-excel-files-with-ssis-c/ Let me know how you get on