Exercise 1: Using the Import and Export Wizard
Scenario
The Marketing department at Adventure Works requires a current list of the currencies used in the AdventureWorks2008 database. They want the list in the format of a comma-delimited text file. You must use SQL Server Integration Services to provide this information in the correct format. Create the flat file from the information in the currency table.
In this exercise, you will use the Import and Export Wizard to export the currency table to a flat file.
The main tasks for this exercise are as follows:
1. Connect to the database.
2. Open the Import and Export Wizard.
3. Choose a data source.
4. Choose a destination.
5. Write a T-SQL statement.
6. Configure a flat file destination.
7. Create a package.
8. Save the package.
Task 1: Open SQL Server Management Studio and connect to the database engine on NY-SQL-01
• Start 6235A-NY-SQL-01, and log on as Student with the password of Pa$$w0rd.
• Start Server Management Studio and connect to the NY-SQL-01 SQL server database engine.
Task 2: Use the SQL Server Import and Export Wizard to export the currency data
• For the AdventureWorks2008 database, start the Export Wizard.
Task 3: Choose a data source
• For data source, specify the following options:
• Data Source: SQL Native Client 10.0
• Server Name: NY-SQL-01
Task 4: Choose a destination
• Specify a flat file destination of E:\Mod01\Labfiles\Currency.txt, stating that the column names are in the first row.
Task 5: Write a Transact-SQL statement to extract currency data
• Use the following query to extract the data from the database:
SELECT CurrencyCode, Name FROM Sales.Currency
Task 6: Configure the flat file destination
• For Row delimiter, use {CR}{LF}.
• For Column delimiter, use commas.
Task 7: Create an Integration Services package
• Do not execute the package immediately. Instead, save it to the database using storage server roles for access control.
Task 8: Save the Integration Services package
• Save the package to the msdb database with the name CurrencyExport.
• Write a description for the package that describes what task it performs.
• Use Windows Authentication.
Results: After this exercise, you should have created a package capable of exporting the Currency table from the database. The package should now be stored in the msdb database as CurrencyExport.
Exercise 2: Running an Integration Services Package
Scenario
Now that the package is created, you will execute it and complete the export.
In this exercise you will run the CurrencyExport package and confirm that the export completed successfully.
The main tasks for this exercise are as follows:
1. Connect to Integration Services.
2. Launch the Execute Package Utility.
3. Run the currency package.
4. View the exported data.
Task 1: Connect to Integration Services
• In SQL Server Management Studio, connect to Integration Services on NY-SQL-01.
Task 2: Launch the Execute Package Utility
• Locate the CurrencyExport package in the packages hierarchy.
• Launch the Execute Package Utility for the CurrencyExport package.
Task 3: Run the Currency Package
• Run the package.
Task 4: View the Currency.txt File
• Browse to E:\Mod01\Labfiles.
• View the file and confirm the currency.txt file contents.
Results: After this exercise, you should have successfully exported the contents of the currency table to the file E:\Mod01\Labfiles\Currency.txt.
Exercise 3: Import Data Using the Import and Export Wizard
Scenario
Use the Import and Export Wizard to import data from a flat file to the SQL database.
In this exercise you will use the Import and Export Wizard to move data stored in a flat file to the SQL database.
The main task for this exercise is as follows:
1. Import data to the AdventureWorks database from a flat file.
Task 1: Import Data Using the Import and Export Wizard
• For the AdventureWorks database, start the Import Wizard.
• Select a flat file source of E:\Mod01\Labfiles\Currency.txt.
• Run the package upon the completion of the wizard.
Results: After this exercise, you should have successfully imported the contents of E:\Mod01\Labfiles\Currency.txt to the AdventureWorks database.
Exercise 4: Managing Packages
Scenario
You need to modify the CurrencyExport package for use with a table containing business contacts. You have been asked to export the package to a file system, change elements of the tasks, and then execute the task to export the contacts table to a flat file.
In this exercise you will use the Import and Export Wizard to move data stored in a flat file to the SQL database.
The main tasks for this exercise are as follows:
1. Export a package to the file system using SQL Server Management Studio.
2. Edit a package.
3. Save a package.
4. Use DTUTIL to import a package.
5. Use DTExec to save a package.
Task 1: Export a package to the file system using SQL Server Management Studio
• In Object Explorer, under NY-SQL-01 (Integration Services 10.0.1300 – NY-SQL-01), right-click CurrencyExport, point to Tasks, and then click Export Package.
• Export the package to E:\Mod01\Labfiles\CurrencyExport1.
• Encrypt sensitive data with user key.
Task 2: Edit a package
• Right-click CurrencyExport1.dtsx and then click Edit.
• In the Data Flow tab, edit the Source - Query task.
• Use the following query to select items to export:
SELECT Firstname, Lastname FROM Person.Person
WHERE BusinessEntityID < 20
• Parse the query.
• Confirm that Columns Firstname and Lastname are checked.
• Click OK.
• Right-click Destination – Currency and then click Edit.
• In the Restore Invalid Column References Editor dialog box, in the Available Columns column, for the first row, select Source - Query.FirstName.
• In the Available Columns column, for the second row, select Source - Query.LastName.
• Right-click DestinationConnectionFlatFile and then click Edit.
• In the Flat File Connection Manager Editor dialog box, in the File name field, type E:\MOD01\Labfiles\Contacts.txt.
• Click Advanced.
• Click CurrencyCode.
• In the right column, select CurrencyCode and type FirstName.
• In the center column, click Name.
• In the right column, select Name and type LastName.
• Click OK.
• Edit Destination - Currency.txt.
• In the dialog box, click Yes.
• In the Flat File Destination Editor dialog box, click OK.
Task 3: Save the package
• Save a copy of the package to the file system as E:\MOD01\Labfiles\ContactExport.dtsx.
Task 4: Import a package using DTUTIL
• Import the new package to the SQL database using the following line command:
• dtutil /FILE E:\MOD01\LabFiles\ContactExport.dtsx /COPY SQL;ContactExport
• Confirm that the package is visible in SQL Server Management Studio.
Task 5: Run a package using DTExec
• Use DTExec to execute the ContactExport package.
• Confirm that Contacts.txt has been created at E:\MOD01\LabFiles.
• Turn of virtual machine and delete changes.
Results: After this exercise, you should have modified an existing package, imported it into the SQL msdb database, and executed it.