SSIS package does not execute properly as a job

Hi
I have created an SSIS package that retrieves data from an Oracle server.
This is done with a "Data Flow Task" that has  a OLE DB Source (Oracle) and a OLE DB Destination (SQL Server)

The OLE DB Source properties is set to :
ValidateExternalMetadata = False
I also tried AlwaysUseDefaultCodePage = True
The SSIS project executes without problems from within SQL Server Business Intelligence Development Studio but when it is executed as an SQL Job i hangs.
It looks like it stops during validation from Oracle:

OnInformation,EZONE-SQL01,EZONE\sqlservice,Package,{7B6F9423-2126-4B55-B3A3-EDC9EE796764},{FBBE5346-A10F-4469-A300-D0895179DB07},16.04.2009 15:15:53,16.04.2009 15:15:53,1074016266,0x,Validation phase is beginning.

OnProgress,EZONE-SQL01,EZONE\sqlservice,Get data from Oracle,{54FE4CD4-3B9E-44DD-87E6-98CD80D38822},{FBBE5346-A10F-4469-A300-D0895179DB07},16.04.2009 15:15:53,16.04.2009 15:15:53,0,0x,Validating
User:Diagnostic,EZONE-SQL01,EZONE\sqlservice,fofo.ext_edocs,{F833C8BE-5035-4544-8740-029579E94909},{FBBE5346-A10F-4469-A300-D0895179DB07},16.04.2009 15:15:53,16.04.2009 15:15:53,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.


After this nothing happens ....

I need help please !

Peter
ContestoasAsked:
Who is Participating?
 
PedroCGDConnect With a Mentor Commented:
the user that is running the package from job is not the same that execute inside BIDS...
Check permissions.
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
ContestoasAuthor Commented:
But I have several other steps that executes fine.
I have one SQL task that deletes all rows in a temp table before the Data Flow Task and  that works fine.
If I disable the Data Flow Task that retrieves data from Oracle then the package executes without problems. All other SQL Tasks is then executed.
0
 
nmcdermaidCommented:
But the Data Flow task is the only one that acceses Oracle, right?
To verify that Pedro is correct, go into Services and change the SQL Agent log on account to your own account and try again.
If that fixes it, you know thats the issue.
Then you just have to find out why.
It could be that the Oracle client installation is user specific or something.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
nmcdermaidCommented:
If your other tasks do indeed access Oracle, then this may not be the exact issue, but still try the SQL Agent Log On test anyway.
0
 
ContestoasAuthor Commented:
Yes it is just the Oracle tasks that fails.
I have created an XML Package Configuration file with the passwords:

<?xml version="1.0" ?>
 <DTSConfiguration>
 <DTSConfigurationHeading>
  <DTSConfigurationFileInfo GeneratedBy="EZONE\xxxx" GeneratedFromPackageName="Package" GeneratedFromPackageID="{7B6F9423-2126-4B55-B3A3-EDC9EE796764}" GeneratedDate="23.03.2009 13:30:13" />
  </DTSConfigurationHeading>
 <Configuration ConfiguredType="Property" Path="\Package.Connections[ezone-sql01.FFe.myuser].Properties[Password]" ValueType="String">
  <ConfiguredValue>xxxxxxxxx</ConfiguredValue>
  </Configuration>
 <Configuration ConfiguredType="Property" Path="\Package.Connections[FOFO.ext_mysys].Properties[Password]" ValueType="String">
  <ConfiguredValue>xxxxxxxx</ConfiguredValue>
  </Configuration>
  </DTSConfiguration>


The package ProtectionLevel = EncryptSensitiveWithUserKey

Is the Configuration file not set up correctly?
Do I need to include the login as well ?

Peter
0
 
nmcdermaidCommented:
To verify that Pedro is correct, go into Services and change the SQL Agent log on account to your own account and try again.
 
0
 
ContestoasAuthor Commented:
Hi
I tried changing the SQL Agent Login and it worked !!!
But I'm a bit puzzled ...
The SQL Server Aget was running with a domain admin user "sqlservice"
I changed it to Administrator and it worked!

Can you tell me what I need to do with my "sqlservice" user to make this work.
0
 
nmcdermaidConnect With a Mentor Commented:
Possibly the Oracle driver uses something in your home directory. If you are using a DSN, make sure it is a system DSN, not a user DSN.
To definitely find the issue, use procmon to find out what SSIS is doing when it accesses the Oracle driver.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.