February 24, 2009 at 7:53 am
Hi,
Initially i had the problem to connect and insert data in DB2 OS/390 7.1.1. But after downlaoding and configuring "MS OLE DB Provider fro DB2" i manage to do that.
Now i am again stuck in weird problem and that is after inserting few rows or few hunders rows package gets fail without giving any meaningfull error.
Error is mentioned below
[color=00009900][OLE DB Destination [1911]] Error: An OLE DB error has occurred. Error code: 0x00040EDA.
[OLE DB Destination [1911]] Error: The "input "OLE DB Destination Input" (1924)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1924)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (1911) failed with error code 0xC0209029. 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.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
its not the data problem, bcoz every time package inserts a different number of rows and gets fail on differnt row. like at times 459, at 54 no fix number of rows.
there is no constarint on the table except one primary key, which i have confirmed i m not violating.
More detail about my package is......i am extracting data from oracle then stroring this data in a SQL server staging table and from this Sql server staging table i am inserting data in DB2.
i have SP2 installed and i have no control over DB2 box all i can do is on my SSIS server.
Can any body help me out from this strange problem
From couple of days my status on facebook is "OLE DB destiantion for DB2 Sucks". i really wanna change my status.
Thanks
February 24, 2009 at 8:48 am
I feel your pain. Especially when it comes to DB2. I don't think I am going to be any specific help, but I would suggest that set the On Error property on the destination to Redirect Row and redirect the output to either a table or file. Then at least you can see the rows that are causing the error. Of course this does mean that non-error rows will continue to be processed and inserted into the table in DB2, so if this is not your desire this may not be the best solution. Here is a link to an article on doing this in SSIS:
http://www.sqlservercentral.com/articles/Integration+Services/62662/
and here is a blog post with an extension of the article:
http://wiseman-wiseguy.blogspot.com/2008/08/ssis-error-logging-custom-component.html
Since you can't be guaranteed of order, unless you have an order by, or use a SORT Transform in SSIS the fact that it fails on different row numbers is really meaningless as it could be the same row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 1:23 am
Thanks jack corbett, Atleast somebody has responded on my thread, previously about connectivity and OLE DB Provider for DB2 i asked a question on MSDN and on this forum, and no body responded me on that thread till date.
well in response to your order by and sort suggestion, i am already using order by clause in my source oracle, even then, failure is after inconsistent number of rows, however i am looking in other two links provided by u and will configure the errorr at destination.
i am onsite consultant at kuwait, we have independence holidays here till sataurday, and i dont have set up of DB2 and oracle to test at home, i will post further investigation and solution on this forum.
Thanks
Rashid
February 25, 2009 at 5:20 am
Your welcome.
The sort on your Oracle source is irrelevant since that is putting the table in a SQL Server table for staging. You need to have an order by on the select from SQL Server staging table.
I hope you can get it figured out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 12:15 pm
yes jack, for that i actually removed that stagging table, and i was inserting data direct in DB2, even then no success.
Thanks once again.
February 25, 2009 at 12:28 pm
Try to get a hold of the DB2 client / gui CD and install that.
I have select * From a DB2 db working on 2005 64 bit export to an Access DB. I believe though it's an old DTS, but it just works .
My DTS connector shows "IBM OLE DB Provider for DB2".
hope that helps!
March 2, 2009 at 6:04 am
well guys configuring "redirect rows on error" worked for me. In source data, there were some rows with Null values, although in destiantion there isnt any Null constraint, therefore my assumption was that it will not create any problem. Howerver it did.
Starnge thing was that when i insertred redirected rows (error rows) direct in DB2 through insert statment, rows inserted successfully, but through SSIS, it was giving me error mentioed above. But handling null values in source data solved the problem.
i think the way oracle,SSIS and DB2 treats null values is different thats y there was problem.
My issuse is resolved but i have a question here, i redirected rows and got error description "No status available" and
error column -1,
Description makes sence, becasue when you work in three different technolgies, SSIS, Oracle DB, DB2 you cannot expect, that SSIS will give you the exact error description, but how one can get exact error column, how can i ineterpret Error column -1. which column is it?
Redirect - http://msdn.microsoft.com/en-us/library/ms166707.aspx
One more thing i have observed during the development of this SSIS package, that SSIS is great ETL tool within microsft technolgies, but when you go out of this box u encounter weird problems. OLE DB Driver issue, 32 bit and 64 bit issue when connecting with oracle and DB2. you guys might not agree with me, but i have observed this, things are aint that straight working in SSIS with other technolgies.....
March 2, 2009 at 6:20 am
I don't know if you actually read the article I linked to, but in it there is mention that you need to use a script component to actually get the error description.
I've never used any other ETL tools, but SSIS is pretty powerful and I think to say that it does not work well in a heterogeneous environment based on the issues you have had would be inaccurate. Because SSIS works with ODBC and OLE DB drivers some of the issues you have encountered may be because of driver issues.
I'm not saying SSIS doesn't have a ways to go, but considering the cost, it is a good tool and I know people who use it to do major ETL processes in heterogeneous environments. SSIS in SQL Server 2005 is really a first generation product because it is so different from DTS. I bet it is much better in 2008 and will be even better in the next generation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 2, 2009 at 6:56 am
jack i did use the script component for error description.
Any ways i agree with u, noramlly microsfot build a paltform and on top of that they mature thier product.
SSIS is first generation product after DTS and i hope that SSIS 2008 would be much better.
March 2, 2009 at 7:25 am
Is the database you are inserting to Binary or ASC II? I have found the removing the Force Translate is sometimes necessary to get some inserts to work. Set 65535 to 0 on the connection in the advanced properties. I am working OS400 v5r4.
March 3, 2009 at 3:20 am
I hope you get this to work - you seem to be getting through most of the problems.
Oracle does indeed deal with NULL values differently to SQL Server and DB2. The SQL connector to Oracle can cope with thse, so this is unlikely to be a problem once the data is in SQL Server.
The Microsoft OLE provider for DB2 is a bit basic. If your organisation has the 'DB2 Connect' product licensed that is a far better choice, but it is not free.
Another place to post about this problem is a DB2 forum. A good one is DB2-L on the IDUG site.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2009 at 8:56 am
Thanks alen for ur feedback....and every body else who are contributing to solve problem. Null values problem now aint that important i have somthing more significant to ask from u guys. However i will definately see DB connect stuff.
here i go with my new problem
Now as per new requirement i have inserted data in DB2 staging table call it "Employee_STG" and from that staging table i need to load data in orginal table call it "Employee"
Seems pretty straight, but i have encounter strange problem in SSIS or may be with DB2
I have to delete data from "Employee" table 1st and then i have to load data. but i have to maintain the transaction. Because i have around 2 million rows that need to be inserted, if there is any data problem in any row or coz of any other reason package gets fails......then delete statment should rollback.
Previous data should be there........simple words "both delete and insert statment should be executed sucessfully or none"
In execute sql task i m passing these two statement.
Note: connection is properly establish with DB2 OS/390 7.1.1
Delete from Employee;
Insert into Employee (id, name)
Select Id, name from Employee_STG;
but the strange thing Execute sql task in SSIS only execute 1st statment and ignores the second insert statment. No idea y..... I dont want to implement two execuste sql task, becoz either complete code should be executed, delete and insert statment or none.
Another thing i dont have nor will be in future any control on DB2 box, therefore i cannot create Store Procedure in DB2 and call that from SSIS. (This option not available")
I have tried DB2 syntax taken from IBM site....and treid Begin Compund Atomic and Begin Atomic statment in Exceute SQL task but no success.......i am totally new to DB2. seems transaction aint that straight in DB2 like MS SQL server
SSIS transaction property cannot be applied here, by putting two seprate Execute SQL task in a container, by this transaction enlisting on DB2 creates problem and since more then one server/machines are invovle...my be MDTC required, dont wanna follow this appracoh.
Can any body help me how could i execute above mention two SQL statment of DB2 in a transaction with in one SQL task. so that both gets completed sucessfully or none.
Or any other way around.....script task of .Net would be last option.....before that can any body figure out. How and y Execute Sql Task executing 1st and ignoring rest of the statment
Thank you very much
March 3, 2009 at 9:07 am
Does DB2 mainframe v7 support the MERGE statement? I don't know when this sttement got to the various DB2 versions. If it is supported then it would be a better choice to do your staging table updates.
All of the DB2 manuals should be available online. If you google 'db2 sql reference' you should find http://www.ibm.com/developerworks/data/library/techarticle/0206sqlref/0206sqlref.html which has a link to the DB2 for OS/390 and Z/OS v7 SQL reference manual.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2009 at 9:09 am
Check out this article on transaction support in SSIS, http://msdn.microsoft.com/en-us/library/ms137690(SQL.90).aspx. I think it covers what you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 3, 2009 at 9:21 am
Thanks jack.
I have already applied this technique and i have mentioned that Transaction property of SSIS creates problem when you have more then one machine and on different enviorments. Microsoft Distributed trancastion cordinator requires for hetrogenous env and machine.
Thanks to u EdVassie as well........
i am looking in the links that you have provided..will see merge statment if that works on 7.1 and within SSIS.
But i still love to see syntax like we have in MS SQL server begin Transaction and end.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply