Добавление данных в Raw File Destination внутри ForEach Loop контейнера

В продолжение темы об использовании ADO.NET коннектора в сочетании с For Each Loop контейнером.

В качестве полезной нагрузки такого решения скорей всего будет выступать Data Flow Task, в котором будут забираться данные из какого-либо листа Excel, обрабатываться, включая использование агрегаций, и загружаться в некую таблицу в базе.

Беда в том, что так как в Data Flow будет обрабатываться один лист за раз, то и агрегации будут накладываться на данные текущего листа. А что делать, если в таблицу нужно заливать исключительно агрегированные данные и создавать вспомогательные таблицы тоже нельзя?

Сразу на ум приходит вопрос. Почему разработчики SSIS сделали Rowset Destination, но не сделали Rowset Source?

В сети можно найти решения на базе Script Task’ов, позволяющие работать с Rowset’ами (например, здесь), можно даже извернуться и использовать объект, в котором содержится rowset, в связке с For Each Loop контейнером.

Но есть более правильное и более быстрое решение – использование Raw файлов.

Raw файл формируется средствами SSIS с помощью Raw File Destination. В нем данные хранятся в двоичном виде, плюс этот файл содержит также информацию о структуре данных и атрибутах столбцов, поэтому он не требует дополнительной обработки и обеспечивает высокую скорость работы.

Разработчики SSIS позиционируют RAW файлы, как способ передачи данных между различными Data Flow Task’ами.

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

Поэтому когда Rowset Destination используется внутри For Each Loop контейнера, файл данных будет либо перезаписываться, либо новые данные будут добавляться к старым, что не правильно и не изящно.

Решить проблему, можно создав два Data Flow Task’a, в одном использовать RAW File Destination в режиме ‘Create Always’, в другом – ‘Append’. А принятие решения – какое Data flow исполнять принимать на основе счетчика.

Script Task накручивает счетчик при каждой итерации контейнера. Если значение счетчика 1, то запускается левое workflow, создается RAW файл и в него загружается часть данных с первого листа. При всех прочих значениях счетчика запускается второе workflow, в котором данные добавляются.

По окончании работы компонента можно запустить Data Flow с Raw File Source, чтобы забрать все данные, провести необходимые агрегации и загрузить их в базу.

Ссылки:

RAW File Destination on Technet: http://technet.microsoft.com/en-us/library/ms141661.aspx

Сравнение скорости работы RAW файлов и Script Task’ов: http://consultingblogs.emc.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx

Рубрика Business Intelligence, Tips and Tricks | Метки | Добавить комментарий

Изменение параметров рендеринга отчета SSRS с помощью параметров URL

По неясной причине SSRS не поддерживают выгрузку данных в формат Excel 2007, в результате чего выгружать отчеты, количество строк в которых превышает 65000 не получится.
В качестве альтернативного варианта можно производить выгрузку в формат CSV, который таких ограничений естественно не имеет, и, в то же время, замечательно открывается в Excel. При такой выгрузке разделителем по умолчанию будет “,”. Беда в том, что такой файл Excel автоматически не распарсит. А если в качестве разделителя использовать “;” , то все данные будут аккуратно разложены по столбцам.

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

http://server/reportserver?/Subdir/Report+name&
rs:Command=Render&rs:Format=CSV&rс:FieldDelimiter=;

На MSDN лежит подробное описание параметров, которые можно передать при рендеринге отчета в CSV: http://msdn.microsoft.com/en-us/library/ms155365.aspx

Ссылки:
Подробная инфа по параметрам для разных видов рендеринга: http://msdn.microsoft.com/en-us/library/ms155397.aspx

Рубрика Business Intelligence, Tips and Tricks | Метки , | 1 комментарий

Использование OPENQUERY в качестве источника данных для обновления локальной таблицы

В продолжение темы об использованием OPENQUERY для взаимодействия с linked-серверами – использование OPENQUERY в качестве источника данных для UPDATE’а локальной таблицы.

UPDATE A
SET A.[Column_1] = B.[some_column]
FROM local_table AS A 
JOIN (SELECT * FROM OPENQUERY(linked_server,sql_query)) AS B 
ON A.[Column_2] = B.[some_column_2]
Рубрика Tips and Tricks, TSQL | Метки , | 2 комментария

MERGE vs. INSERT,UPDATE,DELETE

В пакетах SSIS вполне типична ситуация, когда данные из источника (например файла Excel, CSV) нужно сравнить с уже имеющимися данными в базе данных и, в случае необходимости, обновить, вставить или даже удалить записи из целевой таблицы.

Lookup Transformation

Первый и самый банальный способ – использовать стандартный компонент сервисов интеграции – LOOKUP.


На вход компоненту подаются данные, которые нужно сравнить с целевой таблицей. В свою очередь сам компонент LOOKUP получает ряды из целевой таблицы или SQL-запроса, а на вкладке Columns можно выбрать по каким столбцам будет проводиться сверка.

С данными, которые найдены в целевой таблице все понятно, они попадают в Match Output. С теми, для которых совпадений нет, в зависимости от поставленных целей можно поступить по разному: проигнорировать эти ряды, отправить их в error output, вызвать ошибку выполнения компонента или отправить их в поток No match output.

В рамках поставленной выше задачи нас интересует последний вариант. Если запись в целевой таблице отсутствует, то далее где-то по ходу dataflow будет лежать компонент OLE DB Destination, который, как известно, умеет исключительно вставлять новые записи в таблицу. А по ходу Match Output будет лежать OLE DB Command с инструкцией INSERT…INTO…

Самый главный минус такого решения – производительность. Сам по себе компонент Lookup Transformation не отличается выдающейся скоростью. Эту ситуацию можно немного исправить, создав кэш целевой таблицы в памяти, правда надо учитывать, что чем больше целевая таблица, то тем больше времени нужно на то, чтобы ее подгрузить в память и тем больше нужно места в памяти для хранения кэша.

Коль скоро скорость имеет значение, можно в место целевой таблицы использовать SQL-запрос, чтобы получить только те столбцы, которые реально нужны для целей lookup’a.

Помимо того, что компонент Lookup Transformation весьма не быстрый, вышеописанный метод подразумевает, что далее по ходу выполнения Data Flow Task лежат либо OLE DB Command, либо OLE DB Destination, так что по факту используются отдельные SQL-запросы для каждой операции INSERT,UPDATE,DELETE.

Execute SQL Task
Используем инструкции INSERT, UPDATE, DELETE
Неплохой и достаточно быстрый вариант – вынести INSERT, UPDATE и DELETE за пределы сервисов интеграции – использовать компонент Execute SQL Task, который вынесет эти операции непосредственно на уровень сервера SQL.
Почему предлагается использовать Execute SQL Task, который работает на уровне Control Flow, а не OLE DB Command, работающий на уровене Data Flow? OLE DB Command работает с каждым рядом, который поступает на вход этого компонента, поэтому производительность такого решения не слишком высока, в свою очередь Execute SQL Task закидывает задание на сервер и ждет окончания его выполнения. Естественно SQL-запрос на стороне сервера будет выполнен намного быстрее. Однако для того чтобы этот метод заработал, на стороне сервера должна лежать не только целевая таблица, но и исходные данные, а для этого придется создать временную таблицу, куда этот массив данных упадет.

Так что если мне нужно обновить целевую таблицу информацией, которая лежит, скажем, в документе Excel, я создаю обычный Data Flow Task, в котором забираю исходные данные, фильтрую их, если необходимо, проделываю различные трансформации. Все для того, чтобы загрузить их во временную таблицу на сервере SQL.

Теперь, чтобы обновить информацию в целевой таблице достаточно бросить Execute SQL Task со следующим SQL-запросом:

INSERT INTO Schema.TableB
SELECT     Field1,Field2,...,FieldN
FROM         Support.TableA AS A
-- я использую схему Support для хранения вспомогательных таблиц
WHERE     (NOT EXISTS
(SELECT     A.Field1
FROM          Schema.TableB s AS B
WHERE      (A.Field1 = B.Field1)));
UPDATE Schema.TableB
SET Field3 = Support.TableA.Field3
WHERE Field1  = Support.TableA.Field1 AND Field2 = Support.TableA.Field2

Хотя работать вышеуказанный код будет довольно шустро, INSERT, UPDATE крутятся в разных запросах, что опять же негативно скажется на производительности.

Используем MERGE

MERGE Schema.TableB as target
USING (SELECT Field1,Field2,...,FieldN FROM Support.ReverseTablogix) as source
ON (target.Field1 = source.Field1 AND target.Field2 = source.Field2)
WHEN MATCHED THEN
UPDATE SET target.Field3 = source.Field3
WHEN NOT MATCHED THEN
INSERT (Field1,Field2,...,FieldN)
VALUES (source.Field1,source.Field2,...,source.FieldN);

В общем виде все достаточно просто: целевая таблица, таблица-источник, линкуются по таким-то полям. Вот так одним махом можно решить проблему и апдейтов, и инсертов, и, если потребуется удалений. Более того, условия MATCHED и NOT MATCHED, можно дополнять своими условиями, например:

WHEN MATCHED and target.FieldN < source.FieldN
...action
WHEN MATCHED and target.FieldN > source.FieldN
...action

Плюс результат работы можно вывести в отдельную таблицу для целей ведения логов и анализа:

OUTPUT $action,DELETED.Field1,DELETED.Field2,INSERTED.Field1,INSERTED.Field2

В общем возможности этой функции очень широки, а скорость работы радует. На TechNet есть подробная инфа по MERGE с наглядными и полещными примерами.
Ссылки:
MERGE (Transact-SQL)

Рубрика Business Intelligence, TSQL | Метки , | 1 комментарий

Взаимодействие с отчетами SSRS с помощью гиперссылок

Передача значений параметров в теле гиперссылок

В отчетах SSRS можно передавать значения параметров непосредственно в теле гиперссылок, что очень удобно.

Весь подсвеченный код должен записываться в одну строку, здесь он разбит на несколько строк только ради удобства чтения.

В общем виде ссылка на отчет будет выглядеть следующим образом:

http://server/reportserver?/Subdir/Report+name&
rs:Command=Render¶m1=value1¶m2=value2

Если параметр – multi-value, то ему можно передать несколько значений:

http://server/reportserver?/Subdir/Report+name&
rs:Command=Render¶m1=value1¶m1=value2

Сразу оговорюсь, что по умолчанию веб-морда SSRS, которая крутится на SSIS висит по адресу http://server/reports, из нее ссылки на репорты брать не нужно, параметры передать не получится. Структуру папок на сервере можно посмотреть по линку http://server/reportserver, там лежат правильные ссылки на репорты.

Открытие целевого отчета, передав в качестве значений параметров содержимое ячеек исходного отчета

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

В открывшемся диалоговом окне на вкладке Action нужно выбрать пункт “Go to URL” и кликнуть по кнопке Fx.

Откроется Genric Query Designer, в окне которого нужно ввести код аналогичный представленном ниже.

="javascript:void(window.open('http://server/reportserver?/Subdir/Report+name&
rs:Command=Render¶m1=" & Fields!Field_1.Value "&
param2=" & Fields!Field_2.Value & "','_blank'))"

Выгрузка отчета в формат по выбору без предварительного рендеринга в HTML коде

Если нет необходимости работать с отчетом через веб-интерфейс, можно сразу выгрузить его с сервера репортов в формате по выбору: HTML3.2, HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML. Особую ценность, на мой взгляд, представляют форматы CSV, Excel и XML, а для любителей красивых картинок можно использовать и PDF 🙂

Для этого достаточно использовать параметр rs:Format, передав ему имя формата, в который нужно экспортировать отчет:

http://server/reportserver?/Subdir/Report+name&
rs:Command=Render&rs:Format=Excel¶m1=value1¶m2=value2

Относительно разнообразных служебных параметров можно почитать на MSDN: http://msdn.microsoft.com/en-us/library/ms152835.aspx

Эту замечательную фичу можно использовать, чтобы интегрировать отчеты SSRS с какими-либо бизнес приложениями.

На данный момент в SSRS отправка пользователям отчетов по расписанию реализована с помощью сервиса подписок, при всех достоинствах этого метода, у него есть один недостаток – невозможность задать произвольное имя файлу отчета. Дополнительный бонус решения, описанного выше, – возможность сформировать отчет, сохранить его под любым именем и доставить его пользователю с помощью пакета SSIS.

Dim ReportURL, ReportName As String 
ReportName = Dts.Variables("VbReportName").Value.ToString +"_" +Date(Now()) 
Dim SaveToPath As String = "\\DestinationFolder\ReportName + ".xls" 
ReportURL = "http://server/ReportServer?/reportname&rs:Command=Render&rs:Format=EXCEL" 
SaveFile(ReportURL, SaveToPath) 
Dts.Variables("VbReportName").Value = SaveToPath

Ссылки:
Префиксы параметров в ссылках на отчеты SSRS: http://msdn.microsoft.com/en-us/library/ms153579.aspx
Описание различных служебных параметров: http://msdn.microsoft.com/en-us/library/ms152835.aspx

Рубрика Business Intelligence | Метки , , | 65 комментариев

Получение заголовков столбцов в листе Excel

В сервисах интеграции коннектору к документу Excel можно указать, что первая строка содержит имена столбцов. Беда в том, что не всегда столбцы располагаются в первой строке, зачастую у документа есть еще и шапка. В этом случае имена столбцов подтягивать как-будто бы неоткуда и придется работать с буквеннысми обозначениями столбцов, что очень неудобно.

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

На приведенном скриншоте видно, что диапазон ячеек для работы задан с ячейки A:4  по столбце AQ.

Если имя листа подгружается в переменную, то в данном случае можно использовать Expressions.

REPLACE(@[User::varSheetName],"'","") + "A4:AQ"
Рубрика Tips and Tricks | Метки , , | 4 комментария

Последовательное получение данных со всех листов в документе Excel

Когда в качестве источника данных в проекте SSIS выступает документ Excel, есть несколько вариантов развития событий. Первый – разработчик знает в каком листе хранятся нужные данные и этот лист имеет статическое имя. Второй – данные находятся на листе, имя которого неизвестно, но его можно определить (например к статической части имени листа добавляется дата создания). Третий – разработчик не знает на каком листе могут оказаться нужные данные и каким образом формируется имя листа.

Рассмотрим варианты развитя события подробнее.

Первый. Лист имеет статическое имя.

Здесь все предельно просто, достаточно добавить в Data Flow Task истоник данных документ Excel, указать путь к документу и лист с которого будут браться данные.

Второй. Имя листа формируется динамически на основе заранее известных правил.
Здесь тоже нет ничего сверъестественного. Excel Data Source позволяет указать в качестве листа-источника данных переменную. Каким образом будет формироваться переменная зависит от проекта, но не стоит забывать, что для переменных, как и для кмпонентов workflow, можно использовать выражения (expressions).

Expressions – очень мощный инструмент, которой можно и нужно применять. Во многих случаях использование выражений поможет избежать использования Script Task’ов.

Третий. Имена листов неизвестны, сформировать имя листа невозможно.

Один из вариантов решения поставленной задачи – использование скрипт-таска, о чем хорошо написано, правда на англицокм, здесь. Но несмотря на бОльшую гибкость, которую дает скрипт-таск, все таки лучше стараться прибегать к нему в самых крайних случаях и стараться реализовывать поставленную задачу с помощью комопнентов, входящих в стандартую поставку. Поэтому мы пойдем другим путем.

Для начала нужно создать новый ADO.NET Connector, в качестве провайдера для которого будет использоваться Microsoft Jet OLE DB Provider.

Далее можно смело перейти на вкладку All, на ней нас интересуют параметры Data Source, где указывается путь к файлу Excel, и Extended Properties.

Далее нужно создать строковую переменную, в которую будет записываться имя листа Excel. В каком диапазоне должна быть видна переменная будет зависеть от задач, но можно не стесняясь сделать ее доступной всему пакету.

Теперь бросаем For Each Loop Container на Control Flow и лезем его настраивать. В качестве нумератора будем использовать Foreach ADO.NET Schema Rowset Enumerator. Установим в качестве соединения ADO.NET Connector, а в качестве схемы Tables.


А затем во влкадке Variables привзяываем созданную переменную к Index = 2. Если все сделано правильно, то при запуске пакета цикл For Each последовательно пройдется по всем листам и запишет имя листа в переменную.

В качестве простой полезной нагрузки, можно бросить в контейнер Data Flow Task, в который поместить Excel Source, в настройках которого указать, что имя листа берется из переменной, и какую-либо БД в качестве назначения.

Ссылки:

TechNet: http://technet.microsoft.com/en-us/library/ms345182.aspx

Рубрика Business Intelligence | Метки , | 19 комментариев

Применение OPENQUERY для работы со связанными серверами

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

Понятно, что в данном случае чистый SELECT – не вариант, нужно ограничивать запрос с помощью WHERE. Вот только механизм работы linked серверов устроен так, что связанный сервер сначала возвращает весь объем данных на сервер с которого пришел запрос, а уж потом сам MS SQL применяет к массиву данных условие WHERE.

Для того чтобы обойти это ограничение необходимо использовать OPENQUERY. Запрос, завернутый в эту функцию, связанный сервер обработает на своей стороне и вернет готовый результат.

Приятно и то, что результат работы OPENQUERY можно использовать в качестве исходной таблицы в INSERT, UPDATE и DELETE запросах, и, разумеется, в обычном SELECT’e.

В качестве аргументов функции передаются: имя связанного сервера и текст запроса.

В общем виде OPENQUERY можно применить следующим образом:

SELECT * FROM OPENQUERY(linked_server, 
'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db 
WHERE remote_table.column_N IN (''value_1'',''value_2'')')

В связи с тем, что сам текст SQL-запроса в OPENQUERY должен быть окружен апострофами, то, для того, чтобы параметры для поиска были правильно обработаны, их нужно заключать в двойные апострофы.

OPENQUERY и динамические SQL-запросы

MSDN честно предупреждает, что в качестве аргументов OPENQUERY нельзя применять параметры. Поэтому, если применять OPENQUERY так, как показано в примере выше, о динамическом SQL можно забыть.

Чтобы обойти это ограничение, приведенный выше запрос можно завернуть в апострофы, затолкать его в переменную, а потом подсунуть эту переменную функции EXEC.

Предположим, что в условии WHERE, я хочу применить некий набор значений, который можно получить селектом из таблицы, потом затолкать эти значения в OPENQUERY и выполнить сформированный запрос на связанном сервере, а полученный результат записать в таблицу.

DECLARE @values varchar(MAX)
DECLARE @sql varchar (MAX)

SET @values = (SELECT [Column1] + ',' FROM [DB].[Table1]
GROUP BY [Column1] FOR XML PATH(''))
SET @values = REPLACE(@values,',',''',''')

SET @sql = 'SELECT remote_table.column_1, remote_table.column_2
FROM remote_db WHERE remote_table.column_N IN (''' + @values + ''')'

SET @sql = N'INSERT INTO [Table2] SELECT *
FROM OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')'

EXEC (@sql)

Обратить внимание следует на следующие моменты: во-первых,  в параметр @values, имеющий тип varchar, нельзя передать несколько значение из таблицы, так как это строковый параметр. Поэтому приходится объединять все значения из таблицы в одну строку, разделяя их запятыми. Для этого вполне можно применить XML PATH.

Во-вторых, главное не косячить с апострофами. Поэтому для начала я обрабатываю строку @values, проходясь по ней REPLACE’ ом, а после того, как будет сформирован запрос к связанному серверу, прохожусь по нему REPLACE’ом еще раз, экранируя апострофы, чтобы гамотно завернуть всю конструкцию в OPENQUERY.

Конечный SQL-запрос хранится в параметре @sql, который и передается функции EXEC.

Ограничение на длину запроса

К сожалению, когда в качестве значений для условия WHERE используется SELECT из таблицы, всегда есть вероятность получить довольно большо количество данных. В то же время, максимальная длина параметра @sql не может превышать 8000 символов. Поэтому при неудачном стечении обстоятельств в функцию EXEC будет передан обрезанный SQL запрос, который вернет ошибку.

Я решил бороться с этим, отправляя на связанный сервер запрос, содержащий максимум 250 значений в условии WHERE за раз.

DECLARE @values varchar(MAX)
DECLARE @sql varchar (MAX)
DECLARE @counter numeric(6,2);

SET @counter = (SELECT COUNT([Column1]) FROM [DB].[Table1] GROUP BY [Column1])
-- Так как количество значений, передаваемых за раз условию WHERE
-- 250, то количество полученных рядов нужно разделить на размер пакета.
-- Если рядов будет меньше 250, то достаточно одной итерации, поэтому
-- счетчик устанавливается в значение 1
SET @counter = CEILING(@counter/250)

WHILE @counter <> 0
BEGIN
-- Так как в каждой итерации данные записываются в результирующую таблицу, то
-- можно построить запрос для формирования DynamicTable таким образом, чтобы в нее
-- возвращались только те значения, для которых еще не загружены данные.

WITH DynamicTable AS
	(SELECT A.[Column1] FROM [DB].[Table1] AS A WHERE NOT EXISTS
(SELECT B.[Column1] FROM [DB].[Table2] AS B
WHERE B.[Column1] = A.[Column1])
GROUP BY [Column1])
SELECT @values = (SELECT TOP 250 [Column1] + ',' FROM DynamicTable FOR XML PATH(''))
SET @values = REPLACE(@values,',',''',''')

SET @counter = @counter - 1

SET @sql = 'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db
WHERE remote_table.column_N IN (''' + @values + ''')'

SET @sql = N'INSERT INTO [Table2] SELECT * FROM
OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')'

EXEC (@sql)

END

Ссылки:

OPENQUERY на MSDN: http://msdn.microsoft.com/ru-ru/library/ms188427.aspx
Динамический SQL запросы (Английский): http://www.sommarskog.se/dynamic_sql.htm

Рубрика TSQL | Метки | 1 комментарий

Обработка значений NULL в параметрах SSRS

В процессе гугления информации по NULL-value параметрам, была найдена интересная SQL функция COALESCE(field1,field2,fieldN).

Прелесть ее в том, что ей можно передать неограниченное число аргументов, в роли которых могут выступать и параметры, и столбцы в таблице. Аргументы будут последовательно проверяться на наличие в них чего-то кроме NULL. Соответственно из всех аргументов функция COALESCE вернет первый не равный NULL.

Подробнее почитать о ней можно на MSDN: http://msdn.microsoft.com/ru-ru/library/ms190349.aspx

Рубрика Tips and Tricks | Метки , | 2 комментария