I feel like I am banging my head against a wall, hopefully someone here can point out what I am doing wrong.
First, an explanation of my development environment.
I RDP into our 64 bit SQL server as my user name e.g. Jsmith.
Jsmith is a Windows administrator account. During my RDP session, I run both BIDS and SQL Server MS as Jsmith.
I developed my packages in BIDS as Jsmith, using connection strings to my OLEDB VFP data source. The package is set with 'Run64BitRuntime' as FALSE, due to the VFPOLEDB provider. The package runs in BIDS successfully. I copied the package to the package store via the File menu, and set my protection level (during the copy) to 'Rely on server storage and roles for access control'.
Upon opening SQL Server MS, I login with my Jsmith SQL authentication account. This account I have set with the following server roles: (everything checked, overkill). I have set a user mapping to my own DB that I wish to import to, as well as msdb. For the MSDB, I have set the following roles: db_ssisadmin, db_ssisoperator, public, SQLAgentOperatorROle, SQLAgentReaderRole and SQLAgentUserRole.
My package is available and will run perfectly in the Integration Services node. To double-check I re-imported the package and made sure to check 'Rely on server storage and roles for access control'.
I created a job with one step - Run SSIS package. I set the package source as the package store, and picked my package from the drop-down. Under execution options, I picked 'Use 32 bit runtime' since I am using the 32 bit OLEDB VFP provider in my package. I set the on success action to 'Quit the job reporting success'.
I have tried several ways to run this package. At first, I used the 'RunAs' to SQL Server Agent Account'. I got a generic error 'Step failed'. No other details.
Not having any success, I created a proxy called SSISProxy, and set it to Jsmith credentials, and have the job execute as SSISProxy. I still get that same generic error. I have made so many security changes at this point that I have no idea where to turn, or which thing is messing me up.
Something really confusing to me is the presence of both 'Jsmith' and servername\Jsmith in my SQL user login lists. After setting my proxy, the job details list the job running as 'servername\Jsmith'. As an experiment, I modified the servername\jsmith account to include the relevant roles for MSDB. I still get the same generic error.
I have battled this for days, can someone please shed some light on the mistake I am making?