Solved

ADO.NET Destination ODBC Error

Posted on 2010-08-19
7
1,490 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

632 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