Solved

ADO.NET Destination ODBC Error

Posted on 2010-08-19
7
1,477 Views
Last Modified: 2013-11-27
We are trying to move data from our SQL 2008 server to our as400 server. When creating the SSIS package we are using an ADO.NET Destination to the as400 server. The problem is that when we run the package the ADO.NET destination only writes one record to the as400 before failing the package with the following error:

[ADO NET Destination [253]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [07002] [IBM][iSeries Access ODBC Driver]Wrong number of parameters.

We have tried to upload only one record and the process works succefully.  Then two records which fails.

We have been banging our heads over this issue, any help would be appreciated.
0
Comment
Question by:hedria00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33480165
Are you committing after each insert?
Also - is your ADO.NET calling any stored proc inside AS400?

Thanks
0
 

Author Comment

by:hedria00
ID: 33480184
We are not calling any procedures on the the AS400 side.

I have to admit that I don't know how to check the fact of commiting on each insert.  Is this a setting in the Data flow task?
0
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33480394
Without looking at your code, it is difficult to give an exact reason. However, here are a couple of things to try:

1) Ensure that your ADO.NET connections are being closed - using conn.Close(); at the end of each  (see code below)
2) If you are trying to do multiple inserts all at once (batch mode), you can place them all inside a single transaction as follows (see code below)

If you are doing both of these things, I would need to look at your data access code to figure out what could be missing.
Thanks


// Closing Connections
if (dbConnection.State != ConnectionState.Closed) { dbConnection.Close(); }

// Transaction scope
try
    {
        // BeginTransaction() Requires Open Connection
        connection.Open();
        
        transaction = connection.BeginTransaction();
        
        // Assign Transaction to Command
        command.Transaction = transaction;
        
        // Execute 1st Command
        command.CommandText = "Insert ...";
        command.ExecuteNonQuery();
        
        // Execute 2nd Command
        command.CommandText = "Update...";
        command.ExecuteNonQuery();
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
    finally
    {
       if (connection.State != ConnectionState.Closed) { connection.Close(); }

}

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:hedria00
ID: 33480690
We are using the GUI designer to create data flow tasks.  We have no code level access to move these data elements.  We are doing this in SSIS.
0
 
LVL 4

Expert Comment

by:avarmaavarma
ID: 33480733
I am not very familiar with SSIS, however, you may want to look into the 'Bulk Insert ' task instead of a 'data flow' task to do multiple inserts.

http://msdn.microsoft.com/en-us/library/ms141122.aspx

Thanks
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 500 total points
ID: 33482330
use OLE DB provider for as400 instead of ado.net provider
0
 

Author Comment

by:hedria00
ID: 33503437
Reza

we had tried to use the OLE DB connection with no success,  but we had made the conncection by hand.  Out of sheer desperation we tried the wizard and it created it for us successfully.  

Thanks for your direction
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question