\nA frequently asked question since SQL Server 2005 shipped is "how do I expose my custom data source to SSIS\/SQLRS\/your app here". If you have a data server that you must connect to, and it accepts commands and returns resultsets, its a nature fit for an ADO.NET data provider-based mechanism. Having worked with OLE DB provider writers for a few years, I'm always surprised that its quite straightforward to write an ADO.NET data provider. As an example, the ATL OLE DB Provider Wizard uses a skeleton starter provider implementation in atldb.h that weighs in at over 12000 lines of code. A similar skeleton for a ADO.NET data provider is a hundred or so lines.\n<\/p>\n
\nI originally built an ADO.NET data provider for version 1.0 that simply accepted a command that consisted of a file path and returned file information as a rowset. I'd upgraded the provider for ADO.NET 2.0 without adding much, just restructuring to use base classes instead of interfaces. Today I added enough of the "additional classes" to make it useable in SSIS. Haven't tried much else yet. I had to add two more skeleton classes: a ProviderFactory and ConnectionStringBuilder.\n<\/p>\n
\nThe provider factory connects is the glue between the machine.config info that is used as a "provider enumerator" and the provider itself. You need to:
\n1. Either register the provider in the GAC or make it available to each client.
\n2. Add "provider configuration" entry for it, either in machine.config or in each client's application configuration file. The console app has a specific entry in app.config, for SSIS its probably easier to put it in the GAC.\n<\/p>\n
\nSo that SSIS would accept the connection string (which the provider really doesn't use), I added a dummy property, DataSource. SSIS doesn't like empty connection strings. The provider, test console app, and test SSIS project (data connection only) is attached.\n<\/p>\n
\nAlthough the provider doesn't do much (many of the methods are simply stubs) it may be a useful "starter" providing the scaffolding. Let me know if this is useful and I might do some work to expand it further. (Apr 17: Updated the provider based on feedback in this blog thread) Enjoy!\n<\/p>\n