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

Question

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

Mahesh Shrestha 101

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.

Accepted answer

2 additional answers

Your answer

Answer 1

Mahesh Shrestha 101

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

Answer 2

Monalv-MSFT 5,926

Hi @Mahesh Shrestha ,

Could you please check if you install correct ODBC provider for ODBC Connection? 32bit or 64bit?
If it's 32bit, please set Run64BitRunTime as False;
If it's 64bit, please set Run64BitRunTime as True.

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?

Monalv-MSFT 5,926 Reputation points

2021-01-06T01:48:40.36+00:00
Hi @Mahesh Shrestha ,

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

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

Best Regards,
Mona
Mahesh Shrestha 101 Reputation points

2021-01-06T03:26:25.74+00:00

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.

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.

Regards,
Mahesh
Mahesh Shrestha 101 Reputation points

2021-01-06T05:15:29.43+00:00

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.

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.

However, the same package runs well through Visual Studio- SSDT debug mode on the same machine.
Monalv-MSFT 5,926 Reputation points

2021-01-06T06:13:44.107+00:00

Hi @Mahesh Shrestha ,

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.

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
Mahesh Shrestha 101 Reputation points

2021-01-07T03:22:58.213+00:00
Hi @Monalv-MSFT ,
1.
Version of SSDT is : Version 16.8.2
version of SQL Server is : Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64)

Source for above Picture is ODBC Connection to Sybase. I can find the difference on ODBC images for 2 different versions of SSDT.

I can confirm that the user that is running the SSIS package in the SQL Job has permission to access the ODBC connection manager as the same ODBC is being used by another similar package with same user on same ETL server.

Regards,
Mahesh
Monalv-MSFT 5,926 Reputation points

2021-01-08T08:39:42.033+00:00

Hi @Mahesh Shrestha ,

Please try to use ADO .NET Connection manager to connect to Sybase DB.

We can refer to Import and Export from SQL Server to Sybase DB using SSIS.

Best Regards,
Mona

Answer 3

Mahesh Shrestha 101

Hi @Monalv-MSFT

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.
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.

Monalv-MSFT 5,926 Reputation points

2021-01-14T07:32:44.403+00:00

Hi @Mahesh Shrestha ,

May I know if you find the solution?

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

Best Regards,
Mona

Share via

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

2 additional answers

Your answer