My next tip is related to a post that I created back in February 2008 (more on that in a minute). This tip has to do with Connection Manager names. When you create a new Connection Manager, the resulting object is typically named with two parts – the server name and the database name. So, for example, a Connection Manager that connects to the FinMart database on a server named DevTeam01, that Connection Manager’s name will be DevTeam01.FinMart as shown below:
Now, this might seem fine – until you get ready to move this package to a different server for testing, QA, or even production use. At that point, the Connection Manage name might be very misleading since it may no longer connect to the DevTeam01 server. So, the first part of this tip is to remove the server name from your Connection Manager names…
Now, back to that February 2008 post. That post recommended that you always create two Connection Managers for use with SQL Server databases. The first uses an OLE DB provider (either the SQL Server Native Client or the Microsoft OLE DB Provider for SQL Server) while the second uses an ADO.Net provider (using the SqlClient Data Provider). In the post, I discussed using the first Connection Manager (based on the OLE DB provider) within Data Flow tasks and using the second Connection Manager (based on the ADO.Net provider) for Execute SQL tasks (since it is easier to work with parameterized queries like calls to stored procedures). I still stand by that tip and continue to do that in just about every package I develop to this day (perhaps that will change in SSIS 2012 – I just haven’t made that leap yet on with my current project).
So, in keeping with the naming aspect of this tip, I suggest naming your Connection Managers using two-part names – the first part reflects the database name and the second part reflects the provider being used. Thus, in the example above, if that Connection Manager originally created was based on an OLE DB provider, I would name it FinMart.OLEDB. And then I would add a second Connection Manager to the same database using an ADO.Net provider and name it FinMart.ADONET. So my package would have these two (at least) Connection Managers:
By having both Connection Managers in place, I can easily know which one to use for which task and have the best of both worlds (working in Data Flows and with the Execute SQL task…)