I am fairly new to SSIS but using this example I have set up a package that loops through files on an ftp site, adds a few derived columns and imports the data from each file into an intermediate sql table, then archives the file.
The source files on the ftp site are csv, and the site is hosted internally, so I'm able to specify the path in the file enumerator like \\servername\ftp\clientname\folderwherefiles are.
I first set this up on my local machine and it ran correctly every time, getting all the files. Then I imported it to the production sql server and when I try to run it from the packages area (not a job or a proc) it starts to run, but then gives me an error it can't find the file. I'm guessing it must be something to do with rights??
Also now when I go back to my local copy, since shutting down vs and reopening it, the package no longer works correctly, yet I swear I haven't changed anything. It highlights the for each loop and the load data in red and output has, "processing of ....csv started" then says "can not open the data file ..". It's got the correct name of the file in the output window, which it wouldn't have unless it DID find the file since this name is being stored dynamically in a variable, so I really don't know what the problem might be. Pathing? Rights?
I'm the only one here really digging into SSIS yet, so any help would be appreciated.