4. Сравнение пакетов ssis с программной реализацией (на примере vba)
Для лучшего понимания внутреннего механизма работы созданного пакета SSIS
создадим его аналог методом программирования процессов: коннекции к источнику данных, выполнения SQL-запроса и вывода данных в «сток» (приемник – Excel таблицу).
Для написания программного модуля будем использовать язык высокого уровня VBA, реализованный в среде Excel.
Вызовите Excel и выполните команду
СервисМакросРедактор Visual Basic
В среде VB затем выбрать команду
InsertMacros
В созданном макросе написать скрипт, текст которого приведен ниже. При этом обратите внимание на значение переменных
dbName – имя базы данных, из которой извлекаются данные
Provider - которая задает имя драйвера для связи с базой данных (для Access 2000 это Microsoft.Jet.OleDB.4.0)
xlsName – имя Excel-книги, в которую производится «сток» полученных из источника данных
Структура скрипта можно отобразить схемой:
1 Определение
источника
2 Чтение данных
из источника
3 Определение
приемника данных
4. Вывод данных в
новую таблицу Excel
Рис.4 Блок-схема процесса экспорта данных из Excel в Access
Sub test_read_db_write_excel()
'---- Экспорт данных из базы данных в Excel таблицу ----------
'---- Моксва курс "Хранилища данных"
'---- доцент Герасимов Н.А. 2009 г.
'-----------------------------------------------------------------------------------------
'----- 1 Определение источника данных ------------
Provider = "Microsoft.Jet.OleDB.4.0"
dbName = "D:\Учебные пособия\Учебное пособие по ХД\Northwind.mdb"
ConnStr = "Provider=" & Provider & "; Data Source=" & dbName
MsgBox "ConnStr=" & ConnStr
'--- создание объекта для связи с источником ----
Set db = CreateObject("ADODB.Connection")
'---- открыть источник ----------------
db.Open ConnStr
'---- 2 чтение данных из источника --------------------
'------ SQL- запрос ------------------------------
SqlStr = "SELECT * FROM Customers;"
'--- создание приемника данных ------------------
Set rs = CreateObject("ADODB.RecordSet")
'---- чтение данных их таблицы --------------
rs.Open SqlStr, db
'---3 Определение приемника данных ---
xlsName = "D:\Example1.xls"
Rabtab = "Лист1"
tt = "Отчет по запросу=" & SqlStr
'--- Создание новой пустой книги ---
Workbooks.Add
'---4 - Вывод данных в новую таблицу ---
Sheets(Rabtab).Select
'---- Вывод отчета на лист ------
nstr = 0
Sheets(Rabtab).Cells(nstr + 1, 1).Value = tt
'--- определить кол-во столбцов в отчете ------
nn = rs.Fields.Count - 1
'-------------------
nstr = nstr + 2
'------ вывод строки заголовков отчета на лист ----
For i = 0 To nn
rr = rs.Fields(i).Name
Sheets(Rabtab).Cells(nstr + 1, i + 1).Value = rr
Next
'------ Вывод строк отчета на лист----
nstr = nstr + 1
Do Until rs.EOF = True
For i = 0 To nn
rr = rs.Fields(i).Value
Sheets(Rabtab).Cells(nstr + 1, i + 1).Value = rr
Next
'--- переход к следующей строке ---
nstr = nstr + 1
rs.MoveNext
Loop
'---- сохранение новой книги под именем -----
ActiveWorkbook.SaveAs Filename:=xlsName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
'----- Вывод сообщения об окончании процедуры экспорта ----
tt = "Эксрпорт данных " & Chr(10)
tt = tt & "Из -->" & dbName & Chr(10)
tt = tt & "в <--" & xlsName & Chr(10)
tt = tt & "***Закончен***"
MsgBox tt
End Sub
Создав текст макроса, можно запустить его на исполнение с помощью клавиши F8. Проходя по шагам исполнение скрипта можно понять, как идет обработка данных в процессе трансформации данных.
Аналогично можно создать макрос для импорта данных с листа Excel-книги в базу данных (или хранилище данных).
Создайте с помощью Access тестовую пустую базу Test.mdb. Скопируйте в нее таблицу Customers из базы данных NorthWind.mdb. теперь напишите макрос для перекачки данных из Excel-таблицы D:\Example1.mdb. Вариант текст макроса импорта данных приведен ниже.
Отметим, что данные в таблице Excel расположены со строки 3. Следует учитывать, что типы передаваемых данных должны совпадать с типами данных, определенные в таблице базе данных. Для этого в строке 2 таблицы желательно разместить описатели типа данных: t- текстовый, i- целое, d-дата, c- числовой с пл.запятой.
Перед записью данных в таблицу базы данных, следует принудительно очистить таблицу «приемник».
Sub test_read_excel_write_db()
'---- Импорт данных из Excel таблицы в базу данных ----------
'---- Моксва курс "Хранилища данных"
'---- доцент Герасимов Н.А. 2009 г.
'-----------------------------------------------------------------------------------------
'---- 1 Определение источника данных ------------
xlsName = "D:\Example1.xls"
RabTab = "Лист1"
nstr = 2 ' номер строки, в которой наименования столбцов
'---- открыть книгу ----
Workbooks.Open Filename:=xlsName
Sheets(RabTab).Select
'---- Чтение списка имен столбцов -----------
ncol = 1
Dim TypeD(100)
With Sheets(RabTab)
Do
rr = .Cells(nstr + 1, ncol).Value
If rr = "" Then Exit Do
'-------------------------
TypeD(ncol) = .Cells(nstr, ncol).Value
'---------------------
ncol = ncol + 1
Loop
End With
tt = "Количество столбцов в таблице =" & ncol
MsgBox tt
'--- 2 Определить приемник данных
Provider = "Microsoft.Jet.OleDB.4.0"
dbName = "D:\Учебные пособия\Учебное пособие по ХД\test.mdb"
ConnStr = "Provider=" & Provider & "; Data Source=" & dbName
tabName = "Customers" ' имя таблицы в базе данных
MsgBox "ConnStr=" & ConnStr
'--- создание объекта для связи с источником ----
Set Db = CreateObject("ADODB.Connection")
'---- открыть источник ---------------------------------------
Db.Open ConnStr
'---4--- очистить таблицу приемник ---------------------
Set rs = CreateObject("ADODB.RecordSet")
strSql = "DELETE * FROM " & tabName
rs.Open strSql, Db
'---- 5 Запись данных в приемник --------------------
'------ SQL- запрос ------------------------------
strSql0 = "INSERT INTO " & tabName & " VALUES ( $ss$ )"
' MsgBox "strsql0-->" & strSql0
With Sheets(RabTab)
nstr = nstr + 2
Do
rr = .Cells(nstr, 1).Value
If rr = "" Then Exit Do
'-----------------------------
ss = ""
For k = 1 To ncol - 1
ss1 = "'": If TypeD(k) = "i" Or TypeD(k) = "c" Then ss1 = ""
'-------------------
rr1 = .Cells(nstr, k).Value
rr1 = Replace(rr1, "'", "")
ss0 = ss1 & rr1 & ss1
ss = ss & "," & ss0
'MsgBox ss
Next
ss = Mid(ss, 2)
strSql = Replace(strSql0, "$ss$", ss)
'MsgBox strSql
'--------- запись данных в таблицу базы ----------
rs.Open strSql, Db
'----------------
nstr = nstr + 1
Loop
End With
'----------------
'-----
tt = "Импорт данных " & Chr(10)
tt = tt & "Из -->" & xlsName & Chr(10)
tt = tt & "в <--" & dbName & Chr(10)
tt = tt & "***Закончен***"
MsgBox tt
End Sub