Package re-integration with project & best practices RRS feed

  • Question

  • Hi folks,

    I developed some packages in SSIS and deployed them by copying the package .dtsx files to the file system directory for SSIS. Since then, changes have been made to the deployed packages but not to the original project. I'd like to get things back in order and keep them that way. We are a small installation running SQL Server 2008 standard on one server, and have precisely 2 people who develop packages. We do not have any kind of IT infrastructure or process for things like change control, source code checkin/checkout or the like. Therefore 2 questions:

    1. How can I get the deployed packages into a project so I can update the project and re-delpoy the package after changes? (I've tried to create a new project and add the existing packages, but that resulted in some connections between tasks being broken)

    2. What are (or where can I go find) best practices for maintaining and deploying SSIS packages and projects in a production environment?

    Any help/advice is appreciated.


    Friday, April 15, 2011 4:09 PM


  • Garth:

    Unfortunately, you will find that if you re-import a package that was developed WITH Shared Data Sources into ANOTHER Project, you are going to lose the connection between the Connection manager and Shared Data Source. But try these steps anyway:

    • Create a Project on a drive where both developers can get to it.
    • Import packages from the file system
    • Now create Shared Data Sources using the same name(s) as the Connection Manager(s)
    • Double-click on one of the Connection Managers and see if it picks up the properties of the Shared Data Source. Note that the icon for a Connection manager will change if it has managed to connect itself to a Shared Data Source in the Project.

    Also, I would encourage you to deploy to the MSDB database, look into Package Configurations, and also learn about the Package Protection Level property and its setting, especially how it relates to saving of passwords (for Connection managers) in the package.

    More here: http://toddchitt.wordpress.com


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Proposed as answer by Todd McDermid Friday, April 15, 2011 4:57 PM
    • Marked as answer by Tony Chain Sunday, April 24, 2011 10:27 AM
    Friday, April 15, 2011 4:47 PM