Friday, February 22, 2008

Using Connection Managers in SSIS

Whenever I create SSIS packages, I typically create two different Connection Managers for use against SQL Server databases. The first uses a standard OLE DB provider, like this one to the AdventureWorks database (which I typically name something like AdventureWorks.OLEDB):

AdventureWorks.OLEDB

The second one, to the same database, uses an ADO.NET provider, like this one to the AdventureWorks database (which I typically name something like AdventureWorks.ADONET):

AdventureWorks.ADONET

Now, here's why I set up these two different Connection Managers:

The OLE DB provider seems to be faster than the ADO.NET provider for getting data into and out of a SQL Server database. Thus, in Data Flow tasks within the package, I use the first Connection Manager for OLE DB Source and OLE DB Destination components when hitting the database. Thus:

Data Flow Task

However, as anyone who has used the Execute SQL task along with an OLE DB-based Connection Manager will tell you, dealing with stored procedures and their parameters isn't the most elegant. If the Execute SQL task uses an OLE DB-based Connection Manager, then calls to stored procedures look like this:

Stored Procedure with Positional Parameters

In the Parameter Mappings tab of the task, the parameters then have to be specified using positional numbers, starting with 0. So, for the example above, you'd have to set up mappings to parameters as follows:

Positional Parameter Names

If, on the other hand, you use an ADO.NET-based Connection Manager with the Execute SQL Task, things are a bit more elegant. In this case, you can simply specify the name of the stored procedure without any positional parameter markers:

Stored Procedure Name Only

Note the property called IsQueryStoredProcedure below the name of the stored procedure. When an OLE DB-based Connection Manager is used (as in the first example above), this property is set to False and is grayed out. Now, however, with an ADO.NET-based Connection Manager, the property can be set to True whenever a stored procedure name is provided. As for the parameters, they can now be mapped using the actual parameter names within the stored procedure:

Actual Parameter Names

To me, this is much more intuitive and elegant. And, when you build lots of SSIS packages (for an ETL process, for example), you almost always have more than a few stored procedure calls that need to be made before Data Flow tasks, after them, in Event Handlers, etc. Thus, although it seems somewhat silly to have two Connection Managers to the same database, using one for each of the different providers for SQL Server allow you to have the best of both worlds -- good performance for getting data into and out of a database, and good (or at least better) stored procedure handling for parameter names and mapping.

1 comment:

Denis said...

Hy,

Very useful tip!
I had a multi step Stored Procedure that was not working completely (it gave 100% done, but the result was not right) with the normal OLE DB connection. I could not figure out why until I found the solution here.
Thanks for your help