Pre execute Failure on Look up task

Hi,

I have an OLE DB Source. The output of OLEDB source is connected to a look up task. The output of the lookup task goes into OLE DB Destination. When executed..

I am getting this error:

DTS.Pipeline: component "Lookup" (2834) failed the pre-execute phase and returned error code 0x8007000E.

Any idea why pre-execute phase failure occurs?

TIN,
Anand

0x8007000E is out of memory error. How big is the reference table? Is Lookup running in Full Cache mode? Can you try running it in Partial Cache mode?|||Hi,

You are probably right. I have a data flow task before this specific task which contains 15 Look Up Tasks. I am presuming that memory is not released when needed.

The reference table is very small. It contains around 220 records.

Also, I have 2 GB memory available on this machine.

-Anandsql

PRB: Win2k3srv/SQL2kSP3 --OLE DB-->Ora92

I have a problem connecting to Oracle from SQL2000sp3/linkedservers
on Win2003srv using any OLE DB provider.
[QUOTE]
Machines configuration
=======================
Machine: IVAN (Development workstation)
Intel PC P4/1.5, 512MB
WinXP Pro SP1
SQL2000 SP3, Office XP Pro
ADO 2.71.9030, ODBC 3.520.9030/9041
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK03 (Development server)
Siemens P2/400 Server, 512MB
Win2003 Server Std - RRAS + Kerio WinRoute
SQL2000 SP3, Office XP Pro
ADO 2.80.1022, ODBC 3.525.1022
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK02 (Production server)
Dell Server P3/1.3, 1GB
Win2000 Server Std SP4 - DC
Oracle Database 9.2.0.1.0
Network scheme
==============
|--LAN 1--| |--LAN
2--|
dsl-gw__ws1__ws2
isdn-gw/rras/fw___switch_______dc/db__pc1/20
IVAN <--/--ISDN--/--> GRSK03 <--100BaseT--> GRSK02
Oracle client configuration
===========================
TNSNAMES.ORA & SQLNET.ORA are same on both IVAN and GRSK03
# SQLNET.ORA Network Configuration File:
C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
# TNSNAMES.ORA Network Configuration File:
C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = grsk02.grskoviceva.local)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
SQL2000 Linked server configuration
===================================
On both IVAN and GRSK03
EXEC sp_addlinkedserver
@.server = 'Point',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'ORCL'
GO
EXEC sp_addlinkedsrvlogin 'Point', 'false', NULL, '*****', '******'
PROBLEM
=======
For example following expression:
SELECT TOP 10 * FROM OpenQuery(Point,'SELECT * FROM FK350')
returns resultset on IVAN (works fine) while on GRSK03 returns following
error?
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: The provider did not give any information about the
error.].
The same error on GRSK03 is returned if I try making a linked server using
MSDASQL (ODBC) provider.
I've configured (in registry) Oracle provider for OLE DB (OraOLEDB.dll).
Again on IVAN it works fine, on GRSK03 runs infinitely (no error message).
ODBC DSN on Oracle works fine (for example linking view FK350 in Access) on
both machines.
[QUOTE]
Someone knows why? I suspect security features of Win2003 or RRAS maybe
but for what I've checked all ports are opened and if there is a problem
with router/firewall
it would be on IVAN not GRSK03 which is on LAN side-by-side with GRSK02.
Regarding security I've tried logging using 'sa' but with no results.
I've also tried disabling the Kerio firewall on GRSK03 but it didn't help.
If someone is familiar with the issue, please tell me your opinion.
I'm suspecting mssqlsrv installation although it's been recently installed
and
configured like any other of 6 SQL Servers on other LANs.
Ivan Nikolic
SAIL Company Croatia Ltd.
DB developer
ivan@.sail.hrI would start with this article:
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Possible Validation Problem with Flat File Between Two Data Flows in a Package

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

You'll have to set DelayValidation=TRUE on the second data-flow.

You should probably use raw files rather than flat files. Unless that is you want to use the files elsewhere, other than in SSIS.

-Jamie

|||I tried some things in this order:

1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.

2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:

Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.
|||

Allen H wrote:

I tried some things in this order:

1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.

2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:

Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Strange. Its as if the second data-flow is trying to read the file before it has finished been written. This would be consistent with the behaviour you were getting with flat files.

Allen H wrote:

3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.

They're much much quicker.

-Jamie

|||

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.

|||

jwelch wrote:

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.

Yeah John's spot on. I glossed over this bit initially: "...and stores the data in the same flat file..."

Sorry about that.

Thanks John.

-Jamie