question

MaheshShrestha-3706 avatar image
0 Votes"
MaheshShrestha-3706 asked MaheshShrestha-3706 answered

Getting SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on <DataFlowName> returned error code 0x80004005 on Sybase DB to File Extraction

I am getting data from Sybase DB (using ODBC Connection) to load into the file using MS-SSIS but in the data flow task while getting from the source its giving the following errors.


<ControlFlowName> :Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on <DataFlowName> 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.
There may be error messages posted before this with more information about the failure.

I have tried with setting MaxConCurrentExecutions=1 as suggested on some web pages as well.

Using the same connection/same DB is working fine for some instances of Table extraction.
The same extraction works totally fine while running on the Debug mode using MS-Visual Studio SSDT.

I need help to solve this issue. Any help would be appreciated.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MaheshShrestha-3706 avatar image
1 Vote"
MaheshShrestha-3706 answered

ADO.NET seems super slow.
Finally , I ended up using OLE DB Connector for ODBC.
For this, it will be required to create Linked Server
and point the ODBC DSN.

And then Install SQL Anywhere Driver on ETL servers :
https://wiki.scn.sap.com/wiki/display/SQLANY/SAP+SQL+Anywhere+Database+Client+Download

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
1 Vote"
Monalv-msft answered MaheshShrestha-3706 converted comment to answer

Hi @MaheshShrestha-3706 ,

  1. Could you please check if you install correct ODBC provider for ODBC Connection? 32bit or 64bit?

  2. If it's 32bit, please set Run64BitRunTime as False;
    If it's 64bit, please set Run64BitRunTime as True.
    53459-run64bitruntime1.png


Best Regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?



· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MaheshShrestha-3706,

  1. May I know if you can share the screenshot of components in your ssis package?

  2. Could you please check if you tick use 32 bit runtime in job step? If yes, please untick it. If no, please tick it.
    53851-jobruntime.png

Best Regards,
Mona



0 Votes 0 ·
jobruntime.png (26.8 KiB)

Hi @Monalv-msft
This is the data flow that is failing, which is fairly simple data extraction from Sybase DB table to a text file.

53787-dataflow.png


Also, i have tried enabling and disabling tick for "Use 32 Bit Runtime" on MS-SQL Job for the package.
Also, using the logging level to Verbose did not helped in producing any other error messages.
53848-runtimecheck.png


Regards,
Mahesh

0 Votes 0 ·
dataflow.png (23.7 KiB)
runtimecheck.png (47.4 KiB)

Hi @Monalv-msft
Here's the screenshot of data flow that is failing, which is fairly simple data extraction from Sybase DB table using ODBC Connector to the text file.

53878-dataflow.png


Also , I have tried with both option of "Use 32 Bit Runtime" On and Off and both are failing with same above error on SSIS package run from the SQL Job Agent.
53792-runtimecheck.png

However, the same package runs well through Visual Studio- SSDT debug mode on the same machine.


0 Votes 0 ·
dataflow.png (23.7 KiB)
runtimecheck.png (47.4 KiB)

Hi @MaheshShrestha-3706 ,

1.May I know if you can share the version of SSDT and the version of SQL Server where you use the agent job?


2.May I know what is the source in your picture above? It seems that it isn't ODBC Source.

53893-df.png

53793-odbc-source.png


3.Please check if the user that will run the ssis package in job has permission to access the ODBC connection manager.


Best Regards,
Mona



0 Votes 0 ·
df.png (45.4 KiB)
odbc-source.png (2.0 KiB)
Show more comments
MaheshShrestha-3706 avatar image
0 Votes"
MaheshShrestha-3706 answered Monalv-msft commented

Hi @Monalv-msft
1. Could you please check if you install correct ODBC provider for ODBC Connection? 32bit or 64bit? ==>Yes Correct Sybase Driver are installed and are working while testing the connection. Both 32bit and 64bit are installed.
2. I have tried with both Run64BitRunTime=True and False . Funny thing is , it runs well while running on Debug mode on Visual Studio - SSDT. Its only failing with the above error while running the same SSIS package through SQL Agent Job.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MaheshShrestha-3706,

May I know if you find the solution?

If so, please click "Accept Answer" your own answer. Thank you.

Best Regards,
Mona

0 Votes 0 ·