Имя столбца и описание ошибки в потоке ошибок SSIS

К большому сожалению в SSIS, по крайней мере 2008 R2, при направлении строки, содержащей ошибку в поток ошибок, из дополнительной информации, позволяющей локализовать проблемную ячейку, разработчику предоставляется только ID ошибки и ID ячейки.
Естественно в реальных логах от этой информации мало проку, ведь в базу желательно положить вменяемое описание ошибки, имя столбца, а также проблемное значение. На мой взгляд, бессмысленно создавать отдельную таблицу с набором столбцов для каждого отдельно взятого пакета.
В идеале, таблица для хранения вывода потока ошибок должна иметь следующий вид:
[Error ID] — идентификатор ошибки;
[Error Description] — описание ошибки;
[Error Column ID] — идентификатор столбца;
[Error Column Name] — имя столбца;
[Error Column Type] — тип данных;
[Error Column Length] — максимальный размер данных в ячейке;
[Error Task Name] — задание, с которого был перенаправлен ряд с ошибкой;
[Error Package Name] — имя пакета;
[Error Value] — значение ячейки, вызвавшей ошибку;
[Record TimeStamp] — время события.

Проблема с получением ID ошибки решается довольно просто при помощи Script Task’а и нескольких строк кода:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
     Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    }

C получением имени столбца дела обстоят несколько сложнее. Дело в том, что на уровне пакета SSIS идентификатором столбца является не имя, а Lineage ID, который уникален для каждого входящего и исходящего потока данных для каждого компонента в пакете SSIS.

Таким образом для того, чтобы получить имя столбца имея его Lineage ID, необходимо иметь таблицу соответствий айдишников и названий столбцов.

В своем блоге, посвященном SSIS, Бенни Остин предлагает использовать свободно распространяемую утилиту Dataflow Discover (DFLD), которая позволяет извлечь эту и другую полезную информацию из тела SSIS пакета и положить ее в таблицу в базе, которая в дальнейшем может быть использована для получения соответствий.

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

Более правильный и изящный подход предполагает получение словаря соответствий динамически с помощью Script Task’а, о чем можно почитать здесь:
How To Find Out Which Column Caused SSIS To Fail
или здесь:
Retrieving SSIS Error Column Names

Либо можно использовать отдельный компонент SSIS, который сделает всю работу. На CodePlex мне удалось найти два компонента: eLog, который судя по описанию, делает все что нужно, но к сожалению для него отсутствуют не только исходный код, но и скомпилированная библиотечка (в своем блоге автор написал, что код используется в одном из сторонних коммерческих компонентов); и Error Output’s Description (EOD).

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

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

Для примера, вывод модифицированного EOD компонента выглядит вот так:

DataFlow компонент Error Output’s Description Mod можно скачать отсюда: ErrorOutputDescriptionMod.zip
Библиотека тестировалась только в SSIS 2008!!!

Отдельно хочу обратить внимание на то, что компоненты назначения (dataflow destination) не возвращают ID ошибки и ID столбца, поэтому для них описанные выше подходы и компонент не работают.

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

Логи в SSIS

Провайдеры логов (logging providers).
Самый простой вариант организации логирования в пакетах SSIS — использование logging providers. Это решение позволяет писать логи в текстовые файлы, в базу данных или, например, в журнал операционной системы. Плюс можно гибко настраивать какие события для какого компонента будут писаться в логи. Как правило я оставляю только события OnError, дабы таблица сильно не дулась, хотя для целей отладки вполне можно фиксировать события OnPreExecute и OnPostExecute.

Обработчики событий (event handlers).
Как показывает практика, информация, которую пишут провайдеры логов, не всегда достаточно. Особенно это проявляется в случаях, когда через SSIS проходит большое число файлов для загрузки. И на основе информации, поступившей от logging provider’а, не всегда можно быстро выявить файл, с которым возникли проблемы. К счастью SSIS дает возможность работы с обработчиками событий, что позволяет значительно расширить функционал классического провайдера логов.

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

Для начала создается таблица, в которой будут храниться логи:

CREATE TABLE [Logging].[UploadedFiles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File Name] [nvarchar](255) NOT NULL,
[Task ID] [nvarchar](255) NOT NULL,
[Task Name] [nvarchar](255) NOT NULL,
[Package ID] [nvarchar](255) NOT NULL,
[Package Name] [nvarchar](255) NOT NULL,
[Action Object] [nvarchar](25) NOT NULL,
[Action Flag] [int] NOT NULL,
[Action TimeStamp] [datetime] NOT NULL,
[Record Locked] [bit] NOT NULL
) ON [PRIMARY]
GO

В ней фиксируются имя загружаемого файла, идентификатор и имя задачи, идентификатор и имя пакета.
На поле [Action Object] я остановлюсь подробнее. В связи с тем, что меня интересует состояние загрузки данных в базу данных или на FTP сервер, то в поле [Action Object] падает либо строка ‘FTP’, либо ‘DB’. Естественно можно настроить систему и на мониторинг других задач, например, отправку писем, с помощью соответствующего таска SSIS. Поле [Action Flag] хранит статус выполнения: -1 — ошибка, 0 — в процессе загрузки, 1 — успешное выполнение. [Record Locked] — служебное поле, о нем расскажу чуть ниже.

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

create procedure [Logging].[sp_LogSsisEvents]
@LogFileName nvarchar(255) = null, -- Name of the file being logged
@TaskID nvarchar(255) = null,		-- ID of the component that raised event
@TaskName nvarchar(255) = null,	-- Name of the component that raised event
@PackageID nvarchar(255) = null,	-- ID of the package that raised event
@PackageName nvarchar(255) = null,	-- Name of the package that raised event
@ActionObject nvarchar(10) = null,	-- DB or FTP
@EventType nvarchar(25) = null,	-- Name of the event
@ComputerName nvarchar(100) = null	-- Computer's network name

/*	Action Object
Type of the object where action is performed:
FTP
DB
*/

/*	Event Type
Type of events that trigger logging
OnPreExecute
OnPostExecute
OnError

[Additional Events For Reporting Purposes]
null -- Return events summary total vs. errors
GetErrorsDetails
*/

/* Action Flags
-1 Failed
0 In Progress
1 Success
*/

--@ActionFlag int
as
begin
declare @sql nvarchar(MAX)
declare @sqlinsert nvarchar(MAX)
declare @sqlupdate nvarchar(MAX)
declare @filepath nvarchar(100)

-- Part common for all queries
set @sqlinsert = 'insert into [Logging].[UploadedFiles]([File Name],[Task ID],[Task Name],[Package ID],[Package Name],[Action Object],[Action Flag],[Action TimeStamp],[Record Locked]) '
set @sqlupdate = 'update  [Logging].[UploadedFiles] '

set @sql =

case
when @EventType = 'OnPreExecute' then
@sqlinsert +
'values(''' + @LogFileName +''',''' + @TaskID + ''',''' + @TaskName + ''',''' + @PackageID + ''','''
+ @PackageName + ''',''' + @ActionObject + ''',0,GETDATE(),0)'
when @EventType = 'OnPostExecute' then
@sqlupdate +
'set [Action Flag] = 1, [Record Locked] = 1
where [File Name] = ''' + @LogFileName + ''' and [Action Object] = ''' + @ActionObject + ''' and [Action Flag] <> -1 and [Record Locked] <> 1'
when @EventType = 'OnError' then
@sqlupdate +
'set [Action Flag] = -1, [Record Locked] = 1
where [File Name] = ''' + @LogFileName + ''' and [Action Object] = ''' + @ActionObject + ''' and [Record Locked] <> 1'

end
exec (@sql)
end
go

В числе прочих параметров хранимой процедуре передается параметр @EventType, который содержит информацию о том, какое событие произошло на уровне пакета SSIS. В зависимости от этого формируется динамический SQL запрос. В связи с тем, что первым из числа логируемых событий вызывается OnPreExecute, в динамическом запросе используется переменная @sqlinsert, все последующие события используют @sqlupdate, для обновления статусов.
Так как событие OnPostExecute наступает независимо от наступления события OnError, флаг [Record Locked] используется для того, чтобы исключить изменение данных в таблице.

Теперь можно вносить изменения в пакет SSIS. Для начала создаются необходимые переменные:
logActionObject_DB Значение: DB
logActionObject_FTP Значение: FTP
logEventType_OnError Значение: OnError
logEventType_OnPostExecute Значение: OnPostExecute
logEventType_OnPreExecute Значение: OnPreExecute
logFileName Значение: Присваивается динамически

Как я писал выше записи в таблице с логами будут разделяться по типу объектов, в моем случае — база данных и ФТП-сервер. Если необходимо вести логи для других объектов, для каждого из них необходимо создать свою переменную. Также потребуются переменные для каждого события — то есть OnError, OnPostExecute, OnPreExecute. Переменная logFileName формируется динамически.

Далее на вкладке Event Handlers создается обработчик для необходимых событий. Благодаря использованию хранимой процедуры он будет состоять из одного Execute SQL Task:


Execute SQL Task настраивается для вызова созданной ранее хранимой процедуры:


И для параметров процедуры мапятся соответствующие системные и пользовательские переменные:

Ключевой момент — к параметрам 5 и 6 должны мапиться переменные для соответствующего события и объекта, над которым выполняется действие (DB, FTP и т.д.)

Состояние загрузок можно посмотреть напрямую в базе или создать отчет SSRS:

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

Объединение строк в TSQL

select T1.Field1, coalesce(
					 stuff(
					   (select ',' + cast([Field1] as nvarchar(20)) as [text()]
						from Table2 T2
						where T2.Field2 = T1.Field2
						order by [Field1]
						for xml path(''), type).value('.','nvarchar(max)'),
						1,1,''),
						'') as [ConcatField2]
from T1

COALESCE — возвращает пустую строку, если объединение строк вернуло NULL;
Функция STUFF удаляет первый символ из строки — запятую;
PATH(») означает, что при формировании строки не будут использованы XML-теги;
type — функция вернет XML, который будет конвертироваться в текст методом ‘value’ (это делается для того, чтобы сохранить символы, которые в противном случае были бы восприняты как элементы XML разметки;
метод value() — берет корневой элемент ‘.’ и конвертирует его в текст ‘nvarchar(max)’

Ссылки:
Различные методики объединения строк в TSQL
Синтакс FOR XML

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

Динамические отчеты в SSRS

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

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

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

create  procedure [sp_DynamicReporting]
@QryID int -- Идентификатор запроса к БД
as
begin
set nocount on;
declare @sql varchar(max) -- Переменная для хранения динамического SQL
set @sql =
case
when @QryID = 0	then 
'
select ''1'' as QryID, count(distinct Field1) as AggValue1 from Table1 Where A = 1 and B =2
union
select ''2'' as QryID sum(Field2) as AggValue1 from Table 1 where A = 2 and B =3
'
when @QryID = 1	then 'select * from Table1 Where A = 2 and B = 3'
when @QryId = 2 then 'select * from Table1 Where A = 1 and B =2'
end

exec (@sql)
end

Приведенная хранимая процедура возвращает таблицу агрегированных значений, если параметр @QryID равен 0, и наборы исходных данных, если параметру переданы иные значения.
Также хочу обратить внимание на запрос, который выполняется для @QryId = 0. В каждой строке помимо данных будет возвращаться поле [QryID]. Это сделано для того, чтобы серверу SSRS было откуда брать ID запросов при вызове хранимой процедуры для детализированных отчетов.

К большому сожалению нынешний SSRS не позволяет динамически менять названия столбцов в dataset, который используется в отчете, плюс нет возможности динамически изменять количество столбцов.

Чтобы решить первую проблему, придется немного подправить хранимую процедуру.

alter procedure [sp_DynamicReporting]
@QryID int -- Идентификатор запроса к БД
as
begin
set nocount on;
declare @sql varchar(max) -- Переменная для хранения динамического SQL
set @sql =
case
when @QryID = 0	then -- Сводный отчет
'
select ''1'' as QryId, count(distinct Field1) as AggValue1 from Table1 Where A = 1 and B =2
union
select ''2'' as QryID, sum(Field2) as AggValue1 from Table 1 where A = 2 and B =3
'
when @QryID = 1	then 
'
select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, Col2, Col3 from Table1 Where A = 2 and B = 3
' 
when @QryId = 2 then 
'
select ''Field1_Name'' as Col1, "Field2_Name'' as Col2, ''Field3_Name'' as Col3, ''Field4_Name'' as Col4
union all
select Col2_1, Col2_2, Col2_3, Col2_4 from Table1 Where A = 1 and B =2
'
end

exec (@sql)
end

При использовании динамического SQL не забываем экранировать апостроф другим апострофом!!!

В измененной процедуре особое внимание стоит уделить изменениям в динамическом SQL для формирования детализированных отчетов.

select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, Col2, Col3 from Table1 Where A = 2 and B = 3

Единственный способ скормить SSRS табличные данные, полученные с помощью разных запросов — унифицировать названия столбцов. Первый вариант — присвоить алиасы столбцам в исходном запросе, но он плох тем, что при работе с репортом SSRS пользователь никак не сможет понять какие данные лежат в ячейках. Поэтому перед выборкой из боевой таблицей я добавляю еще один SELECT, где банально указываю названия столбцов и присваиваю алиасы Col1, Col2,…,ColN, т.к. именно их я буду использовать при создании отчета.
Стоит обратить внимание, что типы данных для всех полей в строке 1 — nvarchar, поэтому если SELECT в строке 3 возвращает значения с типами данных отличными от nvarchar, нужно конвертировать эти данные в текст руками, например с помощью CAST.

select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, CAST(Col2 as nvarchar(15)) as Col2, Col3 from Table1 Where A = 2 and B = 3

Данные, возвращаемые процедурой будут выглядеть примерно так:

Подобным образом стоит изменить все запросы, которые возвращают исходные данные.
Унифицированные названия столбцов решают проблему с формированием одного dataset’а для всех отчетов, а наличие в первой строке реальных названий столбцов — вопрос с заголовками столбцов в отчете SSRS.

Отчет SSRS

Теперь в двух словах остановлюсь на создании отчета.

При создании dataset’а, я прикрутил к нему описанную ранее хранимую процедуру и руками прописал имена столбцов, которые она возвращает. Так как названия столбцов одинаковы для всех возвращаемых результатов, то единственная переменная величина — их количество. В данном случае действует простое правило — количество столбцов в dataset должно быть равно максимальному количеству столбцов, которое может вернуть хранимая процедура.

И прикручиваю к dataset’у параметр QryID:

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

Таблицу в SSRS нужно настроить таким образом, чтобы она могла возвращать все столбцы dataset’а:

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

=iif(First(Fields!Col6.Value, "Dataset_Name") = "",True,False)

И теперь последний штрих — выражение, чтобы подменять заголовки столбцов в таблице:

=First(Fields!Col1.Value, "Dataset_Name")

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

Далее можно создать еще один отчет, в котором будут отображаться сводные данные. Иными словами, нужно прикрутить к этому отчету dataset, который будет вызывать хранимую процедуру с параметром @QryId = 0, а для ячеек таблицы настроить вызов детализированного отчета по клику мыши и передать ему в качестве значения параметра ID запроса.

Итог
Теперь если возникнет необходимость добавить какие-либо дополнительные сводные данные и детализацию в отчеты, достаточно отредактировать только хранимую процедуру.

Рубрика Business Intelligence, TSQL | Метки , , | Комментарии (3)

Загрузка изображений в БД

Вопрос о том где хранить изображения — в базе данных или файловой системе — я оставлю за рамками этой записи. Как всегда все зависит от поставленных задач.

Когда в нашей компании решался вопрос о необходимости хранения изображений в базе данных, основным моментом был объем хранимой информации. Исходные 200000 изображений, которые предстояло загрузить в БД занимали около 16 гигабайт, но были при этом сохранены в высоком разрешении. В то же время их предполагалось использовать исключительно при формировании отчетов SSRS, с последующей выгрузкой их в Excel, а для этих целей размера в 150 пикселей по длинной стороне вполне достаточно.

Изначально загрузку предполагалось реализовать с помощью SSIS, но впоследствии было решено написать небольшое приложение на C# и реализовать там простейший GUI, ресайз изображений, загрузку изображений в базу, а оригиналов в высоком разрешении в файловую систему (по желанию пользователя).

На сервере баз данных была создана целевая таблица, содержащая два столбца — ID изображения (ImageID), которое является первичным ключом, и совпадает с именем файла (без расширения), и поля ImageData, типа ‘image’.

Для ресайза и сжатия изображения, использовался код, который был нагло скопипащен отсюда.


private Image resizeImage(Image imgToResize, Size size)

{
int sourceWidth = imgToResize.Width;
int sourceHeight = imgToResize.Height;

float nPercent = 0;
float nPercentW = 0;
float nPercentH = 0;

nPercentW = ((float)size.Width / (float)sourceWidth);
nPercentH = ((float)size.Height / (float)sourceHeight);

if (nPercentH < nPercentW)
nPercent = nPercentH;
else
nPercent = nPercentW;

int destWidth = (int)(sourceWidth * nPercent);
int destHeight = (int)(sourceHeight * nPercent);

Bitmap b = new Bitmap(destWidth, destHeight);
Graphics g = Graphics.FromImage((Image)b);
g.InterpolationMode = InterpolationMode.HighQualityBicubic;

g.DrawImage(imgToResize, 0, 0, destWidth, destHeight);
g.Dispose();

return (Image)b;
}

private void saveJpeg(string path, Bitmap img, long quality)
{
// Encoder parameter for image quality
EncoderParameter qualityParam =
new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, quality);

// Jpeg image codec
ImageCodecInfo jpegCodec = getEncoderInfo("image/jpeg");

if (jpegCodec == null)
return;

EncoderParameters encoderParams = new EncoderParameters(1);
encoderParams.Param[0] = qualityParam;

img.Save(tempImageFile, jpegCodec, encoderParams);
}

private ImageCodecInfo getEncoderInfo(string mimeType)
{
// Get image codecs for all image formats
ImageCodecInfo[] codecs = ImageCodecInfo.GetImageEncoders();

// Find the correct image codec
for (int i = 0; i < codecs.Length; i++)
if (codecs[i].MimeType == mimeType)
return codecs[i];
return null;
}

После того как размер изображения изменен и оно сохранено во временный файл, происходит загрузка в базу. Здесь нужно учитывать, что ImageID — первичный ключ, поэтому если просто попытаться сделать INSERT, есть вероятность натолкнуться в базе на уже существующий ImageID. Чтобы избежать дополнительных запросов и проверок на этапе выполнения SQL-запроса, я использовал конструкцию TRY…CATCH.


                        /* Call functions to resize image and save it to temporary location. */
                        this.img = Image.FromFile(FilePath);
                        img = this.resizeImage(img, new Size(maxWidth, maxHeight));
                        this.saveJpeg(saveFileDia.FileName, new Bitmap(this.img), jpgQuality);

                        /* Reading image contents into array, it will be used later as a data stream
                         * for SQL query parameter. */
                        FileStream fs;
                        fs = new FileStream(tempImageFile, FileMode.Open, FileAccess.Read);
                        byte[] picbyte = new byte[fs.Length];
                        fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
                        fs.Close();

                        //Open DB connection and upload pictures.
                        SqlConnection connectDB = new SqlConnection(connectionString);
                        connectDB.Open();
                        SqlCommand sqlCmd = new SqlCommand();
                        /* Destination table ImageID column
                         * marked as Primary Key.
                         * In case duplicate value is being uploaded executing query with TRY
                         * and ROLLBACK to avoid DB errors. */
                        sqlCmd.CommandText = "BEGIN TRY BEGIN TRAN INSERT INTO " +
                                 "Images ([ImageID], [ImageData]) VALUES ('" + fileName + "', @pic) " +
                                 "COMMIT TRAN END TRY BEGIN CATCH ROLLBACK END CATCH";
                        SqlParameter picparameter = new SqlParameter();
                        picparameter.SqlDbType = SqlDbType.Image;
                        picparameter.ParameterName = "pic";
                        picparameter.Value = picbyte;
                        /* Query got additional paramete @pic which should contain image data stream. */
                        sqlCmd.CommandType = CommandType.Text;
                        sqlCmd.Connection = connectDB;
                        sqlCmd.Parameters.Add(picparameter);
                        sqlCmd.ExecuteNonQuery();
                        connectDB.Close();

Ссылки:
Загрузка изображений в базу с помощью SSIS:http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/ssis-import-images-table

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

Регулярные выражения в пакетах SSIS

Самый простой и, пожалуй, правильный способ проверить корректность данных, обрабатываемых пакетом SSIS — использовать регулярные выражения.

Реальный пример — проверка номеров ГТД
Идентификатор государственной таможенной декларации состоит из трех частей, разделенных слэшами. Первые 8 символов содержат уникальный идентификатор таможенного органа, оформившего ГТД, вторая часть включает в себя дату оформления ГТД вида ‘ddMMyy’, третья — уникальный семизначный номер. Итого 23 символа. Более того, в номере ГТД могут содержаться только цифры от 0 до 9 и слэши.
Соответственно задача состоит в том, чтобы с помощью регулярных выражений проверить корректность номера ГТД, и отправить все номера, не прошедшие проверку, в отдельный поток данных.

Конечно же в SSIS нет готового компонента, позволяющего решить эту задачу, зато есть Script Task.

Прежде чем начать кодить, необходимо настроить Scrip Task таким образом, чтоб у него было два выхода, на которые пойдут корректные и неправильные номера ГТД, соответственно.

Для обоих выходов должна быть указана идентичная ExclusionGroup, а в качестве SyncronousInputID должен быть выбран идентификатор входа, по которому придут данные для проверки, в данном случае ‘Input 0’.

В качестве языка я выбрал C# и в результате получился примерно такой код…

using System;
using System.Data;
using System.Text.RegularExpressions; // Add this to use RegExps in code
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}

public override void PostExecute()
{
base.PostExecute();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (IsCorrectCDN(Row.CDN))
{
Row.DirectRowToProperCDN(); // Redirect all correct CDNs to ProperCDN output
}
else
{
Row.DirectRowToWrongCDN(); // All incorrect CDNs go to WrongCDN output
}
}
public bool IsCorrectCDN(string CDN)
{
// Check incoming CDN against RegExp and return boolean result.
string CDNPattern = @"[0-9]{8}/[0-9]{6}/[0-9]{7}";
Regex CDNRegex = new Regex(CDNPattern);
return CDNRegex.IsMatch(CDN);
}
}

Теперь на выходе Script Task будет два потока данных.

Ссылки:
RegExLib.com Regular Expression Cheat Sheet (.NET): http://regexlib.com/CheatSheet.aspx?AspxAutoDetectCookieSupport=1

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

FTP Task: Password Not Alowed

Когда в пакете SSIS используется компонент для загрузки данных на FTP сервер, вполне может возникнуть ситуация, когда во время дебага все работает, а при попытке выполнить пакет с помощью SQL Agent или, например, создав задание по расписанию, компонент FTP возвращает ошибку «Password Not Allowed».
Как видно на скриншоте, свойству ProtectionLevel присвоено значение «EncryptSensitiveWithUserKey», что означает что пароли в пакете будут шифроваться ключом пользователя, который работает с пакетом SSIS. Соответственно, когда workflow запускается в виде задания или работы SQL Agent, оно запускается скорей всего под какой-либо сервисной учетной записью, которая, в свою очередь, не может получить доступ к паролям сохраненным и зашифрованным чужим ключом.

Решить эту проблему можно двумя способами. Для начала расскажу о том, который нравится меньше:

Способ №1

Свойству ProtectionLevel можно присвоить значение «EncryptSensitiveWithPassword». То есть для защиты различной конфиденциальной информации в пакете будет использоваться пароль, назначаемый пользователем.

При вызове такого пакета, например с помощью утилиты dtexec, потребуется передать мастер-пароль в теле команды:
dtexec /f «c:\package_name.dtsx»  /De password

Способ №2

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

На этапе создания новой конфигурации придется решить в каком виде она будет храниться. BIDS предлагает на выбор несколько вариантов, включая переменные, ключи реестра, XML-файл или базу SQL. В данном случае я остановился на последнем варианте.

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

На следующем этапе нужно будет выбрать объекты, для которых в конфиге будут храниться различные атрибуты. В данном случае, я ограничусь исключительно именем пользователя и паролем доступа на FTP сервер.

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

В данном случае Login и Password — реквизиты для доступа к FTP серверу, которые нужно использовать. Более того, если компонент имеет одинаковое название во всех пакетах, один и тот же конфиг можно смело использовать для разных пакетов SSIS.

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

Небольшая подборка Tips and Tricks, скопившихся за последнее время:

1. Использую Excel Connector можно обращаться к документу Excel, используя SQL запросы типа

SELECT * FROM [Sheet1$A4:AS35]

Более того, если данные в исходном документе, в силу ограничений формата Excel 2003 разбиты на несколько листов, но при этом имеют одинаковую структуру, можно использовать UNION ALL, для того чтобы загрузить все данные из документа за один раз.
Данный подход замечательно работает, если запрос хранится в переменной и формируется динамически на основе имен листов, вытягиваемых с помощью ADO.NET Schema Rowset Enumerator из For Each Loop Container (линк).

2. При экспорте отчета SSRS в Excel иногда в структуре документа могут появляться пустые столбцы, как например столбец ‘B’ на приведенной иллюстрации.

 

 

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

Есть еще несколько причин проявления пустых столбцов, с которыми я лично не сталкивался, но которые описаны здесь.

3. Описанный ранее способ получения имен листов в документе Excel работает только с форматом образца 2003 года. Чтобы жизнь наладилась нужно для начала установить в систему набор компонентов Microsoft Access Database Engine 2010. После этого при настройке коннектора нужно выбрать Microsoft Office 12.0 Access Database Engine OLE DB Provider из раздела .Net Provider for OleDb, указать путь к источнику данных и в Extended Properties указать Excel 12.0. В остальном настройки остаются прежними, но появляется возможность получить наименования всех листов документа формата Excel 2007-2010.

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

Создание измерения времени для BI

Небольшой запрос, с помощью которого можно раз и навсегда решить вопрос с построением измерения времени для бизнес анализа.


/* На случай если согласно настройкам на сервере неделя
начинается с воскресенья, принудительно меняем начало
недели на понедельник.*/
SET DATEFIRST 1;

WITH temp as
(
SELECT Cast (‘2000-01-01’ as DateTime) Date —Start Date 
UNION ALL
SELECT Date + 1
FROM temp
WHERE Date + 1 <= '2099-12-31' --End date  ) SELECT Row_Number() OVER (ORDER BY Date) as ID ,Date ,YEAR (date) as Year ,DatePart ( qq, date) as Quarter ,MONTH (date) as [Month_Number_Of_Year] ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Month_Number_Of_Quarter] ,DatePart (wk, Date) as [Week_Number_Of_Year] ,DateDiff(wk,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Week_Number_Of_Quarter] ,DateDiff(wk,DateAdd(mm,DateDiff(mm,0,date),0),date)+1 as [Week_Number_Of_Month] ,DatePart (dy, date) as [Day_Number_Of_Year] ,DateDiff(dd,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Day_Number_Of_Quarter] ,DAY (date) as [Day_Number_Of_Month] ,DatePart (dw, date) as [Day_Number_Of_Week] ,DateName (mm, date) as [English_Month_Name] ,LEFT ( DateName (mm, date), 3) [English_Month_Name_Abbrevation] ,DateName (dw, date) as [English_Day_Name] ,LEFT (DateName (dw, date), 3) as [English_Day_Name_Abbrevation] ,CONVERT(VarChar(10),date,112) as YYYYMMDD INTO DimDate FROM temp OPTION (MAXRECURSION 0) [/sql]

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

Добавление данных в 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 | Метки | Добавить комментарий