ITKeyword,专注技术干货聚合推荐

注册 | 登录

sql - Weird SSIS Error (Process Exit Code 255)

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:SQL Server SSIS

学习资源的获取:微软TeckNet中文网络 SQL Server DTS的前生今世 1、关系型数据---->维度型数据 企业级数据管理平台框架 业务系统---》信息准备层----》信息存储

I'm having an issue with an SSIS job I recently created that is breaking with a strange error.

Background Info

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: 11.2.0.1, 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.

推荐:Error after SQL Server 2012 installation: Login Failure for "SQL Server Integration Services 11.0" SSIS service

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

2 Answers
2

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

 | 

推荐:SQL Server BI Step by Step SSIS 3 --

    上一次我们已经介绍了简单的数据导出和导入,但是只是对单一文件进行操作,如果我们想同时对一个目录下面的所有的文件执行数据导入怎么实现呢相当简单,SSIS在


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。