Solved

ADO.NET Destination ODBC Error

Posted on 2010-08-19
7
1,441 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
  • 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now