Introduction:
A Package is SSIS can be defined as the unit of work that is retrieved, executed and saved. A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations assembled using either the graphical design tools like Import and Export Data tools that SQL Server Integration Services provides, or build programmatically using BIDS. After the creation of package, it can be saved SQL Server, or the file system, or you can deploy the package to the SSIS server. The scope of this document is to demonstrate the steps for creating, saving and editing a package using Import Export wizard.
Creation of a Package in SSIS using Import Export Data wizard:
Import Export Data wizard tool is provided as part of Microsoft SQL Server 2008 R2. Import and Export Wizard offers the simplest method to create an Integration Services package that copies data from a source to a destination. The following steps demonstrate the steps to create a package using this wizard tool.
1. To open the Import Export Data wizard, go to start and All Programs and select the wizard as shown in the below figure.
2. When you open the Import Export Data Wizard for the first time, the following wizard window is opened as shown in the picture. If you do not want this wizard window to open every time, you can check the “Do not show this start page again” check box and click on the “Next” button to proceed.
3. In this wizard window set the options for a data source. A Data Source in this case specifies from which the data is going to be retrieved. In this case the data source is a Microsoft SQL Data base called Adventure Works DW. So specify the Data Source as SQL Server Native Client, and fill in the appropriate values in the remaining fields and click on “Next” button.
4. Now we need to choose the destination file for the data to be stored. In this case the destination will be a text file, so give an appropriate name and mention if the file has to allow any special characters. If yes the choose Unicode check box. For this case, that is not required, so it is left unchecked. And also mention the delimiter option. The available formats are delimited. And also mention if the column names should appear in the first column of the exported flat file.
5. Since we are copying the data from existing SQL table data, choose the first radio button as shown in the picture, which simply copies the data from the specified SQL table. If you want to copy the data from a dataset or from a view, choose the second radio button and click on “Next” radio button.
6. Select the name of the source table from which the data need to be pulled to be exported, and specify the delimiter characters and click on Edit Mappings button as shown in the below figure.
7. If the destination file to store the data is chosen as an already an existing file, then the “Create destination file” option is disabled as shown in the figure. And the options for how to store the data in the existing file are enabled. Based on the requirement choose either to delete the existing rows in the destination file or append the rows. If any column name mappings are necessary while transferring the data from source file to destination file, which can be done in this window. Then Click on “OK” button.
8. This wizard window shows how to save the package that is going to be created while exporting data to destination file. If the “Save SSIS Package” option is chosen, the package can be chosen in two different ways. First one is, save the package to SQL server. If this option is selected, the SSIS package is stored to MSDB database. If the “File system” option is selected the SSIS package is stored with .dtsx extension in the same location as where the exported flat file will be saved. You may also select the Package Protection Level and supply values for user name and password and click “Next” button.
9. Now, Set the name and description for the package and specify the applicable authentication and click on “Next” button.
10. Now on completion of creation of package, summary report will appear. Now click on “Finish” button.
11. This window shows how many rows of data is transferred to destination file as shown in the below picture.
Editing the SSIS Package:
When it is needed to edit or debug the package, the following steps are followed.
1. After creating a package using the Import Export wizard tool as described in the document so far, if it is needed to edit the package, Create a new project in SQL Server Data Tools (SSDT) as shown in the below picture.
2. In the Solution Explorer of SSDT, right click on SSIS Packages, and choose an option to add an existing package as shown in the below picture.
3. The following wizard window appears in which supply the server name and package name and click on “OK” button.
4. Now you can click on the package name and package is ready to be edited as shown in the below picture.
5. Make the necessary changes to the package and save it and close the package.
That is it guys .. Try it 🙂
Regards,
Roopesh Babu V
