• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

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
0
Contestoas
Asked:
Contestoas
  • 4
  • 3
2 Solutions
 
PedroCGDCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
nmcdermaidCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now