OLEDB is the native client for SQL Server. Every SSIS developer is familiar with how to use it. Almost every task in Integration Services can use an OLEDB connection. As a matter of fact, it’s a preferable client to use if your backend is SQL Server.

However, there is one exception, that is the Transfer SQL Server Objects Task. This task uses an SMO Server connection only.

If you don’t know this before you use this transfer object task, there can be confusions during development and also during deployment.

Here are my lessons learned.

1. Visual differences between the OLEDB and the SMO Server connections in Integration Services 2012 helps

In SSIS 2012, I am happy to see the visual differences. If you are still stuck in 2008, you will not see any visual differences.

2. Name your SMO Server connections properly

A good practice is to name the SMO Server connection properly. I used such names as:

• SMOServer_Staging (this could be the source server for the transfer task)
• SMOServer_Reporting (this could be the destination server for the transfer task)

3. Rename the SMO Server connection properly

In SSIS 2012, you have three difference places to create connections.

• At project level: connections are available to all packages in the project
• At package level: only available to the packages in which the connection is created
• At task level: also only available to the packages in which the connection is created

The best practice is to create connections at the project level.

However, no matter where you are creating the connections, the UI in the SSIS always prefix the connection with the server name:

• For OLEDB – servername.databasename: removing the servername is usually the best practice here, leaving just the database name.
• For SMO Server – servername: since we cannot just simply remove the servername, the best practice here is to rename it to what is suggested in the section 2.

4. Confusions during deployment over where to change to production/test server

In 2008 , during deployment, I’ve seen DBAs done the following:

• Create new connection: because of the confusions about OLEDB vs. SMOServer connections, and even bigger confusions if your connections have your development server name in it, DBAs would most likely choose to create new connections.
• Rename the connection name: again if you have not named your SMO connections properly, they will tend to rename it.

Neither of the above practices is good. If you as a developer have done if properly, DBA’s will not need to mess too much with the connections:

• Named your SMO Server connections properly so they know the differences with the OLEDB connections.
• Your connection names do not have your development server name so they don’t need to rename it.

5. In 2012, the deployment is a lot easier

In 2012, deployment is much more straightforward because

• Connections can be created at project level: there is only one place to mess with the connections, including the connection name, server name and database name, and bunch of other connection properties
• Of cause you can take even greater advantages of many features such as project level parameters if you have chosen to deploy packages to SSIS Catalog

More about SMO connection manager

SMO Connection Manager

Quote from the MSDN article:

An SMO connection manager enables a package to connect to a SQL Management Object (SMO) server. The transfer tasks that SQL Server Integration Services includes use an SMO connection manager. For example, the Transfer Logins task that transfers SQL Server logins uses an SMO connection manager.

When you add an SMO connection manager to a package, Integration Services creates a connection manager that will resolve to an SMO connection at run time, sets the connection manager properties, and adds the connection manager to the Connections collection on the package. The ConnectionManagerType property of the connection manager is set to SMOServer.

You can configure an SMO connection manager in the following ways:

• Specify the name of a server on which SQL Server is installed.
• Select the authentication mode for connecting to the server.

More about Transfer SQL Server Objects task

Besides, the data flow object, the Transfer SQL Server Objects task is another task you can use to transfer not just data but also other types of objects between instances of SQL Server. For more info, check out this MSDN article.

Transfer SQL Server Objects Task