It looks to me as if it is from the subsystem. I don't see any events with package code in it.
I did notice that the command line for the step of my job shows the provider as being SQLOLEDB.1. I had changed it to that at one point but changed it back on suggestion from someone. If I change it in the command line I just get a very generic message saying the step failed.
It's likely trying to connect to a different server then. I'd go through the XML of the package at this point and see if you spot any QA servers or other non-prod servers in there.
Thanks, but there is no reference to another server in the XML.
I wasn't able to try changing the service account until this morning. I changed the account to use my credentials as I did on the QA machine while testing. As expected I got the same result.
So I have tried the following (the package runs in every circumstance on the QA machine):
I created proxy using my account
I changed the SQL Server Agent Service account login (for trying to run as a job)
I changed the SQL Server Service (for trying to run as a SP)
I checked all of the SQL login permissions for my account and the local system account
I ran a trace on the jobs and did not see any code being executed related to the package being run
I checked the XML of the package and there is no reference to any other server
I tried using a configuration file
I tried changing the connections string provider from SQLNCLI.1 to SQLOLEDB.1 and back again
The package is on the same machine as the database
Anyone else with any thoughts?
Try changing the data source to use servername,portnumber instead of just servername.
Once I do that I get the error in BIDS as well.
Are you sure you specified the correct port number? Check the SQL Server Error Log or SQL Server Configuration Manager for the port.
Yes - it is the correct port number. I tried it again in BIDS in case I typed it incorrectly and it worked in BIDS but I get the same error when running the job.
Can you import the package into msdb or SSISDB (if applicable) instead of running it as a file? I know how to override the connection string when the package is inside the SQL Server but not sure how to help when it's on the file system.
I actually just did since my last post. I was getting an error message when trying to expand msdb but that has been corrected and I added the package. I ran the package manually but it still doesn't work from the job.
Looks like I have the issue resolved. Although I won't believe it until I run it a few times.
I tried running the process again in BCP and after getting some errors I added the -S option with the server name and instance. It was looking for a format file so I figured I made the connection.
While I am pretty sure I already did this I went to the data sources of the job and added the instance name after the sever and it worked. It may have been a combination of things that contributed.
Thank you both so much for your help. I have learned a lot through this process.
Woohoo! Glad you got it working.