SSIS Help Needed

AsSiMiLaTeD

I realize it's a longshot, but I'm pulling my hair out and am desperate. What I am trying to do seems simple enough, I want to send an email with multiple attachments using SSIS, and I can't get it to work.

Here's what I'm doing:

I have a ForEach loop and in that loop I'm grabbing all the filenames in a folder and am storing them in a variable. I have two variables, one for the single filename on a given iteration of the loop and another that accumulates those single filenames, separated by a |, so that would look something like file1.txt | file2.txt etc etc.

Then I take that variable and use it in a Send Mail task as a FileAttachments expression.

I'm getting the weirdest behavior. Sometime the package will run just fine, and sometime I'll get an error saying that either the file doesn't exist or I don't have permissions to access it.

Not alot to go on, but if someone think they can help with some more info on my end I can provide whatever you need. Like I said, longshot, but worth a try.

TIA

quadzilla

It's not much, but when I run into problems like that with my scripts, I add debug statements along the lines of "here's the list of things I'm going to operate on" which then just dumps the array to a log file, and others along the lines of "this is what I'm going to do next" and dump out the full command including arguments to a logfile.

But often, on windows, that error actually means that the file is locked by another process. That may be what's happening, and why it works sometimes and not others.

Someone really should teach the Windows OS and platform coders about non-exclusive locks, because they don't seem to have heard of them. And that's why I sticks with 'NIX.

AsSiMiLaTeD

I wouldn't know where to put any debugging code, the script task in the foreach loop container runs just fine every time, it's the part where the send mail task goes to send out the email where it fails:

Error: 0xC00291CB at Send Mail Task, Send Mail Task: Either the file "Courier Upload A 20110912.xlsx" does not exist or you do not have permissions to access the file.

I've ruled out any type of network connection issue by just hosting the file on my C drive, there just has to be something that I'm missing somewhere...

ryanjoachim

If the script works sometimes, and not others, it is most likely a usage issue. It sounds like the file is randomly read-only, but whether that's from another program using it or someone else having it open, I can't say.

AsSiMiLaTeD

Well I've closed out all other programs and even rebooted and have the files on my local drive, so I know no other program or person is using the file. Whatever is causing the issue has to be happening somewhere in SSIS.

Also, when I just send the file directly using the Send Mail task and specify the file name in the attachment box instead of using a variable it works every time. So that leads me to think there's something wrong with my logic to create the variable, but the fact that it works sometimes seems to negate that...so weird.

gimpod

I think the problem might be that your not setting the current working directory to the directory that has the files your trying to send. One way around this if you can't set the current working directory is to build a full path name for each file i.e \folder\file1.txt | \folder\file2.txt | "D:\folder\file 3.txt" etc. also don't forget that if any part of each path filename has a space in it it needs to be in quotes.