Copying Connection Managers in SSIS

Leave a comment

December 10, 2012 by Kenneth Fisher

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.

    Copying Connection Managers in SSIS Pic1

  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.

    Copying Connection Managers in SSIS Pic2

  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

    Copying Connection Managers in SSIS Pic3

  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!

Copying Connection Managers in SSIS Pic4

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.

    Copying Connection Managers in SSIS Pic5

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

    Copying Connection Managers in SSIS Pic6

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

    Copying Connection Managers in SSIS Pic7

  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.

    Copying Connection Managers in SSIS Pic8

  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

    Copying Connection Managers in SSIS Pic9

  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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: