Did you know you can copy and paste connection managers in SSIS? While I haven’t found this a huge benefit with OLE DB connections it can come in very handy for flat file connections. Here is an example I ran across recently.

The task:

Create a SSIS package to unload the data from a large table (5gb or so) into a text file.

Move the file to an alternate location.

Create a SSIS package to load the data from the text file into a new table.

I’m going to use Adventureworks2008.HumanResources.Employee as my sample table.

1. In BIDS create a new package. BIDS = Business Intelligence Development Studio = Visual Studio with a few additional project types.
2. In the package create an OLE DB connection manager called MySource and a dataflow object. In the dataflow create an OLE DB source also called MySource and set the OLE DB connection manager property to the MySource connection manager. Also create a Flat File destination called MyDestination. Link the source and the destination together.

   

3. Open the properties for MyDestination.
4. Click on the New button next to the Flat File connection manager drop down.
5. Select Delmited and OK.

   

6. Fill in the following on the new Flat File Connection Manager Editor
   1. Connection manager name: MyDestination
   2. File name: c:\test.txt (making sure that the file does not already exist)
   3. Check column names in the first data row

   

7. Hit OK
8. Click on the Mappings tab, then OK.

At this point you have a package that will export the table Adventureworks2008.HumanResources.Employee to the file C:\test.txt. If you look at the connection manager MyDestination and go to the Columns and Advanced tabs you will find all the column information already filled in. This is because SSIS already had enough information from the source table to take care of this for you. On the off-chance you’ve never tried to set up a complicated flat file definition in a connection manager let me assure you this is a HUGE help!

Next the package that loads the data into another table.

1. In Adventureworks2008 run the following query:

   SELECT TOP 0 *
   INTO dbo.CopyConnectionManagerTest
   FROM Adventureworks2008.HumanResources.Employee

2. In BIDS create a second package. (Don’t close the first package.)
3. In the package create a dataflow. In the dataflow create an OLE DB Destination called MyDestination and a Flat File source called MySource. Link the source and the destination together.

   

4. In the first package right-click on the flat file connection manager called MyDestination and select “Copy”.

   

5. Go back to the second package and right-click on the connection manager area and select “Paste”.

   

6. Rename the connection manager to MySource and assign it to the MySource OLE DB Source.
7. Copy and paste the OLE DB MySource connection manager from the first package to the second. Note: It will be named “MySource 1” because we already have a MySource connection manager.
8. Rename the MySource 1 connection manager to MyDestination.

   

9. Open the properties of the MyDestination and fill them in.
   1. OLE DB Connection Manager: MyDestination
   2. Name of Table or View: dbo.CopyConnectionManagerTest

   

10. Select the mappings tab to initialize the mappings then hit OK.

At this point we have finished both tasks. Please note the results of being able to copy the connection managers. There wasn’t a huge savings in time for the OLE DB connection manager because they are fairly quick to set up. The flat file connection manager on the other hand was a HUGE savings in time. As I said above, if you have never tried to set one of these things up yourself this is BIG! All of the column definitions are created for you. Name, length and data type are pre-filled in. This means, among other things, that your mappings should go very smoothly, with a minimal amount of user intervention.