File exists for multiple specific filenames in folder

Question

Post reply

File exists for multiple specific filenames in folder

UconnDBA

Default port

Points: 1439
More actions
November 27, 2013 at 10:04 am

#283028

Hi.
In a SSIS package I need to first check that all files exist that I need before I start processing them (truncate destination table then bulkinsert file into table). If all files do not exist I dont want to process any of them. So in the folder there may be 30 files and i need to know if say 10 exist. For example, if in the folder the following files exist...
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt
i want to know thats say file1.txt, file3.txt, file4.txt, file7.txt exists. so i cant just use a pattern like the foreachloop wants. i need to specify the file names and iterate through and if a file doesnt exist, sendemail that it doesnt exist. after checking all files, if any didnt exist fail the job.
Any ideas on how I can do this? without making a script and email for every file. would like to loop through these file names. thought about using a object variable but not sure how to hard code the file names into that object variable.
Thanks.
John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246978 More actions · Answer 1

I can't think of a neat and tidy way of doing this without scripting. But with a script task, not difficult at all.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

UconnDBA Default port Points: 1439 More actions · Answer 2

Thanks for the reply. I was getting that idea. Was driving myself crazy trying to find a way to make it work, thinking there should be a way.

Do you have any script examples or a link to an example? I have over 10 files to check for and only thinking I can do a nested if, but that if statement will get very large. I am doing this in SSIS 2005.

Thanks.

John

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

Here's some rough code - might get you started.

Public Sub Main()

Dim ErrorMsg as string = ""

If not (System.IO.File.Exists("C:\FileData\file1.txt")) Then ErrorMsg = ErrorMsg & "File1 missing. "

If not (System.IO.File.Exists("C:\FileData\file2.txt")) Then ErrorMsg = ErrorMsg & "File2 missing. "

If not (System.IO.File.Exists("C:\FileData\file3.txt")) Then ErrorMsg = ErrorMsg & "File3 missing. "

'etc etc

'and finally

If ErrorMsg <> ""

'We have one or more missing files

Dts.TaskResult = ScriptResults.Failure

else

Dts.TaskResult = ScriptResults.Success

end if

End Sub

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

messages4kanthi SSC Veteran Points: 262 More actions · Answer 4

You may maintain the list of files in a table, read them into a ADO records set. In the For each control load the name of file into a variable, build a file connection using the same variable.

In the error event handling , you can use the send mail task; notifying the absence of file.

UconnDBA Default port Points: 1439 More actions · Answer 5

Thanks for the replies! Going to try Phil's script. Cleaner than the one I have (easier to look at). Had kind of thought about a table before, but want to keep things as simple as possible so that any of our sysadmins (non-dba's) can look at a SSIS package and have a shot at understanding it. By having less outside dependancies think it will be easier to understand.

John