Importowanie i eksportowanie danych MS SQL Server 2000 za

Ćwiczenie 6
Importowanie i eksportowanie danych MS SQL Server 2000 za pomocą
usług transformacji danych.
Wprowadzenie teoretyczne.
Technologia DTS (Data Transformation Services) umożliwia niezawodne i skuteczne
przenoszenie danych oraz konwersję lub transformację danych pomiędzy heterogenicznymi
źródłami danych. Głównymi komponentami DTS są : sterowniki bazy danych, pakiety oraz
narzędzie DTS (DTS Designer oraz obiekty COM DTS).
Sterowniki bazy danych OLE DB i ODBC stanowią jeden z głównych elementów DTS umożliwią one komunikację z innymi systemami. MS SQL Server zawiera sterowniki OLE
DB i ODBC dla:
 MS SQL Server
 Oracle
 Sybase
 MS OLAP Services
 MS Internet Publishing
 Access i Excel
 Plików tekstowych (ASCII)
 Innych źródeł danych ODBC.
Sterownik plików tekstowych jest przydatny podczas przenoszenia danych z systemu, który
nie może być obsługiwany przez żaden z rodzimych sterowników ani przez ogólny sterownik
ODBC. W większości przypadków można wyeksportować dane z takiego systemu do pliku
tekstowego, a następne przenieść dane z tego pliku do SQL Server. Tą samą metodą można
wyeksportować dane z SQL Server do drugiego systemu.
Pakiety DTS mogą być stworzone za dopomogą kreatora DTS Import/Export Wizard.
Kreator ten służy do tworzenia pakietów DTS, które później można przeglądać lub
modyfikować w DTS Designer. Pakiety są zbiorami zadań związanymi z importem,
transformacją i eksportem danych, którzy mogą być uruchamiane wielokrotnie. Pakiety mogą
być przechowywane w repozytorium na serwerze lokalnym lub zdalnym. Wykonanie całego
pakietu sprowadza się do:
 Połączenia z dowolnym źródłem danych.
 Kopiowania danych (i ewentualnie obiektów bazy danych).
 Transformacji danych.
 Odłączenia od źródła danych.
 Powiadomienia użytkowników, procesów lub nawet innych pakietów o zaistniałych
zdarzeniach (np. za pośrednictwem e-maila).
Każdy pakiet zawiera następne zdefiniowane obiekty:
Połączenia – służą do przechowywania informacji o źródle lub celu danych. W połączeniu
określa się dostawcę danych (np. Microsoft OLE DB Provider for SQL Server), serwer
docelowy, nazwę konta oraz nazwę bazy danych. Połączenia można wybierać w DTS
Designer w menu DATA.
Zadania DTS – Dyskretny zestaw funkcji wykonywanych w pojedynczym kroku. Zadania są
poleceniami wykonującymi żądane przekształcenia i przesłania danych. Zadanie jest w stanie
wykonać skrypt SQL, przesłać wiadomość e-mailem, wykonać masowe wprowadzenie
danych, a także uruchomić skrypt ActiveX lub zewnętrzny program. Zadania można wybrać
w DTS Designer w menu TASK.
Procedury przepływu zadań(precedence constraints) – określają kiedy i w jaki sposób
zadanie ma zostać wykonane, na przykład po pomyślnym lub niepomyślnym zakończeniu
poprzedniej operacji. Na przykład można utworzyć zadanie, które wysyła wiadomość e-mail
informująca o pomyślnym lub niepomyślnym wykonaniu zadania. Te procedury mogą być
trzech typów: bezwarunkowe (On Comletion), w przypadku sukcesu (On Success) oraz w
przypadku porażki(ON Failure). Procedury transformacji danych mogą być wyznaczony w
menu Workflow DTS Designer.
Procedury transformacji danych – definiują szczegółowy proces transformacji danych.
Nazywane także pompami danych. Procedury transformacji danych są skojarzony z zadaniem
Transrorm Data Task.
Przebieg ćwiczenia.
1. Czynności przygotowawcze. W tym punkcie będą skopiowane pliki, niezbędne do tego
ćwiczenia.
Stwórz na dysku c: katalog „Nazw_studenta”. Skopiuj do tego katalogu plik
Cw_6_archyw.zip ze Strony internetowej oraz rozpakuj. Są niezbędne następne pliki do tego
ćwiczenia:
 newprods.sql
 newprod2.sql
 new_data.dts
 newprods.txt
2.Importowanie danych wewnątrz bazy danych przez polecenie SQL.
W poniższych podpunktach za dopomogą kreatora DTS będzie stworzona nowa tabela w bazie
danych Northwind. Do tej tabeli będą importowane dani przez polecenie SQL. W ten samy
sposób mogą być przechowywane np. wyniki działalności firmy za termin określony. Te
wyniki często występują w postaci agregowanych rezultatów danych. Dla wewnętrznego
importowania danych można wykorzystać źródło danych w postaci Microsoft OLE DB
Provider.
2.1. W tym podpunkcie będzie stworzony pakiet DTS do importowania danych za dopomogą
kreatora DTS.
Uruchom SQL Server Enterprise Manager. Rozszerz: Microsoft SQL Servers, SQL Server
Group, twój server. Kliknij menu Tools -> Wizards. W oknie Select Wizard rozszerz Data
Transformation Services, Kliknij DTS Import Wizard. Wpisz niezbędnie dani na
odpowiednich stronach kreatora oraz potem Kliknij „Next”. Dani pobierz według tabeli 1.
Tabela 1.
Wartość
Opcja
Data source oraz
Data destination
Microsoft OLE DB Provider for SQL Server
Server name
(Dla stron Source oraz Destination)
(local)
Security context
(Dla stron Source oraz Destination)
Windows Authentication
Database
(Dla stron Source oraz Destination)
Northwind
Table copy or query
Use a query to specify the data to transfer
SQL query statement
Wpisz następne polecenie SQL dla pobierania danych pro
realizowane towary we wszystkich zamówieniach:
SELECT ProductName,
SUM(o.UnitPrice * Quantity)
AS Total
FROM [Order Details] AS o
INNER JOIN Products AS p
ON o.ProductID = p.ProductID
GROUP BY ProductName
Parse
Yes
Destination table name
(Modyfikuj nazwę Results do nowej wartości)
Product_Totals_nazw_studenta
Kontynuacja tabeli 1
Opcja
Wartość
Transform (Kliknij pole Transform)
Drop and recreate destination table
Do not allow NULLs in the Total column (clear)
Run DTS package immediately
Yes
Save DTS package
on SQL Server
Yes
DTS package name
Northwind_Product_Totals_nazw_studenta
DTS package description
Year to date product totals
Server name
(local)
DTS package security context
Windows Authentication
2.2. Zanalizuj komunikaty przy uruchamianiu pakietu DTS. Objaśnij, dlaczego powstał się
komunikat ze statusem „Error occurred”?
2.3. Otwórz SQL Query Analyzer w trybie Microsoft Windows Authentication. Stwórz
polecenie SQL dla odwzorowania stworzonej w p.2.1. tabeli. Sprawdź zawartość tabeli.
2.4. Uruchom DTS Designer dla odwzorowania treści i właściwości pakietu DTS który został
zapisany w SQL Server. W tym celu rozszerz Data Transformation Services, Kliknij Local
package. Kliknij (Right-click) pakiet “Northwind_Product_Totals_nazw_studenta”, wybierz
Design Package. W oknie projektu zbadaj właściwości każdego komponentu pakietu DTS.
2.5. Powtórz pp.2.1 -2.3 dla stworzenia w bazie danych Northwind pakietu DTS, który
tworzy nową tabelę oraz importuje dani pro ceny wszystkich zamówień z tabel Orders oraz
Order Details. W tym celu sformuj polecenie SQL w Query Analizer. Nowa tabela musi
zawierać kolumny: Identyfikator (zamówienia), Data (zamówienia)
oraz
Cena
(zamówienia).
3. Modyfikacja pakietu DTS przez DTS Designer.
3.1. W tym podpunkcie będzie uruchomiony istniejący pakiet DTS New_data.dts oraz dani z
istniejącego pliku tekstowego new_prods.txt będą importowane do nowej tabeli bazy danych
Northwind.
a) Stwórz tabelę newprods w bazie Northwind, w tym celu uruchom plik
C:\nazw_st\newprods.sql w Query Analizer. Sprawdź w Query Analizer obecność tabeli
newprods.
b) Odtwórz istniejący pakiet DTS zawarty w pliku C:\nazw_st\New_data.dts. W tym celu w
Enterprise Manager Kliknij (Right-click) DataTranformation Services oraz Kliknij Open
Package. Wybierz plik C:\nazw_st\New_data.dts .Rozszerz pakiet oraz odtwórz ostatnią
wersję pakietu.
c) W DTS Designer Kliknij (Right click) w panelu element New Products. W oknie
Properties ustal dostęp do pliku źródłowego C:\nazw_st\newprods.txt . Plik tekstowy
newprods.txt jest źródłem danych dla pakietu DTS.
d) W DTS Designer popatrz właściwości drugiego elementu typu connection oraz elementu
Transform data task ( musi być ustalona baza danych Northwind w elemencie SQL Server
Destination oraz odpowiedni schemat transformacji danych w elemencie Transform data
task). Objaśnij schemat transformacji danych.
e) Uruchom pakiet DTS (Opcja menu DTS Designer Package->Execute).
g) Sprawdź w Query Analizer rezultaty transformacji danych - zawartość tabeli newprods.
Tabela musi zawierać 1343 rekordów.
3.2. W tym podpunkcie będą dodane nowe zadania do istniejącego pakietu DTS.
a) Odtwórz pakiet New_data (patrz p.3.1.b)
b) Kliknij ikonę Execute SQL Task i przeciągnij ją do panelu projektu DTS Designer.
Będzie uruchomiane okno Execute SQL Task Properties.
c) Uzupełnij opcji dialogu według tabeli 2. Kliknij OK.
Tabela 2.
Opcja
Wartość
Description
Usuniecie tabeli Northwind.newprods
Existing connection
SQL Server Destination (Northwind)
SQL statement
DROP TABLE Northwind.dbo.newprods
Parse query
OK
d) Powtórz p.3.2.b dla drugiego elementu Execute SQL Task .
e) Uzupełnij opcji dialogu drugiego elementu Execute SQL Task według tabeli 3. Kliknij
OK.
Tabela 3.
Option
Value
Description
Stworzenie tabeli Northwind..newprods
Existing connection
SQL Server Destination (Northwind)
SQL statement
Wpisz polecenie CREATE TABLE z pliku
C:\nazw_st\newprod2.sql
Parse query
OK
3.3. W tym podpunkcie do projektu pakietu będą dodane procedury przeplywu zadań
(elementy typu „workflow”), którzy wyznaczają związki(precedence constraits) pomiędzy
oddzielnymi zadaniami pakietu DTS. Te związki odwzorowują warunki aktywacji zadań
pakietu.
a) Na panelu DTS Designer przy naciśniętym klawiszu CTRL Kliknij lewą myszą (left click)
następne zadania: 1)Usunięcie tabeli 2)Stworzenie tabeli (w tej samej kolejności). Na panelu
będą wyświetlane jednocześnie oba te zadania.
b) Z menu Workflow wybierz opcję On Complection (niebieska strzałka). Na panelu będzie
wyświetlana niebieska strzałka, skierowana od zadania Usunięcie tabeli do zadania
Stworzenie tabeli. Popatrz właściwości stworzonego elementu Workflow oraz zamknij
okno dialogu.
c) Na panelu DTS Designer w sposób analogiczny opisanemu w p.3.3.a wyświetli następne
zadania: 1)-Stworzenie tabeli 2) - Odczytanie pliku tekstowego. Połącz te dwa zadania przez
związek On Success w sposób opisany w 3.3.b. Na panelu będzie wyświetlana zielona
strzałka, skierowana od zadania Stworzenie tabeli do zadania odczytania pliku tekstowego.
Popatrz właściwości stworzonego elementu Workflow oraz zamknij okno dialogu.
d) Zachowaj ten pakiet DTS oraz uruchom go. Sprawdź rezultaty. Zanalizuj komunikaty
pakietu DTS. Objaśnij przeznaczenia wszystkich elementów wykorzystanych w pakiecie
DTS. Zamknij DTS Designer.
4. Importowanie obiektów SQL Server. W tym punkcie będą importowane obiekty (tabeli
oraz widoki) SQL Server z bazy danych Northwind do nowej bazy danych przez kreator DTS
Import/Export.
4.1. Utwórz dwa podkatalogi \DB_wind oraz \DB_xfer w katalogu C:\nazw_st .
Wykorzystując wiedzie z poprzednich ćwiczeń stwórz przez Query Analizer nową bazę
danych NazwiskoSt_wind z parametrami po domyśleniu w podkatalogu \DB_wind. Katalog
\DB_xfer będzie wykorzystany przez DTS dla stworzenia skryptów obiektów transformacji,
którzy będą importowane do bazy danych NazwiskoSt_wind.
4.2. W sposób opisany w p. 2.1 uruchom DTS Import Wizard. Uzupełnij opcji kreatora DTS
według tabeli 4.
Tabela 4.
Option
Value
Data source and destination
Microsoft OLE DB Provider for SQL Server
Server name
(source and destination)
(local)
Security context
(source and destination)
Windows Authentication
Database (source)
Northwind
Database (destination)
NazwiskoSt_wind
Table copy or query
Copy objects and data between SQL Server
databases
Objects to transfer
Clear Include all dependent objects
Clear Copy all objects
Option
Value
Select objects
Orders table
Order Details table
Order Subtotals view
Summary of Sales by Quarter view
Summary of Sales by Year view
Use default options
Clear
Options (Security options)
Clear Copy database users and database roles
Clear Copy object-level permissions
Options (Table options)
Clear Copy triggers
Clear Copy full text indexes
Clear Copy PRIMARY and FOREIGN keys
Script file directory
C:\nazw_st\DB_xfer
Run DTS package
immediately
Yes
4.3. Uruchom Query Analizer. Wykorzystując widok systemowy information_schema.tables
wyznacz listę widoków oraz tabel importowanych do bazy danych NazwiskoSt_wind.
4.4. Popatrz przez przeglądarkę zawartość podkatalogu \DB_xfer. Objaśnij przeznaczenie
oddzielnych plików w tym katalogu. Popatrz przez notatnik zawartość nie pustych plików
.viw, .tab, .log. Objaśnij przeznaczenie kodów zawartych w tych plikach.
5. Eksportowanie danych do plików tekstowych. W tym punkcie będą eksportowane dani
przez kreator DTS/Export do postaci plików tekstowych. Te dani można potem importować do
innych baz danych lub środowisk informacyjnych. Dla przykładu będą eksportowane dani pro
klientów Ameryki Południowej.
5.1. Uruchom SQL Server Enterprise Manager. Rozszerz: Microsoft SQL Servers, SQL
Server Group, twój server. Kliknij menu Tools -> Wizards. W oknie Select Wizard rozszerz
Data Transformation Services, Kliknij DTS Export Wizard. Wpisz niezbędnie dani na
każdej stronie kreatora oraz potem Kliknij „Next”. Dani pobierz według tabeli 5.
Tabela 5.
Option
Value
Data source
Microsoft OLE DB Provider for SQL Server
Server name
(source and destination)
(local)
Security context
(source and destination)
Windows Authentication
Database (source)
Northwind
Data destination
Text file
Destination file name
C:\nazw_st\SoAmer.txt
(wpisz imię pliku)
Table copy or query
Use a query to specify the data to transfer
Query Builder
Yes
Source table
Customers
Selected columns
Wszystkie kolumny oprócz Phone oraz Fax
Sorting order
Country column, CompanyName column
Query criteria
Only rows meeting the following criteria
Customers.Country = 'Argentina'
OR
Customers.Country = 'Brazil'
OR
Customers.Country = 'Venezuela'
SQL query statement
Parse
Destination file format
Delimited
Column delimiter
Tab
Run DTS package
immediately
Yes
Schedule DTS package for
later execution
Yes
Recurring job schedule
Weekly, on Monday, Wednesday, and Friday
at 9:00 A.M.
Save DTS package on
SQL Server
Yes
DTS package name
SouthAmerican_Customers
DTS package description
Klienci South American
Server name
(local)
DTS package security
context
Windows Authentication
(Przechowywanie pakietu DTS w SQL Server Agent)
5.2. Popatrz przez notatnik zawartość pliku SoAmer.txt . Czy odpowiada ten plik ustalonym
warunkom kopiowania?
5.3. Popatrz właściwości stworzonego pakietu DTS w SQL Server w katalogu Local Packages
w sposób analogiczny p.2.4.
5.4. Popatrz właściwości stworzonego zadania w środowisku planistycznym SQL Server
Agent. W tym celu w Enterprice Manager rozszerz katalogi Management->SQL Server
Agent. Kliknij Jobs. W prawym panelu są zadania przeznaczone dla automatycznego
uruchamiania. Popatrz właściwości stworzonego w p.5.1 zadania.
6. Importowanie danych przez program masowego kopiowania danych (Bulk Insert
Task). W tym punkcie dla importowania danych z pliku tekstowego będzie wykorzystany
program Bulk Insert Task. Dla konstruowania zadania z wykorzystaniem Bulk Insert Task
będzie wykorzystany DTS Designer.
6.1. Uruchom DTS Designer (right-click DataTransformation Services oraz click New
Package).
6.2. W panelu connection wybierz źródło danych Microsoft OLE DB Provider for SQL
Server.
6.3. Uzupełnij okno Connection Properties według danych tabeli 6.
Tabela 6.
Opcja
Wartość
New Connection
Bulk Insert Connection
Database
Northwind
6.4. Kliknij w panelu Task ikonę Bulk Insert Properties i przeciągnij ją do panelu projektu
DTS Designer. Będzie uruchomiane okno Bulk Insert Task Properties.
6.5. Uzupełnij okno Bulk Insert Task Properties według danych tabeli 7.
Tabela 7.
Option
Value
Description
NewProducts
Existing connection
Bulk Insert Connection
Destination table
[Northwind].[dbo].[newprods]
Source data file
C:\nazw_st\Newprods.txt
(wpisz nazwę pliku tekstowego)
Column Delimiter
Comma
6.6. Kliknij Execute dla importowania tekstowych danych do tabeli newprods.
6.7. Zachowaj pakiet pod nazwiskiem Nazw_st_BulkInsertNewProducts.
Treść sprawozdania
1. Odnośnie do każdego punktu ćwiczenia umieścić kod Transact-SQL oraz rzuty
rezultatów.
2. Do kilku wybranych punktów zademonstrować własne przykłady.
Przykładowe pytania kontrolne
1.Jakie zadania, procedury przepływu danych(elementy workflow) oraz procedury
transformacji danych zawiera pakiet DTS stworzony w p.3.3
2.Jakie są przeznaczenia procedur przepływu danych (elementów workflow)?