In this blog post I will show you a simple example on how to create using SSIS 2012 OLE DB Connection in Connection Manager. This tutorial aims for beginners and will be used to support other SSIS articles so we don't have to repeat the same steps.
What is OLE DB Connection? In short OLE DB allows us to connect to various sources and does the hard work so we can focus on just writing the query. For more information visit http://en.wikipedia.org/wiki/OLE_DB
For the purpose of this tutorial we will use AdventureWorksDW2012 which is a Microsoft Sample database that we very often use in our articles. If you haven't got the database Installed and you would like to use it please check our article SQL Server 2012 AdventureWorksDW2012 Download and Install
The example in here are created using SSIS 2012 but they should work the same way in SSIS 2008 R2, 2008 and 2005.
Create OLEDB Connection
In SSIS Project open a package from Solution Explorer Pane. In my case it is Package.dtsx. Ensure Control Flow tab is active and below you should have Connection Managers windows. Right Click empty space and select New OLE DB Connection... See below picture
A dialog box should appear. In my case Data Connection (left side) is empty but if you have created a connection before you might something available there and use if it is suitable. In my case I click New... button
and get another dialog box
In Provider: I have Native OLE DB\SQL Server Native Client 11.0 which will allow me to connect to my SQL Server 2012 (version 11) database instance.
My server name is dot (.) which is equivalent of localhost
In my case I use Windows Authentication which should work if you installed SQL Server yourself.
In Select or enter a database name I have chosen my previously installed AdventureWorksDW2012
and clicked OK
You can see we are back to the first dialog box and we can see new connection was created
I select the connection and click OK
As you can see the new connection was created
Best Practice: You can notice that connect manager has a prefix or localhost. this should be removed by renaming the connection name as this may change depending on environment and may get confusing.
For more information on how to use the connection see our SSIS Create simple package (SSIS 2008 R2 but should be very similar in SSIS 2012)