SSIS : too many bind variables

SSIS : too many bind variables

Postby wawanco » Fri 22 May 2015 12:21

Hi,
I'm using:
    Visual Studio 2013
    dotConnect for Oracle 7.9
    SQL Server Data Tools 12

I try to insert data into an Oracle table using SSIS.

When I have less than 5000 rows (i just add ROWNUM < 5000 at the end of the query), everything works fine.

But when I have more than 5000 rows I have the following error :

Code: Select all
[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: ORA-06550: line 5463, column 205:
PLS-00183: too many bind variables


Have you ever encountered this issue ?

Edit : I also tried with dotConnect 8.4 : same issue
wawanco
 
Posts: 2
Joined: Fri 22 May 2015 12:11

Re: SSIS : too many bind variables

Postby Pinturiccio » Tue 26 May 2015 14:42

We could not reproduce the issue. Please provide the following:
1. DDL/DML scripts of you table. If a DML script is big you can send us the first couple of rows;
2. The actions you perform:
2.1. Which ADO NET Source component do you use, and with which provider;
2.2. Which ADO NET Destination component do you use;
3. Is the issue reproduced if you perform the same actions with another table?
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Re: SSIS : too many bind variables

Postby wawanco » Thu 28 May 2015 09:05

Thanks for your answer.

1. DML of my table

Code: Select all
CREATE TABLE EXT_STN_REL_RELCOM
(
  LOGIN       INTEGER,
  NUMACH      INTEGER,
  CODCLI      VARCHAR2(64 CHAR),
  CODNAF      VARCHAR2(6 CHAR),
  ENSACH      VARCHAR2(40 CHAR),
  NOMACH      VARCHAR2(255 CHAR),
  SIREN       VARCHAR2(9 CHAR),
  NUMVAT      VARCHAR2(64 CHAR),
  IDNATIONAL  VARCHAR2(50 CHAR),
  ATR_ID      VARCHAR2(66 CHAR),
  CODCLIENT   VARCHAR2(64 CHAR),
  ENTJUR      INTEGER
)


2. The actions I perform

My SSIS flow is simplest possible flow :
[SRC] --> [DST]

SRC component has the following query :

Code: Select all
SELECT        LOGIN, NUMACH, CODCLI, CODNAF, ENSACH, NOMACH, SIREN, NUMVAT, IDNATIONAL, ATR_ID, TYPE_FICHE, CODCLIENT, ENTJUR
FROM            STN_REL_RELCOM
WHERE        TYPE_FICHE = 0


2.1 Which ADO NET Source component do you use, and with which provider

I use base ADO NET source component provider : .Net Providers\dotConnect for Oracle

2.1 Which ADO NET Destination component do you use

I use base ADO NET destination component provider : .Net Providers\dotConnect for Oracle

3. Is the issue reproduced if you perform the same actions with another table

I have try with another table, another server, another Oracle instance and I have the same issue.
I also try with another ETL (with JDBC access) and everything works fine.
wawanco
 
Posts: 2
Joined: Fri 22 May 2015 12:11

Re: SSIS : too many bind variables

Postby Pinturiccio » Fri 29 May 2015 11:42

We are investigating the issue and will post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Re: SSIS : too many bind variables

Postby Pinturiccio » Tue 02 Jun 2015 13:56

We could not reproduce the issue with your script. Please provide us more information:
1. What connection mode do you use, OCI or Direct;
2. If you use OCI connection mode, tell us, which client version you use;
3. Oracle server versions you use in source and destination components;
4. Call stack of the error;
5. dotConnect for Oracle version;
6. Please create and send us a test package that reproduces the issue.
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Re: SSIS : too many bind variables

Postby mezulity » Thu 18 Jun 2015 14:27

Hi, I am having the same issue too. I am loading about 300 Million records with a CLOB datatype in Oracle using ADO.NET destination.
mezulity
 
Posts: 3
Joined: Thu 18 Jun 2015 14:25

Re: SSIS : too many bind variables

Postby mezulity » Thu 18 Jun 2015 14:33

Using ADO.NET/Oracleclient Data Provider
Call stack of error
ORA-06550: line 4099, column 1:
PLS-00183: too many bind variables
mezulity
 
Posts: 3
Joined: Thu 18 Jun 2015 14:25

Re: SSIS : too many bind variables

Postby mezulity » Thu 18 Jun 2015 14:35

Oracle version 12.1.0.2
mezulity
 
Posts: 3
Joined: Thu 18 Jun 2015 14:25

Re: SSIS : too many bind variables

Postby Pinturiccio » Fri 19 Jun 2015 14:00

mezulity wrote:Using ADO.NET/Oracleclient Data Provider

We support only Devart's products. Try using dotConnect for Oracle instead of OracleClient Data Provider.

dotConnect for Oracle supports SSIS only in the Professional edition. For more information, please refer to http://www.devart.com/dotconnect/oracle/editions.html

dotConnect for Oracle has a Trial version which allows you to evaluate dotConnect for Oracle during 30 days since the provider installation. Trial version of dotConnect for Oracle offers the same features as the Professional edition. You can download the Trial version of dotConnect for Oracle from our site http://www.devart.com/dotconnect/oracle/download.html

Is the issue reproduced with dotConnect for Oracle?
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle