Solved

ADO.NET Destination ODBC Error

Posted on 2010-08-19
7
1,484 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Viewers will learn how the fundamental information of how to create a table.

729 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