Creating a Package by Running a Wizard
Typically, you create a new Integration Services package by creating a new Integration Services project, which contains one package by default, or by adding an empty new package to an existing project.
However, you can also use a wizard to help you create a new package. Both the SQL Server Import and Export Wizard and the Integration Services Connections Project Wizard can create a package for you. You can use the factors in the following table to determine which of these wizards is most appropriate for your requirements.
SQL Server Import and Export Wizard
Integration Services Connections Project Wizard
Create a package that performs a simple import or export between a single source and destination.
Create a package that may connect to multiple sources and destinations, and to have the wizard configure the required connections for you.
Starting the wizard
From the Start menu or as a context-menu option in either Management Studio or BI Development Studio.
For more information, see How to: Run the SQL Server Import and Export Wizard.
From the New Project dialog box in BI Development Studio.
For more information, see How to: Create a New Integration Services Project.
A single package that can be saved to SQL Server, the SSIS Package Store, or the file system.
A new Integration Services project in BI Development Studio that contains a single package.
Contains the connection managers, source, any transformations that are required, such as Data Conversions, and the destination. Optionally, the package might contain Execute SQL tasks to create, drop, or truncate tables.
Contains the connection managers, sources, and the destinations that you selected.
Complete and can be run immediately. However, you must add the package to an existing Integration Services project before you can change the package or run the package in BI Development Studio.
Not complete and cannot be run immediately. Before you can run the package, you have to open it in BI Development Studio to add any required transformations, and connect and configure the components in the data flow.
The SQL Server Import and Export Wizard creates a complete package for a simple import or export. This wizard configures the connections, source, and destination, and adds any data transformations that are required to let you run the import or export immediately. You can optionally save the package to run it again later, or to refine and enhance the package in BI Development Studio. However, if you save the package, you must add the package to an existing Integration Services project before you can change the package or run the package in BI Development Studio.
The Integration Services Connections Project Wizard creates a new package that contains the data sources and destinations that you specify, and the connection managers that those sources and destinations require. In configuring the connection managers, the wizard reduces the challenge of selecting the appropriate data provider. However, after you run the wizard, you cannot run the new package immediately. You have to finish configuring the data flow by adding transformations and connecting them to the preconfigured sources and destinations. For example, even to perform a simple export, you might have to add a Data Conversion transformation between source and destination.
For information about how to add transformations to the data flow, see the following topics:
For information about how to connect components in the data flow, see the following topics:
For troubleshooting information, see Troubleshooting Package Development.
Video, Exporting SQL Server Data to Excel (SQL Server Video), on technet.microsoft.com