I am running SQL Server 2008 R2 Enterprise 64-bit. I have a SQL Job Setup to run an SSIS package. The package is File System based. The Service Account I'm using for the agent has Local Admin to the server and 'sa' on the SQL Server.
The SSIS package is running a C#.NET, 2010, console application that downloads an Excel Spreadsheet from a site and imports it into a SQL Server.
The SSIS package runs fine if I run it manually. It does not run from the job and gives me the Process Exit Code 255 error. When I run it from a SQL query window as xp_cmdshell it also gives me the Process Exit Code 255 error. When I run it from a CMD line I get an error though it doesn't specify any error code.
it seems to me a permission issue. please go through the following thread which helps you to figure out your problem:
That was my first thought and I have spent the last 2 weeks trying everything that has to do with permissions to figure it out. It isn't permissions at least in the way that any of these imply. As I stated above, 'Local Admin' and 'sa' rights have been granted.
BTW, both of those articles are not valid.
1. I have to run mine as a scheduled task.
2. Creating an application to run an SSIS package that runs an application is out of the question.
I think the issue is with protection level setting of your package. Read the following tips and check your package deployment process -
- During development, leave the protection level of packages set to the default value, EncryptSensitiveWithUserKey. This setting helps ensure that only the developer sees sensitive values in the package. Or, you can consider using EncryptAllWithUserKey, or DontSaveSensitive.
- When it is time to deploy the packages, you have to change the protection level to one that does not depend on the developer's user key. Therefore you typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team in the production environment.
- After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. Or, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey or EncryptAllWithUserKey, and using the local credentials of the account that will run the packages.
See http://msdn.microsoft.com/en-us/library/ms141747.aspx for more information about setting protection level of package.
It was not the protection level. I tried this and it did nothing.
So, I went through and tried the '/x86' switch and the 'Run as 32-bit' checkbox on the job. Neither has worked. No matter how I run it I get this:
Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
The odd part is I'm running this as 32-bit so why am I getting a 64-bit error?
Try to modify the command line with explicitly specifying the x86 version of dtexec used in the execution. To do it, go to the properties configuration windows of package job, click Command line tab and select "Edit the command line manually", then edit it like this -
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /FILE ".......\yourpackage.dtsx" /CHECKPOINTING OFF /REPORTING E /X86
The above command was written manually by me without any test. But it would be something like that. Please give it a try and let us know the result.
Besides, i would still suggest try the "Run as 32-bit" option which should work for such kind of issue instead of to amend the command line in SQL Server 2008 R2 environment.
That did the trick to bypass the Excel Error. I'll have to remember this going forward.
I actually had 2 things going on, when I ran the job under the "32-bit" flag it would just spin. This was due to the C# console application. It uses IE to get to the internet and the site, because it is on a server, wasn't allowed. Running IE as the Service Account I was able to add the sites to the Trusted Sites. This allowed the "32-bit" flagged SQL Job to run which in turn bypassed the Excel Error.