Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 31, 2007

Administering SQL Server 2005 Integration Services - Page 2

By DatabaseJournal.com Staff

Creating an Integration Services Package

Using the SQL Server Import and Export Wizard is one of the easiest ways to create a basic package. Sophisticated package creation is done through the Business Intelligence Development Studio (BIDS).

This example creates a package that will import comma-delimited data from biometric monitors for patients in a vaccination study. The biometric data consists of a patient number and a series of measurements of the patient's temperature, pulse, respiration, and blood pressure.

The blank database named BioData already exists on the SQL Server 2005 server named SQL01. The biometric data will be refreshed periodically, so you need a package to import the data for ease of scheduling. To create it, follow these steps:

  1. Open SQL Server Management Studio.

  2. Connect to the Database Engine of the SQL01 SQL server.

  3. Expand the Databases folder in Object Explorer.

  4. Right-click on the BioData database.

  5. Select Tasks, Import Data.

  6. Click Next.

  7. From the Data source drop-down, select Flat File Source.

  8. Enter the filename of the source data, in this case biodata.txt.

  9. A warning appears in the messages window, stating "Columns are not defined for this connection manager."

  10. Check the box Column Names in the first data row.

  11. Click Next.

  12. Click Next to leave the destination defaults.

  13. Click Edit Mappings.

  14. Check the box Drop and Re-create Destination Table. This setting overwrites the table each time the package is run.

  15. Click OK.

  16. Click Next.

  17. Check the box Save SSIS Package and click Next. This saves the package to the SQL Server.

  18. Enter a name for the package, in this case BioDataImport.

  19. Click Next and then Finish.

The package execution results are summarized in a window, as shown in Figure 5.1. Note that the package completed with one error, which was due to the BioData table not existing on the first run. As you can see from the figure, more than 7 million rows were imported into the table.

The package was installed to the SQL Server. To confirm this, you can connect the Integration Services instance using the following steps:

  1. In SQL Server Management Studio Object Explorer, click on the Connect drop-down and select Integration Services.

  2. Click Connect to connect with the current credentials.

  3. Expand the Stored Packages folder in the Object Explorer window.

  4. Expand the MSDB folder to see the BioDataImport package.

At this point, you should see the package in the folder.

Figure 5.1
Using the SQL Server Import and Export Wizard.

Storing Packages

Packages can be stored in the SQL Server MSDB database, SSIS package storage, or file system. Only the SQL Server storage and SSIS file system storage are managed by SSIS.

Folders and subfolders can be created in either the MSDB storage or the SSIS file system storage to help organize the packages.

Packages stored in the MSDB database are stored in the sysdtspackages90 table. Folders within the MSDB are stored as rows within the database.

Packages stored in the SSIS file system storage are stored by default in the %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Packages\ directory. Packages can be viewed and managed in these folders from the Object Explorer within SSIS, as well as the file system. Folders created in the file system are actually file folders in the directory structure.


Note - Unfortunately, packages cannot be dragged and dropped within the folder structure of the SSIS storage. You must use the export and import feature to move the packages around.


However, you can drag and drop files within the native Windows file system. When you do, the changes are reflected in the SSIS file system folder.

Importing and Exporting Packages

Packages can be imported and exported from the SSIS storage. This can be done with the Object Explorer in SQL Server Management Studio or the file system in Windows.

Packages can be exported to

  • Back up the packages

  • Move the packages to a different SQL Server

  • Include the packages in a project or solution using SQL Server Business Intelligence Development Studio

Packages can be exported to SQL Server (that is, the MSDB database), the SSIS package store, or the file system. The destination can be local or on another server, such as another SQL Server or even just a file share on another server. The saved file will have a .dtsx extension.

To export a package, such as the BioDataImport package, from SSIS to a backup file, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the MSDB folder.

  6. Right-click on a package to export, in this case the BioDataImport package.

  7. Select Export Package.

  8. Select the destination of the package, in this case File System.

  9. Enter the path and filename for the package. The path must exist already for the package to be saved.

  10. Alternatively, click on the button next to the Package path field to browse for a location.

  11. Click OK to save the package.

These steps save the package in XML format, which you can then import into another server with SQL Server Management Studio or into a project in the Business Intelligence Development Studio.

To import a package in SQL Server Management Studio, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Right-click on the File System folder.

  6. Select Import Package.

  7. Select the source of the package, in this case File System.

  8. Enter the path and filename for the package. Alternatively, click on the button next to the Package path field to browse for a package. In this case, the package named PatientStatusImport.dtsx is imported to load patient status. This package imports data from a file named PatientStatus.txt.

  9. Click in the Package Name field. The package name is filled in automatically but can be changed if needed.

  10. Click OK to save the package.

The package is then displayed in the File System folder. You can now run the package from there.

Running Packages

You can trigger the packages from within the SQL Server Management Studio and monitor their execution progress in detail. The contents of packages cannot be reviewed within the tool. To do that, you must export the package and then open it in the Business Intelligence Development Studio (see Chapter 11).

To run a package (using the newly imported PatientStatusImport package) within SSIS, do the following:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the File System folder.

  6. Right-click the PatientStatusImport package imported earlier and select Run Package.

  7. The execute package utility runs.

  8. In the General options page, there is the package source, the server, the authentication, and the package name.

  9. Click the Reporting options page to see the reporting options available.

  10. Click the Command Line options page to see the command-line version of the execution. This capability is useful to automate the package execution in the future.


Note - You can add parameters to the command line by selecting the Edit the Command Line Manually option.


  1. Click Execute to run the package.

  2. The Package Execution Progress window opens, displaying the package progress and information as shown in Figure 5.2. The message indicated shows that 10,000 rows were written to the table.

Figure 5.2
Package Execution Progress window.

  1. Click Close to close the progress window.

  2. Click Close to close the execute package utility.

The execution of the package logs a pair of events in the Windows NT Application event log. These events are from source SQLISPackage with event ID 12288 when the package starts and 12289 when the package finishes successfully. If the package fails, the event ID logged is 12291.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM