Creating projects and solutions with SSIS

By: Ray Barley

Overview

The BIDS paradigm for developing SSIS packages is based on the concept of projects and solutions.  A project is a container for one or more SSIS packages.  Projects can be used to organize SSIS packages in a meaningful way.  For instance if I have a number of SSIS packages that are used to load a data warehouse from a particular source system, I might create an SSIS project for all SSIS packages that are used in the ETL process for that source system.  I might create other SSIS projects for the various other source systems that are used to populate the data warehouse. 

A solution is a container for one or more projects.  When you create multiple SSIS projects to organize your packages, you combine those packages into one or more solutions.  Continuing with my example above, if I have several SSIS projects that each represent a source system used to populate a data warehouse, I might create a single solution to hold all of these related SSIS projects.  In this section we will walk through creating a project and a solution with BIDS, adding a new project to our solution, and adding an existing SSIS package to a project.

Explanation
To begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, New, Project on the top level menu to display the New Project dialog.  Select Business Intelligence Projects as the project type, then Integration Services Project as the template; fill in the dialog as shown below:

After creating this new project and solution, I can navigate to the samples folder in Windows Explorer and see the following:

The samples folder holds my solution file (samples.sln) and contains the Tutorial-Sample-1 folder which holds my project.  The samples folder was created as a result of clicking Create directory for solution.

Returning to BIDS you will now see the following in the Solution Explorer which is located in the top right corner of the window:

By default a new SSIS package is added when you create an Integration Services Project; you can right click on it and rename it.

Now that we have successfully created a new project and a solution, let's add another new project to this solution.  Click the top level File menu in BIDS then Add, New Project and fill in the Add New Project dialog as shown below:

I have created this additional project to hold the SSIS packages created by executing the Import and Export wizard in SSMS.  This will create an SSMS-Samples folder to hold our new project; this folder will be underneath our samples folder.  I would like to suggest that you pay attention to what you're doing while creating new projects.  Make sure that the location and name are what you want.  The default values that are automatically filled in are rarely useful.  You can easily create a project and not know it's name or location.

Now when we look at the Solution Explorer in BIDS we see the following:

Our solution now contains two projects.  The SSMS Import and Export wizards provide the option to save your work as an SSIS package.  If you choose to save your work, you may want to add the package to an existing project.  Right click on the SSIS Packages node in the Solution Explorer of the SSMS-Samples, select Add Existing Package, and fill in the dialog as follows:

As stated in the title of the dialog, you are adding a copy of the SIS package to the project.  The SSIS package is copied from the package path you specify into your project's folder.