SSIS: How To Create a SSIS Package

A package within SSIS is the core object that contains the business logic to handle the data extraction, manipulation, and transformation. You use SSIS packages to move data from sources to destinations, and handle data processing in the mean time.
Creating a package is the first step in getting to work with SSIS. There are two ways to create a package:

1. By using Import And Export Wizard in SQL Server 2008.
2. By creating a package inside an SSIS project in BIDS.

Lets go over those two methods in more detail.

Using Import And Export Wizard in SQL Server 2008

When you create a package with the Import and Export Wizard in SQL Server 2008 you don't have to go through the process of creating a SSIS project. You can use the wizard through SSMS (SQL Server Management Studio).

Note:

• To launch SSMS, from the Start menu select --> Microsoft SQL Server 2008 and then --> SQL Server Management Studio.

 

Import And Export Wizard helps you to either import or export data.

• Importing data lets you bring into a SQL Server table any data from other SQL Server databases, flat files, Microsoft Office Excel spreadsheets, Microsoft Office Access databases, and Oracle databases.
• Exporting data lets you send data from SQL Server tables, views, or custom queries to flat files or database connections.

 To start Import And Export Wizard follow these steps:

1. Through SSMS, connect to the instance of the SQL Server 2008 Database Engine that contains your source or your destination.
2. Open Object Explorer.
3. Right-click the database that you want to use as your source or destination.
4. Click Tasks. If the database is the source of data that you want to send to a diffrent system, select Export Data. If the database is the destination for files that are outside the system, select Import Data. See Figure below:

 

 

The wizard will walk you through several pages of questions. Lets follow the steps for Import Data Task here:

1. Choose A Data Source page. Here you specify where your data is coming from, such as SQL Server database, a flat file, Exel file, Microsof Office Access database, Oracle database.
   
   
   
2. Choose A Destination page. Here you specify where your data will be sent. The data destination settings will match those of the database you selected prior to starting the Import Data wizard, but you can change those.
   
3. Specify Table Copy Or Query page. Here you can choose to copy one or more tables and views from the source, or you can write your own query (if you selected a relational database source that allows custom queries).

• If you choose Copy data from one or more tables and views, the next page named Select Source Tables And Views will enable you to select from the list of tables and views. You can choose more than one table and/or view.
  
  
• If you choose to specify a query, an additional page, named Provide a Source Query, enables you to enter the query.
  
  
  In the next page, Select Tables And Views, only your query will be available as a choice. The same thing will be true when your source is a flat file; only the flat file will be available as a choice.
  
  
  Also on this page, you can rename the destination table and edit the column mappings by clicking the Edit Mappings button to define column NULL settings, identity insert, and whether the table should be dropped and recreated every time.
4. Save And Run Package page. You can run the package immediately and/or save it for later execution.
   
   

 

If you save the package, you can later go back and edit it by using SSIS Designer. In other words, you open the package within an Integration Services project in BIDS. You can create the package by using the wizard but you can NOT edit it through the wizard. You can restart the wizard and recreate the whole package from the begining, or you can edit the package in BIDS.

 

So, in a last note, here are some limitations to be aware of when using the wizard:

• You can specify only one source and one destination in the wizard.
• Advanced workflow precedence is not available through the wizard.
• The wizard does not share data sources with other packages.

So, the wizard allows you to build a simple package that moves data from one source to a destination without any complex transformation in between. If you want to add advanced logic create the package within an Inegration Services project in BIDS.

SSIS: How to Create a SSIS Project in BIDS

The interesting thing, when you start working with SSIS, is that you don't have to start by creating a project first. You can just go to SQL Server Management Studio (SSMS) and use Import and Export Wizard from there to create a package. Normally the Import and Export Wizard is useful for generating a quick package that moves data from one source to a destination. But if you want to create a package that has more complicated requirements or if you want to create a set of coordinated packages, you need to create a new SSIS project in BIDS.

Notes:

• To Launch BIDS, from Start menu --> select Microsoft SQL Server 2008 --> and then SQL Server Business Intelligence Development Studio (BIDS).
• BIDS in SQL Server 2008 uses Microsoft Visual Studio 2008 interface with specific templates to create Business Intelligence (BI) objects.

To create a new SQL Server Integration Services (SSIS) Project follow those steps:

1. In BIDS, select File / New / Project:

     2. In the New Project dialog box:

• Under Project Types, select Business Intelligence Projects.
• Under Templates, select Integration Services Project.
• In the Name box type the name of your project.
• In the Location box, either leave the default folder location ( ..\Documents\Visual Studio 2008\Projects\ folder) or change it to a location of your choice.

     3. Click OK to build the project.

Look at the Solution Explorer in BIDS and notice that under the project that you just created you will have a few logical folders created automatically as well as a new SSIS package named Package.dtsx.

You can rename the Package.dtsx by right clicking and choosing Rename.
Now you can configure and develop your package.