Jeffrey: Thanks for the reply and added information/explanation.
You asked why my user needs to run the job on demand. It's a fair question. Here's the situation:
In the past, when we got a data download from a State's database, we were able to either directly connect to the database to pull the data we need or we got an extract that was run nightly and SFTPed. Users did not have to be part of the data transfer process at all. The new State's model of "cooperation" is to make us manually run "reports" from the State's application. A support staff person will run the reports and save the exported reports/.csv files to our file system. The staff person then needs the SSIS job to import the data into our database. She then will need to immediately look at our "Import Errors" front-end application and deal with any issues with merging or validating the data.
This process happens once a day and needs to happen only once. While I could technically set up the job to run at say 7:00 am every morning, it would be terrible service/not user-friendly. It would require the staff person to make sure she does part one of her task every morning before 7:00. What if she's running late? Too bad for that day? That's not acceptable. If she runs the job early, then then the staff person has to wait until after 7:00 to resolve the import errors, breaking up her work-flow. Bottom line: At best, this set up would not be efficient/not nice for the staff person. At worse, a day might be missed on getting the data uploaded into our database. That's not acceptable.
The goal is to allow the user to do all steps one after another without having to wait for Agent to do anything. The user would: 1) download the data from the State's database, 2) run the SSIS to bring the data into our database, 3) process Import Errors.
re: "The problem here is going to be permissions - and the only way around those permissions is using a proxy account from the agent. You won't be able to use EXECUTE AS or let the user run it directly from the catalog. The only thing that will work is to setup an agent job with no schedule - and a stored procedure the user can execute that starts the agent job."
It's good to know that my choices are limited/that I can't actually run the job from the server. It's disappointing news, but better to know it is not possible than to keep trying to make it work.
I didn't know that I could run an agent job via a stored procedure. I will try to find an example for that.
What do you think of Martin's idea of first trying to move the data files from our file server to a local drive on the SQL Server? SSIS should be able to access files on it's own hard drive? Maybe I could set up a batch file that is stored locally on the SQL Server and which moves the data files. The SSIS project could then run the batch file first thing?
re: "If you have Kerberos setup correctly - with the appropriate SPN's configured - then the 'second' hop should work and authenticate using the user ticket generated from Windows. However - this may not work because the action of right-clicking and executing the package in SSMS may not be using the user credentials that logged into SSMS, and instead is using the context of the account running SQL Server (not the agent - and not the integration services service)."
Is this saying that there is a solution that just involves magically fixing SPNs or somethign? If I share this paragraph with my network admins, should this be enough information for them to know how to fix the problem? Or is this just explaining to me why I'm stuck and need a work-around? (Sorry for my denseness. You definitely helped me get a better grasp on the issue. I just don't have the full grasp yet.)