Use the OleDbConnection, Luke! (in Integration Services)

  • Article

I've consulted in more than my fair share of Integration Services (SSIS) engagements over the last year or so since Yukon hit the streets. (Don't get me wrong, I love SSIS! But... I'd like to write some MDX every once in a while, if you know what I mean.) In every single SSIS engagement, I've been seeing a trend in Script Task and Script Component coding that concerns me. I'd like to point out that there's a "better way" IMNSHO.

For whatever reason, .NET developers seem to know only the System.Data.SqlClient namespace backwards and forward – which leads them to use it in Script Tasks & Script Components. The SqlClient classes rock! They're my preference in pure, nothing-but-.NET code. However, which co-mingled with SSIS, this causes one of two goofy things (or both) to happen:

  1. Duplicate storage of connection strings:
    1. One for the OLEDB SSIS Tasks and
    2. One for the SqlClient classes
  2. A lot of hokey-pokey to convert the stored OLEDB connection string into the connection string for the ADO.NET SqlClient classes (to remove Provider, etc).

Instead of doing one or both, why not just stop using the SqlConnection, SqlCommand and SqlDataAdapter inside SSIS Script Tasks, Script Components and custom components? A few simple changes to your script code (and custom task code!) will simplify all this to having to manage only ONE copy in ONE format of your connection string. Start with this one little change:

Imports System.Data.OleDb

 

The System.Data.OleDb namespace is so similar to the SqlClient namespace that the only changes that will probably be required in your code is a Find-and-Replace of SqlClient with OleDb to leverage the equivalent power of the the OleDbConnection, the OleDbCommand and the OleDbDataAdapter. Here's an example. [Pardon my C# that came from a custom component for populating a strongly-typed dataset.]

OleDbConnection cnx = new
OleDbConnection(connectionString);

OleDbCommand cmd = new
OleDbCommand("[myschema].[fetch_MultipleTablesWithOneSproc]", cnx);

cmd.CommandType = CommandType.StoredProcedure;

OleDbDataAdapter oda = new
OleDbDataAdapter(cmd);

oda.TableMappings.Add("Table", ds.OneOfMyTables.TableName);

oda.TableMappings.Add("Table1", ds.AnotherOfMyTables.TableName);

oda.Fill(ds);

cnx.Close();

 

As always, this is just my opinion, not an official pronouncement by anyone but me, and YMMV. Keep it fun!