.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSIS and Microsoft OLE DB Provider for DB2

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server

I am trying to get the best performance driver for transfer of data from AS400 to SQL Server 2005 using SSIS.  I am comparing it to DTS packages using Client Access ODBC Driver (32-bit) which I'll call std ODBC

.Net Provider/ODBC works but is VERY slow. I understand that Microsoft has simply put the .Net layer on top of the std ODBC driver and this overhead is slowing it right down (to a crawl in my opinion)

.Net Oledb/ IBM DB2 for iSeries IBMDA400 works and is about 2.5 times faster than .Net/ODBC, but still about 3x slower than std ODBC

.Net Oledb/ IBM DB2 for iSeries IBMDASQL is similar.

Microsoft OLE DB Provider for DB2 is supposed to be the fastest but it requires upgrading to Enterprise Edition, which is not a trivial cost.

Can anyone give me feedback on the performance I can expect with Microsoft OLE DB Provider for DB2 compared to std ODBC.


View Complete Post

More Related Resource Links

What's wrong with MSDAORA.1 (Microsoft OLE DB Provider for Oracle) in SQL2008R2

Dear Gurus, I'd install a test SQL2008R2 on Windows Server 2008 R2 x64 Version. And Install Oracle both x86 and x64 version Client. And I'd create a new AS Database as AS System Administrator. And then create a new Datasource with "Microsoft OLE DB Prodiver for Oracle" Provider. When I test connection ,  it tested succeed. Then I create Datasource View / Dimension / Cubes etc... But when I process then Database. There is a error occurs. "Database Access Module Error , Provider 'MSDAORA.1' not register" I'd follow http://msdn.microsoft.com/en-us/library/ms152516.aspx change SQL Server 2008 R2's registry key as the note. Then restart this server . But still same error !   And I'd success process the cube if I change provider as "Oracle OLE DB Proverder"   Is MSDAORA been obsolete in SQL SERVER 2008 R2 ?Wilson

How to install Microsoft OLE DB Provider for Oracle

I am using an SSIS Package to get the data from Oracle DB to  SQL server 2005 DB. I am using Micorsoft OLE DB provider for oracle for creating a connection manager to connect to ORACLE 10 g.   The package runs great in my local PC.  when I move the package to the Server ( Microsoft windows server 2003 R2, Enterprise x64 edition) which also has SQL server 2005 .  the package fails to run showing the error: " the AcquireConnection method call to the connection manager "HRDATA" failed with error code 0xC0202009."   I tried to see if the Microst OLE DB provider for  Oracle is installed or not  using the .udl file.  The provider tab in .udl file does not show any provider named " Microsoft OLE  DB provider for Oracle"   Can anyone please help me by telling how can I installed the Microsoft OLEDB provider for Oracle, given my server is ( Microsoft windows server 2003 R2, Enterprise x64 edition)  and my source data base is  ORACLE 10g.

Microsoft.SqlServer.Dts and related assemblies to develop custom ssis task are missing

Hi All, I tried to develop a simple ssis task but the problem that I can't refer the necessary assemblies like Microsoft.SqlServer.Dts.Runtime And also Microsoft.SqlServer.DTSPipelineWrap Microsoft.Sqlserver.DTSRuntimeWrap Microsoft.Sqlserver.ManagedDTS Microsoft.SqlServer.PipelineHost What is certan that they aren't installed within the GAC in my case, so where could I find them? I have SQL server 2008 entrprise edition Other question, should I use Microsoft.SqlServer.Dts.Design and  Microsoft.SqlServer.ManagedDTS which are missed too, or they are part of the 2005 version only Thank you   The complexity resides in the simplicity

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

Dear All, I am getting the below error while converting Excel 2007 file into text format with my customization. Error: 'The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered' Enviromnet : Windows Server 2008 Enterprise 64 bit with SP2 and SQL Server 2008 64 bit installed. Some times its working fine when i registerd C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll file through regsvr32 and restarting SQL Server. But after few days again i am getting the same error. Can some one help me in resolving this issue. Regards, Jignesh

The provider reported an unexpected catestrophic failure using BETA MICROSOFT.ACE.OLEDB.14.0 drivers

I'm just in the process of converting my app to be able to support text importing via "OPENROWSET" in 64 bit SQL Server using the Beta MICROSOFT.ACE.OLEDB.14.0 drivers.  I've previously used the MICROSOFT.ACE.OLEDB.12.0 drivers but of course these only work on 32 bit instances of SQL Server.  I've found that after I've installed the beta that the connections wont work if I use MICROSOFT.ACE.OLEDB.14.0 as the provider.  However if I use MICROSOFT.ACE.OLEDB.12.0 it works... sort of... I can import data a few times then I get a "The provider reported an unexpected catestrophic failure" error and the app hangs.My environment is 64-bit Windows 7 running VM Workstation, with a 64-bit Windows 7 guest running SQL Server 2008 64 Bit.  Essentially I'm running the following which works... occasionally getting the failure above select top 50 * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=c:\temp\captell;HDR=Yes', 'SELECT * FROM [DASDGIGS0013046591395.TXT]')and select top 50 * FROM OPENROWSET('Microsoft.ACE.OLEDB.14.0','Text;Database=c:\temp\captell;HDR=Yes', 'SELECT * FROM [DASDGIGS0013046591395.TXT]')Which doesn't work, returning the "OLEDB provider ... has not been registered" message.So I'm thinking that the drivers are 64-bit drivers but the registration process during installation is using

Does "Microsoft OLE DB Provider for SQL Server" get different versions for SQL2005 and SQL2008?

We know SQLServer OLE DB have two driver types: SQL Native Client and Microsoft OLE DB Provider. I know The former one have different versions for SQLServer2005 and SQLServer 2008 (SQLNCLI.DLL, SQLNCLI10.DLL), right? And for latter one, does it have different versions? I only got SQLOLEDB.dll under system32 folder, but have no idea whether it have different versions. Can some one help me?   Thanks Spring  

Microsoft ole provider for oracle is not available in my VS 2008 list of providers when setting up C

Hi, Trying to set up a connection manager using Microsoft OLE Provider for Oracle, but it is not in the list of Providers. I do have Oracle Provider for OLE DB. But I need the former. I tried installing MDAC 2.8 but I get a message saying that it cannot be installed because it is part of the OS already. So, how do I get Microsoft OLE Provider for Oracle as an available provider? Thanks!--ACG

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access

Hi,I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');The following error is comming:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.Msg 7350, Level 16, State 2, Line 1Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".Can any one suggest what should I do to resolve?Thanks,Parmeshthee. Learning .Net 3.5

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,   I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.   It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error: "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied. You must access this provider through a linked server."   I am using VB 6.0 as front end. Is there anyway i can overcome this error?   Please help.   Dhiraj

The CodeDom provider type "Microsoft.VJSharp.VJSharpCodeProvider, VJSharpCodeProvider, Version=4.0.0


I get this error: The CodeDom provider type "Microsoft.VJSharp.VJSharpCodeProvider, VJSharpCodeProvider, Version=, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" could not be located.  

I checked out these posts: http://forums.asp.net/p/1562439/3875469.aspx and http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/4c6fce15-c7d1-43e9-a7d5-5eeb6da2a70b

And added this to my web.config:

      <compiler language="vj#;vjs;vjsharp" extension=".jsl;.java" type="Microsoft.VJSharp.VJSharpCodeProvider, VJSharpCodeProvider, Version=, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A">

But still I cant build my website....please help!

Getting Error "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered". when importi



I am using below sql command to import the excel file (created in excel 2007). 
SELECt * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\File1.xlsx', 'SELECT * FROM [Sheet1$]');

This command is working fine on the machine which have the office 2007. But, I am getting following error message when I run this command on the server on which the office 2007 is not installed:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Not able to see microsoft oledb provider for oracle in sql server 2005 providers



can any one help me, environment window server 2003  64 bit and sql server 2005 sp3 64 bit and i have installed oracle client  10g 32 bit. problem is, I am not able to see the provider name  while creating linked server.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered


Hi All,

I am getting the below error while executing the opendatasource statement

SELECT ParamName, ParamDate,ParamNumber,ParamChar

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source= c:\testcmd\misc\CSTARIntegrationParameters.xls;Extended properties=Excel 8.0')...[CSTARIntegrationParameters$]

Error :

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

Enviromnet :

Windows 2003 64 bit with SP1 and sql 2005 installed.

Same code is executing fine in my local machine which is XP 32 bit.

Can some one help me in resolving this issue.

Also when I tried to check the providers by expanding the linked servers node in the SQL server management studio . i couldnt found the provider for jet.






Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data.OleDb;

using System.Data;

namespace WebApplication1


    public partial class WebUserControl1 : System.Web.UI.UserControl


        protected void Page_Load(object sender, EventArgs e)



        protected void Button1_Click(object sender, EventArgs e)


            OleDbDataAdapter myCommand = null;

            DataSet myDataSet = null;


            DataTable dtExcelInfo = null;


SSIS Dynamic Connection Provider



I am working on SSIS 2008. My requirement is that I need to connect to SQL Server or Oracle datbase in lookup, but I will get to know this at runtime whether its SQL Server or Oracle. So one option is to create separate connection for each and use separate lookups. But my question is can I use only one connection and one lookup and achieve this? I short can I can change oledb connection provider at runtime?




Issue using FastLoad option in Microsoft OLEDB Provider for DB2 v3.0


Hi All

We are migrating a few DataStage jobs to SSIS 2008 and below is an issue that is bothering me.

I am trying to use the FastLoad option of the Microsft OLE DB Provider for DB2 v3.0 but have been unsuccessful. I am trying to run a simple package that loads the data from one DB2 table to another without any transformation.

The reason I am exploring the fast load is because the normal option of OpenRowset does not perform as well the DataStage job to load the same amount of data, its almost 10 times slower.

DB2 version 9.5 Fixpack 5

SSIS 2008

Error msg

[OLE DB Destination [79]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft DB2 OLE DB Provider"  Hresult: 0x80040E14  Description: "An internal network library error has occurred. A network level syntax error has occurred. SQLSTATE: HY000, SQLCODE: -379".

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (79) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (92). The identified component returned an error from the ProcessInput method. The error is specific

Running SSIS job step as Proxy account does not find log provider for windows event log

When I run a ssis job step using a proxy account i get this error message:

Description: The log provider type "DTS.LogProviderEventLog.2" specified for log provider "SSIS log provider for Windows Event Log" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.  End Error  Error: 2010-02-02 14:43:03.88     Code: 0xC0010021     Source:       Description: Element "{028F8760-7E28-4F62-A204-66F33F4064BE}" does not exist in collection "LogProviders". 

If the account is a member of administrators group on the sql server the job step works fine.

Since the logprovider DTS.LogProviderEventLog.2 exists on the system I guess that this has something to do with access rights, probably to some registry key(s), but I cannot find out which part of the registry the proxy user does not have access to.

When running the same package using DTEXEC in a Command Prompt and using RUNAS <proxyuser> the package works fine and writes to the event log.

Kind regards


update 17-Feb-2010: Microsoft has reproduced this situation and I hope to get a workaround or fix soon :-
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend