Could not get provider invariant name from connection type qualifier

[pshaffer]

Downloaded professional evaluation package and installed. Version 4.2.122.0. Windows 7 Pro x64, fully patched.
Running VS2008, fully patched.

Building an SSIS package to import data from SQLite Dbs that were created using a fairly recent version of SQLite libraries (driver not from DevArt).

Added ADO.Net connection to SSIS package, where selected the dotConnect for SQLite driver and pointed to an existing db. Test Connection worked without a problem.

Using an ADO.Net data source, I attempted to configure it to use the just created connection manager to retrieve data from a table. The "loading..." appeared, but eventually coughed up an exception about not being able to create the DBProviderFactory.

Selecting the just created connection manager entry at the bottom of the SSIS editor and right clicking "Edit", the Connection Manager editor screen comes up, but also pops an exception message box with the following text:

TITLE: Microsoft Visual Studio
------------------------------
Could not get provider invariant name from the connection type qualifier "Devart.Data.SQLite.SQLiteConnection, Devart.Data.SQLite, Version=4.2.122.0, Culture=neutral, PublicKeyToken=09af7300eec23701". You may need to manually edit the 'Qualifier' property of the connection manager if the default one selected is different from what you want.

------------------------------
ADDITIONAL INFORMATION:

Could not create a DbProviderFactory class for the provider invariant name 'System.Data.SQLite'. Verify that this provider is installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

Failed to find or load the registered .Net Framework Data Provider. (System.Data)


Looking in Machine.config on the box, I find the following entry already placed there by the installer under the <DBProviderFactories>:

<add name="dotConnect for SQLite" invariant="Devart.Data.SQLite"
description="Devart dotConnect for SQLite" type="Devart.Data.SQLite.SQLiteProviderFactory, Devart.Data.SQLite, Version=4.2.122.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />


Suggestions, Ideas, Solutions?

[pshaffer]

More information.

Trying to duplicate the SSIS development environment problem, which seems to be failing while going through the DbProviderFactories mechanism if I'm reading the error messages correctly, in a console application. The following code works correctly in all versions of x64 vx x86, and under runtimes of 2.0, 3.5, and 4.5. So this is definitely something specific to how VS2008 BIDS SSIS Connection Managers work with the dotConnect for SQLite driver.

Still looking for suggestions on how to get a handle on this problem. Help DevArt support?

Code: Select all

DbProviderFactory factory = null;
SQLiteConnectionStringBuilder csb = null;
SQLiteConnection cn = null;
try
{
	factory = DbProviderFactories.GetFactory( "Devart.Data.SQLite" );
	SQLiteProviderFactory factorySQLite = factory as SQLiteProviderFactory;
	csb = factorySQLite.CreateConnectionStringBuilder() as SQLiteConnectionStringBuilder;
	csb.DataSource = @"E:\Assurant\Discover Workspaces\Discover310\Workspaces\0e197512-a56d-4fcd-bff0-b00581330fb8\Documents\2012-03-19\Results.ddb";
	csb.ConnectionTimeout = 60;
	csb.DefaultCommandTimeout = 300;
	csb.FailIfMissing = true;
	csb.ForeignKeyConstraints = SQLiteForeignKeyConstraints.On;


	Console.WriteLine( "ConnectionString: {0}", csb.ConnectionString );

	cn = factorySQLite.CreateConnection() as SQLiteConnection;
	cn.ConnectionString = csb.ConnectionString;
	cn.Open();
	Console.WriteLine( "Server version: {0}", cn.ServerVersion );
	cn.Close();
}
catch ( Exception ex )
{
	Console.WriteLine( "An unexpected exception occured." );
	Console.WriteLine( ex.Message );
	Console.WriteLine( ex.Source );
	Console.WriteLine( ex.StackTrace );
}
finally
{
	if ( cn != null )
	{
		cn.Dispose();
		cn = null;
	}
}

[Pinturiccio]

Could you please provide the following information?
1. The moment when the exception occurs. Does it occur during the design time or when you execute the Package?
2. Detailed information about steps for reproducing the issue.
3.

Could not create a DbProviderFactory class for the provider invariant name 'System.Data.SQLite'.

Why does the exception contain the reference to System.Data.SQLite assembly? Did you use it in a SSIS package and if yes, then where?

[pshaffer]

The System.Data.SQLite ado.net provider is installed because the application that creates the SQLite databases being imported uses that driver. That driver does not support the DDEX structures necessary for SSIS to use it. That is why we are looking at your product. But the original application is not going to be rewritten, so that driver must be installed on the same machine.

Create a ssis package (brand new). Click on the bottom of the screen in the connection managers section and select "create new ado.net connection". Select the Devart dotConnect driver for SQLite. Point it at a valid SQLite db. Hit the "Test" button, it comes up and says the connection is okay. Hit the "ok" button and it's saved.

Here is where the problem occurs. Right click on the newly created connection manager and select "edit". The same screen used to create it comes up and displays the error message I posted above. Below is the XML for the package, since this forum doesn't appear to provide me with the ability to attach it as a file.

The same problem will also occur if I try to create a data flow with an ado.net data source. When I try to configure the data source to use the newly created connection, at the point I drop down the combobox to display the list of tables/views to select from the problem also occurs. The XML below does not contain the data flow to reflect this.

I will note that this problem occurs on my workstation at work. On my home workstation, where I have all the same software and drivers installed (and alot more besides), this problem does not manifest. I have not been able to figure out what differences might exist, as the machine.config files contain the same entries for the factories.

Code: Select all

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>
<DTS:Property DTS:Name="VersionComments"></DTS:Property>
<DTS:Property DTS:Name="CreatorName">FAST-TALK\pshaffer</DTS:Property>
<DTS:Property DTS:Name="CreatorComputerName">NXDEV49</DTS:Property>
<DTS:Property DTS:Name="CreationDate" DTS:DataType="7">11/26/2012 11:25:24 AM</DTS:Property>
<DTS:Property DTS:Name="PackageType">5</DTS:Property>
<DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property>
<DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property>
<DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property>
<DTS:Property DTS:Name="VersionMajor">1</DTS:Property>
<DTS:Property DTS:Name="VersionMinor">0</DTS:Property>
<DTS:Property DTS:Name="VersionBuild">1</DTS:Property>
<DTS:Property DTS:Name="VersionGUID">{8F860C6E-724C-4A4D-A088-CF1A6C85B33A}</DTS:Property>
<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointFileName"></DTS:Property>
<DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property>
<DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property>
<DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">Nexidia Discover Phrases ado.net</DTS:Property>
<DTS:Property DTS:Name="DTSID">{6B997E5F-0BDC-4FDA-9D07-134E610571E1}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">ADO.NET:Devart.Data.SQLite.SQLiteConnection, Devart.Data.SQLite, Version=4.2.122.0, Culture=neutral, PublicKeyToken=09af7300eec23701</DTS:Property><DTS:ObjectData><DTS:ConnectionManager>
<DTS:Property DTS:Name="Retain">0</DTS:Property>
<DTS:Property DTS:Name="ConnectionString">Data Source=E:\Assurant\Discover Workspaces\Discover310\Workspaces\0e197512-a56d-4fcd-bff0-b00581330fb8\Phrases.ddb;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:Property DTS:Name="LastModifiedProductVersion">10.50.4000.0</DTS:Property>
<DTS:Property DTS:Name="ForceExecValue">0</DTS:Property>
<DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property>
<DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property>
<DTS:Property DTS:Name="Disabled">0</DTS:Property>
<DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property>
<DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property>
<DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property>
<DTS:Property DTS:Name="LocaleID">1033</DTS:Property>
<DTS:Property DTS:Name="TransactionOption">1</DTS:Property>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions>
<DTS:Property DTS:Name="LoggingMode">0</DTS:Property>
<DTS:Property DTS:Name="FilterKind">1</DTS:Property>
<DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Property DTS:Name="ObjectName">Package1</DTS:Property>
<DTS:Property DTS:Name="DTSID">{EA989A5B-FFBE-4E53-BD09-3B50C4804D6E}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property>
<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:Layout><dds>
  <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="3" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="38709" y="20267" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
    <font>
      <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
    </font>
    <mouseicon>
      <ddsxmlobjectstreamwrapper binary="6c74000000000000" />
    </mouseicon>
  </diagram>
  <layoutmanager>
    <ddsxmlobj />
  </layoutmanager>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">{EA989A5B-FFBE-4E53-BD09-3B50C4804D6E}</DTS:Property>
<DTS:Property DTS:Name="DTSID">{2CEE0E0F-1CDF-425A-B816-C3EBFE1B260A}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable>
<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:Layout><dds>
  <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="3" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="38709" y="20267" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
    <font>
      <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
    </font>
    <mouseicon>
      <ddsxmlobjectstreamwrapper binary="6c74000000000000" />
    </mouseicon>
  </diagram>
  <layoutmanager>
    <ddsxmlobj />
  </layoutmanager>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property>
<DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">package-diagram</DTS:Property>
<DTS:Property DTS:Name="DTSID">{2ADD57DF-2DD3-46BD-8EF9-CF54C5FB49DD}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable></DTS:Executable>

[Pinturiccio]

Could you please provide the following information:
1. Do you have other connections in the Connection Manager?
2. Can you create a connection in the Connection Manager using another ADO.NET provider (not Devart) and then edit it?
3. Can you connect with dotConnect for SQLite in the Server Explorer?

According to the inner exception:

Could not create a DbProviderFactory class for the provider invariant name 'System.Data.SQLite'. Verify that this provider is installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

Failed to find or load the registered .Net Framework Data Provider. (System.Data)

We suppose, that Visual Studio is trying to open a connection via the System.Data.SQLite provider when opening a connection in the Connection Manager. Probably, you have already created a connection via the System.Data.SQLite provider. Then you deleted this provider, but the connection remained in Connection Manager.