Unable to connect with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 with ADO Destination Component in  SSIS 2008  at run time (although the connection is tested successfully with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in  SSIS 2008)
As we are passing through critical phase of the project and we are facing following issues which need to be resolved.Please help on this. Currently we are transferring Data from SQL Server to DB2 using SSIS (SQL Server 2008).The IBM DB2 version is DB2 ESE 9.1 Fix Pack 5 on Linux 4.8. 1] Unable to connect with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008 at run time (although the connection is tested successfully with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008) and the error is "[ADO NET Destination [6096]] Error: An exception has occurred during data insertion, the message returned from the provider is: Object reference not set to an instance of an object. " I tried to use CozyRock DB2 Destination component instead of ADO destinatoin Component then i am able to use IBM DB2 .NET provider at run time. Also i tested connection successfully using IBM DB2 .NET Data Provider 9.1.5 but whenever i edit ADO destination component and i select the connection manager using IBM DB2 .NET Data Provider 9.1.5 then i am not able to view the list of tables in Tables or Views Tab 2] The overall performance is very poor at the time of transferring data from SQL server to IBM DB2 (with OLEDB providers for DB2) using SSIS 2008. Please suggest and give solution for above stuff which will be great help for our project because i have spent lot of time on this but i am unable to find solution.
October 5th, 2010 5:28pm

Currently we are transferring Data from SQL Server to DB2 using SSIS (SQL Server 2008).The IBM DB2 version is DB2 ESE 9.1 Fix Pack 5 on Linux 4.8. 1] Unable to connect with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008 at run time (although the connection is tested successfully with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008) and the error is If you try to set ODBC DSN (as a test) using IBM DB2 .NET Data Provider 9.1.5 does it connect? I only see IBM DB2 .NET Data Provider 9.1.5 mentioned, what is the difference? So, you can use CozyRock but it slow, or not? Please elaborate. "[ADO NET Destination [6096]] Error: An exception has occurred during data insertion, the message returned from the provider is: Object reference not set to an instance of an object. " I tried to use CozyRock DB2 Destination component instead of ADO destinatoin Component then i am able to use IBM DB2 .NET provider at run time. Also i tested connection successfully using IBM DB2 .NET Data Provider 9.1.5 but whenever i edit ADO destination component and i select the connection manager using IBM DB2 .NET Data Provider 9.1.5 then i am not able to view the list of tables in Tables or Views Tab 2] Please suggest and give solution for above stuff which will be great help for our project because i have spent lot of time on this but i am unable to find solution. The overall performance is very poor at the time of transferring data from SQL server to IBM DB2 (with OLEDB providers for DB2) using SSIS 2008. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2010 9:03pm

Thanks for your response. I have created ODBC DSN and i have used this DSN in ODBC provider then its working fine.We can not use ODBC DSN with IBM DB2 .NET Data Provider 9.1.5 becuase if we will open connection manager for ADO .NET in SSIS then you will see ODBC provider and IBM DB2 .NET Data Provider 9.1.5 seperatly. CozyRoc has its own DB2 destination component and it has Bulk insert functionality which gives fast performance (i have tested) but company do not want to go for that because of support related concerns. My biggest concern is with IBM DB2 .NET Data Provider 9.1.5 or IBM DB2 .NET Data Provider provider. Whether this provider is working with SSIS 2008. 1] CONN1- First I have created ADO .NET connection using IBM DB2 .NET Data Provider 9.1.5 provder for DB2 and its tested successfully. CONN2 - Also i have created ADO .NET connection for SQL server using ODBC provider and its tested successfully. 2] I have created Dataflow Task in Control flow and under that task - I have used ADO Source component with CONN2 for SQL server and ADO destination component with CONN1 for DB2 in DATA FLOW. 3] Whenever i executed package i got following error whihc is related to IBM DB2 .NET Data Provider 9.1.5 provder for DB2 ; "[ADO NET Destination [6096]] Error: An exception has occurred during data insertion, the message returned from the provider is: Object reference not set to an instance of an object. " I want to know why this error is coming and what is the solution? Also i want to know whether IBM DB2 .NET Data Provider 9.1.5 provder for DB2 is working with SSIS 2008(becuase this provider is working with Cozyroc DB2 destination component instead of ADO destination component whihc i have mentioned above in SSIS 2008). If it is working with CozyRoc then it should work with ADO destination component. Is there any extra setting is required. I hope you got my problem. Please help me on this.
October 6th, 2010 7:00pm

Please respond on above issues. I kindly request everyone please help me.....
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2010 10:58am

Hi, My name is Carlos and I work with SQL Server 2008 R2. I can transfer data from DB2(AS/400) into SQL Server. I'm not have a problem with that. My problem is that the transfer is very slow using connection ADO.NET, but was only way for transfer from DB2 if I don't want lost data. This because ADO.NET use ODBC connection and there I can convert data in Binary CCSID 65535 and who know that, know about I talk now. Anyway, anybody knows how I can use ADO.NET or other provider for transfer more fast? Anybody know me about this problem? Thank you very much.. Carlos - DBA
October 11th, 2010 6:27pm

Hi, My Name is vishal.You should use ADO.NET Source using ODBC for DB2 and Destination component should be OLEDB Destination for SQL server. Here for Destination i.e OLEDB load data using Fast Load option. Using OLEDB destination with Fast Load is only applicable for SQL server not for DB2. Try it out and let me know whether its working. Thanks Vishal
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 11:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics