Creating SSIS Packages

Introduction
     In this article, I will explain that how many ways to creating packages.
Description
     By using two ways to create package

•   By using the built in Import and Export Wizard in SQL Server 2008, which asks you about moving data from source to a destination and then automatically generates an SSIS package. After you create a package in the wizard, you can execute it immediately, schedule it, or associate it with an SSIS project.
( How to Import data by using Import & Export Wizard )

•   By explicitly creating a package inside an SSIS project in BIDS. BIDS in SQL Server 2008 uses the Microsoft Visual Studio 2008 interface. Within the BIDS development environment, you first create as SSIS project and then create and develop new packages.
 ( Creating SSIS Project in BIDS )

Creating an SSIS Project in BIDS

Creating an SSIS Project
          Although the Import and Export Wizard is useful for generating a quick package that moves data from one source to one destination, these packages are frequently use one time of solution only at the time of starting point. But presently we will need to either develop a package that has more complicated requirements or create a set of coordinated packages. That reason you first need to create a new SSIS Project in BIDS.

            To import data from Excel and stored into Sql server by using BIDS Development tool, we have to do the following steps to create new SSIS package.

Steps:
1. Start --> Programs --> Microsoft SQL Server 2008 R2--> SQL Server Business Intelligence Development Studio (BIDS)

2. In BIDS, choose New, Project from the file Menu. (If you have Visual Studio 2008 installed separately from BIDS, you can simply select New Project from File Menu)

3. Fill out the New Project dialog box as follows:
• Under Project Types, select Business Intelligence Projects.
• Under Templates, select Integration Services Project.
• Assign a name to your Project in the Name box.
• In the Location box, either leave the default folder location for storing new Projects (Default is \Documents\Visual Studio 2008\Projects\folder) or change to location of your choice.

4. When you have finished, Click OK to build the Project. The project contains several SSIS Objects as shown below Diagram.

5. In this above Diagram shows a new project, with the default Package.dtsx package (created with the project) in the SSIS Designer. In Solution Explorer, right click of that package and then click Rename.

6. At the time of rename of package, BIDS might prompt you to rename the package object. If a message box appears that prompts you to rename the package object is well, click Yes.

7. Click the Save button on the toolbar, and then close the package by clicking the Close button in the upper right corner of the SSIS Designer.

8. Finally you are now ready to Configure and Develop your Package.

How to Import Data by using Import and Export Wizard in Sql Server 2008

Using the Import and Export Wizard
          With SQL Server 2008, you use the Import and Export Wizard to copy data without going through the process of creating an SSIS Project. Use the following considerations to determine which part of the wizard to use:
•  Importing data with the wizard lets you bring data from various sources like flat files, data in Microsoft Office Excel spreadsheets or Microsoft Office Access databases or data in Oracle databases into a SQL Server tables.
•  Exporting data with the wizard lets you send data from SQL Server tables, views or custom queries to flat files or another database.

To import data from Excel and stored into Sql server by using Import and Export Wizard in SQL Server 2008, we have to do the following steps.
Steps:
1. Start --> Programs --> Microsoft SQL Server 2008R2 --> SQL Server Management Studio (SSMS)
2. Open SQL Server Management Studio
3. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)

4. Click Connect to connect to the server from SSMS
5. Right-click on the name of the database instance you wish to use and select “Import Data” from the Tasks menu

6. Click Next to advance past the wizard’s opening screen

7. Choose Microsoft Excel as your data source
8. Click the Browse button, locate the abc.xls file on your computer, and click Open

9. Verify that the “First row has column names” box is checked

10. Click Next to advance past the Choose a Data Source screen
11. On the Choose a Destination screen, select SQL Server Native Client as the data source
12. Choose the name of the server that you want to import data into from the Server Name drop-down box.
13. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.

14. Choose the name of the specific database you want to import data into from the Database drop-down box
15. Click Next to continue
16. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen

17. In the Destination drop-down box, choose the name of an existing table to import the data into a table that already exists in your destination database or type the name of a new table that you wish to create. In our example, we will use this Excel spreadsheet to create a new table called “Sheet1”

18. Click Next to move past the Select Source Tables and Views screen is displays by click on Preview Button.

19. Select both Check boxes of Run Immediately & Save SSIS Package then Choose File System and select “Do not save sensitive data” in Drop down list

20. Click Next to save package in Specific location with specific name.

21. Click the Finish button to skip ahead to the verification screen
22. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.

23. It shows Execution status screen, click the Close button.

24. Finally to check data in SSMS (Sql Server Management Studio) as shown below.

         In general, the import and Export Wizard provides a quick way to move data from one Source to Destination for one time use, but there are some limitations:

•  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.

By overcome above limitations you need to develop a new package from scratch in BIDS (Business Integration Development Studio)