sql - Weird SSIS Error (Process Exit Code 255)
学习资源的获取：微软TeckNet中文网络 SQL Server DTS的前生今世 1、关系型数据---->维度型数据 企业级数据管理平台框架 业务系统---》信息准备层----》信息存储
I'm having an issue with an SSIS job I recently created that is breaking with a strange error.
This is an SSIS package (.dtsx) that is being run with a SQL job (SQL Server 2008, job created with SSMS) using the type SQL Server Integration Services Package as you would expect. The job runs fine in our development environment, but not in our test environment. The package uses a configuration file (.dtsConfig) to maintain the connection strings for the different environments. What the package is actually doing is pretty simple; it truncates some staging tables, and then fills then with data pulled from an Oracle database using 8 data flow tasks in a sequence container, with each task breaking down into Source (Oracle) -> Data Conversion (for unicode strings) -> Destination (SQL Server). Because of the Oracle connection that needs to be made, the package contains the user/pass for that connection and as such has a protection level of 'EncryptSensitiveWithPassword'to maintain that. The server that this job is being run on is running Windows Server 2008 R2 SP1. I believe thats it as far as background info, but if I missed anythign let me know.
The Actual Error
This is the only useful message in the broken job's job history:
The step did not generate any output. The return value was unknown. The process exit code was 255. The step failed.
As you can see, not very helpful. The real kicker is that when I run this job manually using DTEXEC on the server, it runs fine. This leads me to believe that it may be a permissions issue with the sqlagentadmin or something, but everything that I look at seems fine. I checked out what was happening using procexplorer and it was running the correct DTEXEC command. The application event viewer does pop up with this though (so maybe its an oracle driver issue):
Faulting application name: DTExec.exe, version: 2007.100.5500.0, time stamp: 0x4e7b1ed6 Faulting module name: OraClient11.Dll, version: 184.108.40.206, time stamp: 0x4bb1dcae Exception code: 0xc0000005 Fault offset: 0x0000000000057106 Faulting process id: 0x2d94 Faulting application start time: 0x01cd66b0d360164d Faulting application path: C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe Faulting module path: c:\app\product\11.2.0\client_1\bin\OraClient11.Dll Report Id: 118a2173-d2a4-11e1-b1ec-001517955142
I've looked around the internet for hours and I've tried some things. I changed the Delay Validation property to 'True' on all data flow tasks. I've tried setting up the job with the integrated SSIS GUI, and with the operating system option to just run the dtexec, both fail.
When you install SQL Server 2012 and you try to connect to SSIS services, you cannot due to that the SSIS service not running. When you manually try t
Any suggestions? Sorry for the wall of text, but I just wanted to make sure I covered everything.
Thanks!sql sql-server sql-server-2008 ssis
this question edited Feb 26 '13 at 18:56 Perception 58.4k 8 116 147 asked Jul 23 '12 at 19:57 BloodBall 26 1 3 1 It looks like the Oracle driver that you are running is not functioning properly. The 2nd last line of your error output tells that the error is within OraClient11.Dll. Can you try running this on another machine? – rvphx Jul 23 '12 at 20:05 I assume you do not have package logging turned on? – billinkc Jul 23 '12 at 20:14 The account that runs the package in test, is that the same as the SQL Agent account or do you have a proxy? Is it set up the same, as best as you can tell, in your dev environment? – billinkc Jul 23 '12 at 20:18 4 Hey everyone, thanks for your comments. The problem was in the oracle driver as was pointed out. We had recently done some reinstalling of that driver, and it turns out that sqlagentadmin was referencing an old driver, whereas windows was referencing the correct driver (hence working manually but not with the job). It's all set now, thanks again! – BloodBall Jul 23 '12 at 22:25 1 Can you please promote the final comment into an answer and mark the question as answered? – milivojeviCH Oct 22 '12 at 9:59 | show more comment
I got the following error : ' The step did not generate any output . The return value was unknown . The process exit code was 255. The step failed. ' To solve my problem , I checked for privileges for reading the source data. I did not have the right privileges. Got the correct privileges and restarted my job solved the problem . I could suggest that the API between SSIS as the executing task and in my case DB2 data source provider should assign fault with better information : -) Problem solved by getting reading priviliged to source data.
this answer answered Jan 10 '15 at 13:24 Jens Jensen 11 1
Is your server a 64 bit server? What about the Oracle server? We had very similar issues issues when using SSIS to connect to an Oracle server. We had to install both the 64 bit and the 32 bit client tools on our 64 bit server.
In addition, there's a flag that you may have to set in the SQL Agent job to tell it not to use 64 bit. Look at the properties for the Step. Go to the Configuration tab, then click on the Advanced tab (it may be a little different in SQL 2008) and check off "32-bit runtime" then try it again. This could be why it works when debugging but doesn't work as a job.
this answer answered Oct 4 '13 at 21:06 CindyPsych 91 2
- 1SSIS处理导入数据时, 存在的更新, 不存在的插入 - zjcxc(邹建)的Blog
- 2误删除SSIS中的“Maintenance Plans”文件夹的恢复 - zjcxc(邹建)的Blog
- 3sql 2008的SSIS应用方法: 将多个一定格式的文本文件批量导入到数据库
- 4SQL2005 SSIS-在脚本组件中访问数据连接
- 5SQL2005-SSIS 使用 OLE DB Command Transformation 组件
- 2SQL Server SSIS
- 3SQL Server BI Step by Step SSIS 3 --
- 4Error after SQL Server 2012 installation: Login Failure for "SQL Server Integration Services 11.0" SSIS service
- 5SQL Server BI Step by Step SSIS 4 ---合并数据3
- 6SQL Server BI Step by Step SSIS 4 ---合并数据1