?
Solved

ADO.NET Destination ODBC Error

Posted on 2010-08-19
7
Medium Priority
?
1,504 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

718 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