Contestoas
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\sqls ervice,Pac kage,{7B6F 9423-2126- 4B55-B3A3- EDC9EE7967 64},{FBBE5 346-A10F-4 469-A300-D 0895179DB0 7},16.04.2 009 15:15:53,16.04.2009 15:15:53,1074016266,0x,Val idation phase is beginning.
OnProgress,EZONE-SQL01,EZO NE\sqlserv ice,Get data from Oracle,{54FE4CD4-3B9E-44DD -87E6-98CD 80D38822}, {FBBE5346- A10F-4469- A300-D0895 179DB07},1 6.04.2009 15:15:53,16.04.2009 15:15:53,0,0x,Validating
User:Diagnostic,EZONE-SQL0 1,EZONE\sq lservice,f ofo.ext_ed ocs,{F833C 8BE-5035-4 544-8740-0 29579E9490 9},{FBBE53 46-A10F-44 69-A300-D0 895179DB07 },16.04.20 09 15:15:53,16.04.2009 15:15:53,0,0x,ExternalRequ est_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataS ource'.
After this nothing happens ....
I need help please !
Peter
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,
OnProgress,EZONE-SQL01,EZO
User:Diagnostic,EZONE-SQL0
After this nothing happens ....
I need help please !
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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="{7 B6F9423-21 26-4B55-B3 A3-EDC9EE7 96764}" GeneratedDate="23.03.2009 13:30:13" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections [ezone-sql 01.FFe.myu ser].Prope rties[Pass word]" ValueType="String">
<ConfiguredValue>xxxxxxxxx </Configur edValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections [FOFO.ext_ mysys].Pro perties[Pa ssword]" ValueType="String">
<ConfiguredValue>xxxxxxxx< /Configure dValue>
</Configuration>
</DTSConfiguration>
The package ProtectionLevel = EncryptSensitiveWithUserKe y
Is the Configuration file not set up correctly?
Do I need to include the login as well ?
Peter
I have created an XML Package Configuration file with the passwords:
<?xml version="1.0" ?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="EZONE\xxxx" GeneratedFromPackageName="
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections
<ConfiguredValue>xxxxxxxxx
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections
<ConfiguredValue>xxxxxxxx<
</Configuration>
</DTSConfiguration>
The package ProtectionLevel = EncryptSensitiveWithUserKe
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.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.