Link to home
Start Free TrialLog in
Avatar of Contestoas
ContestoasFlag for Norway

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Contestoas

ASKER

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.
Avatar of nmcdermaid
nmcdermaid

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.
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.
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
To verify that Pedro is correct, go into Services and change the SQL Agent log on account to your own account and try again.
 
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial