Error Running SSIS Package as a Job in SQL MS 2008

HbugProject used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I finally got the package to work, however, I am not entirely sure what I did that made it work.
First of all, I changed the source connection strings in my package to use a UNC, not a drive mapping, I also stopped typing the full file name into the initial dialog, and picked it from the drop-down once the connection was established (even though the package was executing fine before).
I then changed my destination connection string to the SQL server to use Windows Authentication, and removed the jSmith SQL login.

I re-copied and re-imported the package into the package store and set my security to  'Rely on server storage and roles for access control'. I deleted and recreated my only job step, set the RunAs to my jSmith proxy, and checked the boxes for my 2 data connection strings on the DataSources tab, set to run as 32 bit, and set my action to 'quit and report success'. I have no idea which exact thing did it, but I think I had some tom-foolery with my SQL vs Windows logins.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial