Выполнение пакетов в SSIS при помощи T-SQL

Начиная с SQL Server 2012 структура проектов SSIS претерпела существенные изменения, путем перехода от модели управления единичными пакетами (package deployment model) к модели управления проектами (project deployment model). Последний подход упрощает и унифицирует разработку, позволяя группировать логически связанные пакеты в проекты и управлять ими как общей единицей. При развертывании проекта на сервер, все его компоненты размещаются в общей базе данных SSISDB.

Сегодняшняя статья будет посвящена процессу выполнения пакетов, развернутых в SSIS при помощи project deployment model.

В SSIS, начиная с 2012 версии обновился компонент Execute package task. Для запуска пакета, находящихся в одном проекте можно воспользоваться данным компонентом с опцией Project Reference. В выпадающем списке PackageNameFromProjectReference отобразятся все пакеты текущего проекта. Перейдя на вкладку Parameter Bindings можно задать значения переменных запускаемого пакета. Особых комментариев по работе данного компонента не требуется, за исключением одного. Данные компонент не позволяет выполнить пакет из другого проекта.

Добавление пользователей в группы Sharepoint

$siteUrl = "http://siteUrl"
$siteCollUrl = "http://siteUrl/Reports"
$web = Get-SpWeb -site $siteUrl | Where-Object {$_.Url -eq $siteCollUrl}

$users = Invoke-SQL -sqlCommand  "exec dbo.GetUserRightsForSP"
foreach ($user in $users.Rows)
{
    
    $userSp = Get-SpUser -Identity $user["SpUser"] -Web $web
    Set-SPUser -Identity $userSp -Web $web -Group $user["Group"]
}

Добавление пользователей в локальные группы при помощи PowerShell

В данной статье будет рассмотрен способ добавления пользователей в определенные для них группы, информация по которым будет браться из базы данных.
Следующий командлет PowerShell добавляет пользователя AD в заданную локальную группу пользователей:

function Add-LocalUser{
     Param(
        $computer   = $env:computername,
        $group      = "GroupName",
        $userdomain = $env:userdomain,
        $username   = $env:username
    )
        ([ADSI]"WinNT://$computer/$group,group").psbase.Invoke("Add",([ADSI]"WinNT://$userdomain/$username").path)
}

Следующий командлет выполняет SQL-запрос на заданной базе данных MS SQL Server и возвращает табличный результат.

function Invoke-SQL {
    param(
        [string] $dataSource = "ServerName",
        [string] $database   = "DbName",
        [string] $sqlCommand = "select [User], [Domain], [Group] from dbo.vUserRights"
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
}

Получаем из базы данных таблицу принадлежности пользователей к группам и добавляем каждого пользователя в определенную для него локальную группу на данной машине.

$users = Invoke-SQL -sqlCommand  "select [User], [Domain], [Group] from dbo.vUserRights";
foreach ($user in $users.Rows)
{
    Add-LocalUser -group $user["Group"] -username $user["User"] -userdomain $user["Domain"] 
}

Дополнительные таблицы. Календарь

Измерение даты и времени -- ключевая и обязательная сущность любого хранилища данных или аналитической системы. В данной статье будет приведен пример создания типовой таблицы календаря с гранулярностью до уровня дня. Для создания данной таблицы  нам понадобится таблица dbo.Numbers из предыдущей статьи. Полный код и результаты работы можно посмотреть здесь.

Создадим таблицу dbo.DimDate. В качестве кластерного ключа таблицы будем использовать суррогатный целочисленный ключ, представленный в формате YYYYMMDD. Ниже представлен скрипт создания таблицы календаря, а также скрипт создания дополнительной таблицы dbo.Numbers.

Запросы к Active Directory

SQL Server позволяет производить выборки данных из множества источников данных при помощи многочисленных доступных драйверов. В том числе MS SQL Server позволяет выполнять запросы к контроллеру домена. В моем случае к Active Directory. Это очень полезная функция, которая может быть использована для множества задач. Я в основном использую ее для динамической настройки прав доступа к отчетным системам. По хорошему, настройка прав должна производиться с использованием групп пользователей домена. В таком случае, при добавлении нового пользователя в группу домена, для него автоматически будут применены соответствующие права. Но не все системы позволяют настроить роли с учетом групп пользователей контроллера домена. Например, я столкнулся с такой проблемой в MS SQL Server SSAS Multidimensional. Чтобы решить данную проблему, необходимо получить список пользователей необходимой группы контроллера домена и автоматически добавить каждого пользователя в нужную нам роль.

Настройка Linked Server

Параметр	Значение
Provider	OLE DB provider for Microsoft Directory Services
Product Name	ADSI
Data Source	afsdatasource
Security	Be made using the login's current security context

Настройка связного сервера к MS AD не представляет из себя ничего трудного. Достаточно указать следующие свойства:

Запросы к AD

Запросы к AD могу писаться в двух форматах:

• SQL
• LDAP

SQL-формат:

SELECT * FROM OPENQUERY(AD,
'SELECT name, ADsPath, title  
FROM ''LDAP://DC=domain_name'' 
WHERE objectCategory = ''User''' 
) 

LDAP-формат:

SELECT * FROM OPENQUERY(AD,'<LDAP://DC=domain_name>;(&(objectcategory=user));name, ADsPath, title ')

Но существует ограничение на получение данных из AD -- 900 строк с данными. Данное ограничение можно обойти настроив соответствующим образом контроллер доменов.

Далее я приведу примеры запросов, которые я использую:

Дополнительные таблицы. Numbers

Для многих задач полезно иметь под рукой дополнительные таблицы. Одной из таких таблиц является таблица с последовательным набором чисел Numbers. Ее можно использовать как для создания тестовых таблиц с данными, так и для прикладных задач. Особенно полезны для работы со строками, для их итеративной обработки.

Существует множество способов создания такой таблицы:

• master..spt_values
• row_number() с любой таблицей, например sys.all_objects
• CTE
• Рекурсивный CTE
• Цикл

Загружаем OpenStreetMap в MS SQL Server

На днях подкинули задачку по геокодированию объектов, а именно:

• По строке адреса определить соответствующие ей географические координаты. Строка с адресом не нормализованна и потенциально может содержать ошибки.
• Обратная задача. По геокординатам точки предоставить список всех имеющихся в базе объектов в эпсилон окрестности от этой точки.

Сервис по геокодированию должен работать только для российских адресов. Для решения задачи геокодирования можно воспользоваться известными географическими средствами, благо такие возможности имеются. К сожалению у большинства сервисов имеются ограничение на число запросов в сутки:

• Яндекс.Карты Геокодирование (25 000 запросов/сутки)
• Google Geocoding(2 500 запросов/сутки)
• Nominatim OpenStreetMap (политика использования 1 запрос/секунда)

Каждый сервис реализует собственные алгоритмы и предоставляет определенный уровень достоверности результатов. Однако, предполагается, что объектов, геокодирование которых необходимо производить, будет больше, чем количество допустимых запросов и обработку необходимо производить в пакетном режиме.

В качестве возможного варианта решения, я решил попробовать реализовать собственный поиск, используя для этого открытую базу данных Open Street Map (OSM). В качестве инструмента для первоначальной реализации я выбрал MS SQL Server 2012. В последнем поддерживаются пространственные типы данных, а также имеется возможность создания пространственного индекса.

SQL Server. Storage Engine. Страницы данных

В данной статье я хочу описать внутренние механизмы движка MS SQL Server по хранению данных и механику данного процесса. Это очень полезно для изучения особенностей работы SQL Server и для собственной проверки гипотез и утверждений о том, как работает SQL Server. Как говорится, доверяй, но проверяй.

Структура таблицы

Рассмотрим структуру таблицы в SQL Server.
Каждая таблица или индекс хранит свою информацию в партициях (partition, до 15.000 для sql server 2012). Каждая партиция может содержать один объект  B-дерева (b-tree, если быть точным в виде B+ дерева) или кучу(heap), отсюда, данные структуры называют общим именем hobt (heap or b-tree). Между партицией и hobt-структурой существует отношение один к одному. В зависимости от типа данных колонок таблицы или индекса, hobt может состоять из трех наборов страниц (allocation unit), каждая из которых образует цепочку страниц (iam chain):

• IN_ROW_DATA (практически все типы данных, кроме описанных ниже)
• ROW_OVERFLOW_DATA (типы переменной длины, вместе с которыми запись не помещается на страницу)
• LOB_DATA ( nvarchar(max), filestream, xml, varbinary)