ERROR in loading data to DB2 from AS400

Hi,

I have a problem loading data from a flat file into a DB2 Dtabase by using the OLE DB Provider for DB2.

I read the data from a flat file in the unicode format and as soon as the data is to be written in the DB2 database the following error occurs and the loading process is aborted:

Information: 0x402090DE at Data Flow Task, Flat File Source [813]: The total number of data rows processed for file "C:\Dokumente und Einstellungen\Administrator\Desktop\SSIS1.txt" is 2.
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [842]: An OLE DB error has occurred. Error code: 0x80040E53.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (842) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.......

......Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package 1.dtsx" finished: Failure.

I dont know waht those error codes meanand I tried so many things already. The tables in the database are created correctly and also, SSIS can read the data from the database if I use the preview function, but somehow, the program never starts to write data into the database for some reason. Can anyone help me?

Thx, andy

It looks like some other have had similar problems using the OLE DB Source with DB2.

One

user fixed the problem but using a DataReader source and an ODBC

connection to DB2 (see this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250370&SiteID=1)

Another

user seems to have fixed it by restarting the Distributed Transaction

Coordinator service although he was using beta 2 of SQL Server. See

here: http://www.ureader.com/message/287061.aspx

Hope this helps.

Jason|||Hi,

I'm Facing almost similar kind of problem.
Got to load data to DB2 data base taken from AS400.

I'm using dataReader ( ADO .Net connection (ODBC connection did not work, its a work around to fetch data)) to fetch data from AS400 Server and trying to load it to a DB2 datbase ( OLEDB conection manager - using IBM OLEDB provider for DB2)

>> Is there a compatibility issue with DataReader Source and OLEDB destination ?
>> Any other alternative in that case?

PLZ help !!!!!!!!!!

Here is the ERROR Message i get on execution:

Error: 0xC0202009 at Data Reader to DB2, OLE DB Destination [12]: An OLE DB error has occurred. Error code: 0x80040E53.

Error: 0xC0047022 at Data Reader to

DB2, DTS.Pipeline: The ProcessInput method on component "OLE DB

Destination" (12) failed with error code 0xC0202009. The identified

component returned an error from the ProcessInput method. The error is

specific to the component, but the error is fatal and will cause the

Data Flow task to stop running.

Error: 0xC0047021 at Data Reader to DB2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.

Information: 0x4004300B at Data Reader to DB2, DTS.Pipeline: "component "OLE DB Destination" (12)" wrote 0 rows.

Task failed: Data Reader to DB2

SSIS package "Package.dtsx" finished: Success.

Error in deleting

When I am trying to delete the record from flat table it gave me following
error and that record doesn't have a constraints / triggers so why it's
giving me this error.
SQL Server Enterprise Manager
Key column information is insufficient or incorrect. Too many rows were
affected by update.
OK Help
Thanks in advance.
you don't have a unique identifier/key
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>
|||You need to have a primary key.
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>
|||you need to have a primary key on that table .
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>

Error importing excel file

When ever importing a excel file or flat file, I get an Unhandled exception error and the event log reads a .Netframework error ID 1000.

Checking the database I was importing into, I find that the table was created with the columns, but no data within the columns.

The import wizards report 509 row copyed before crashing with the Handle error, where a dialog box appears allowing debug or close.

Can anyone help?

I include the logged errors.

Event Log

.NET Runtime 2.0 Error
Event ID: 1000

Faulting application dtswizard.exe, version 9.0.1399.0, stamp 434f5e2a, faulting module dtspipeline.dll, version 2005.90.1399.0, stamp 434f5dbc, debug? 0, fault address 0x0004ba38.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

0000: 41 00 70 00 70 00 6c 00 A.p.p.l.
0008: 69 00 63 00 61 00 74 00 i.c.a.t.
0010: 69 00 6f 00 6e 00 20 00 i.o.n. .
0018: 46 00 61 00 69 00 6c 00 F.a.i.l.
0020: 75 00 72 00 65 00 20 00 u.r.e. .
0028: 20 00 64 00 74 00 73 00 .d.t.s.
0030: 77 00 69 00 7a 00 61 00 w.i.z.a.
0038: 72 00 64 00 2e 00 65 00 r.d...e.
0040: 78 00 65 00 20 00 39 00 x.e. .9.
0048: 2e 00 30 00 2e 00 31 00 ..0...1.
0050: 33 00 39 00 39 00 2e 00 3.9.9...
0058: 30 00 20 00 34 00 33 00 0. .4.3.
0060: 34 00 66 00 35 00 65 00 4.f.5.e.
0068: 32 00 61 00 20 00 69 00 2.a. .i.
0070: 6e 00 20 00 64 00 74 00 n. .d.t.
0078: 73 00 70 00 69 00 70 00 s.p.i.p.
0080: 65 00 6c 00 69 00 6e 00 e.l.i.n.
0088: 65 00 2e 00 64 00 6c 00 e...d.l.
0090: 6c 00 20 00 32 00 30 00 l. .2.0.
0098: 30 00 35 00 2e 00 39 00 0.5...9.
00a0: 30 00 2e 00 31 00 33 00 0...1.3.
00a8: 39 00 39 00 2e 00 30 00 9.9...0.
00b0: 20 00 34 00 33 00 34 00 .4.3.4.
00b8: 66 00 35 00 64 00 62 00 f.5.d.b.
00c0: 63 00 20 00 66 00 44 00 c. .f.D.
00c8: 65 00 62 00 75 00 67 00 e.b.u.g.
00d0: 20 00 30 00 20 00 61 00 .0. .a.
00d8: 74 00 20 00 6f 00 66 00 t. .o.f.
00e0: 66 00 73 00 65 00 74 00 f.s.e.t.
00e8: 20 00 30 00 30 00 30 00 .0.0.0.
00f0: 34 00 62 00 61 00 33 00 4.b.a.3.
00f8: 38 00 0d 00 0a 00 8.....

Debug Log

Unhandled exception at 0x2357ba38 in DTSWizard.exe: 0xC000001D: Illegal Instruction.

Would you be able to help us nail down this problem by identifying what data is exactly causing this crash?

You can do this by reducing the data set in your spreadsheet (i.e. take 10 rows around the row #509 and see if the problem reproes, than try to find the exact row, then the exact column if possible) and figuring out what is the data and data type of the cell that could not be handled.

In the case you can give us the entire spreadsheet or the minimal piece that still reproes the problem; we could start our investigation from there.

Thanks.

|||

Hi

I have cut down the excel file, still no luck. I have even created a new excel file with two columns and two rows and I still get the error. I have noticed the test file I created "test.xls" is imported with a $ "test$" .

test.xls

Temp Contract 1 231 2 234

This should be simple to import.

John

|||

Well, this is quite simple data and it should not cause any problem. It worked for me quite easy.

Could it be possible that you have some problems with your installation/binaries? What edition of SQL Server do you have installed? Have you installed some previous versions on the same machine?

|||

Bob,

Here are the version information:

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

There was no prevoius installations of SQL, However after installing SQL2005 I installed the MSDN disk that came with it, I believe there was an error, I uninstalled and re-install MSDN without any errors "Installation Successful".

John