透過自訂 Component 呈現 SSIS Error Column 的名稱和 Error Description

Winnie 提的一個有趣的問題,解答在 https://naseermuhammed.wordpress.com/tips-tricks/getting-error-column-name-in-ssis/ 這篇 Blog,可惜提供的是 SSIS 2012 以及 Code page 1252,我將 Sample Code 改成 SSIS 2014 以及 Code Page 950

自訂 Data Flow 的 Error Column Name 元件與範例 Package 的 Sample Code

這個客製化元件需要正確資料和錯誤資料流都經過它,才能找到 LineageID 所代表的欄位名稱。以這個例子而言,因為錯誤資料流不會提供轉換失敗的欄位出來,僅有原始資料欄位。因此若只拉錯誤資料流,會造成找不到 LineageID 的錯誤。所以這個 Component 的程式碼只在正確 Input 找 LineageID,它在初始化加 Input 事先加 Error Input,再加 Success Input

public override void ProvideComponentProperties()
{
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Error Input";
input.Dangling = false;
input.Description = "This is for Initialize component.";

IDTSInput100 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Success Input";
input2.Dangling = false;
input2.Description = "This is for Initialize component.";

但它在執行時,透過 LineageID 找 Column Name 只在 Success Input 找 ID

IDTSInput100 input = ComponentMetaData.InputCollection[1];
IDTSInputColumn100 column1 = input.InputColumnCollection.GetInputColumnByLineageID(Lineage);

所以針對會發生失敗的元件一定要將成功、失敗兩條輸出都設到"Error Column Name"兩個分別對應的輸入

image

為了強化提醒效果,也在元件說明加入這一段:

image

針對檔案來源,目標資料表有就修改,沒有就新增

範例封裝

來聽課的朋友希望示範透過查閱(Lookup)示範這個情境:

若檔案來源有兩筆相同主鍵的紀錄,第一筆是新增,第二筆要修改,批次查閱會造成當下查閱都不存在,而都導向新增。示意圖如下:

image

資料內容如下:

1    a
2    b
1    aa
3    c

原目標資料表是空的,期待結果:

image

我的解法是單筆 Lookup,但這就需要用 Script Component 當作 Source

image

Get all records 資料流:

image

Single record lookup 資料流:

image

 

最後要強調的是,凡是改成單筆運作,在大資料量時,一定損傷效能,若商業邏輯允許,儘量批此載入到中介資料表,然後透過 T-SQL 處理可能較佳

多歧路亡羊,少歧路亡心,因為沒了歧路沒了經驗與成長…可以這麼說嗎

看到網路上張愛玲的句子:

善於妥協的女人,很寶貴。但是,只善於妥協的女人,很廉價

這很容易造句,把"妥協"換成任何一種人性,例如誠實、正直…等,似乎都如此,就算是負面的依然可用,例如憤怒、欺騙…。

但又有另一個李小龍的句子廣為流傳:

不怕練過一萬種踢法的人,我只怕將一種踢法練過一萬次的人。

同樣地,"練過一萬種踢法"也很好替換。

似乎是,人生與記憶要廣,理智與技藝要深…

平行複製檔案

朋友需要將目錄下多檔案平行複製到多家直營店,簡單寫個 SSIS 的範例,透過變數設定平行執行的工作量,藉以調節總部可承載的網路頻寬:SampleCode

image

安裝完 AS 後切換 Tabular 和 MultiDimensional 模式

感謝巧玫提供這有趣的網址:

http://caryhsu.blogspot.tw/2013/04/analysis-service.html

http://cathydumas.com/2012/04/23/changing-an-analysis-services-instance-to-tabular-mode/

透過 XQuery 進行 base64 編碼

參考以下網址:http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)
set @source = convert(varbinary(max), ‘Hello Base64′)
set @encoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@source"))’, ‘varchar(max)’)
set @decoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@encoded"))’, ‘varbinary(max)’)

select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar

看來若要寫自訂函數,encode 可以將 binary 轉成 varchar,decode 將 varchar 轉成 binary

create function dbo.fnBase64Encode(@Binary varbinary(max))
returns varchar(max)
as
begin
    declare @encoded varchar(max)
    set @encoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@binary"))’, ‘varchar(max)’)
    return @encoded
end
go

create function dbo.fnBase64Decode(@Varchar varchar(max))
returns varbinary(max)
as
begin
    declare @decoded varbinary(max)
    set @decoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@varchar"))’, ‘varbinary(max)’)
    return @decoded
end
go

 

–驗證

declare @hash binary(20)=HASHBYTES(‘sha1′,’Password’)
select @hash ‘雜湊編碼後的二進位資料’,
datalength(dbo.fnBase64Encode(@hash)) ‘Base64 編碼後的長度’
select dbo.fnBase64Encode(@hash) ‘Base64 編碼後內容’,
dbo.fnBase64Decode(dbo.fnBase64Encode(@hash)) ‘Base64 解碼後原始資料’

結果如下:

image

透過 T-SQL 發送訂閱報表

use reportserver
go
declare @eventData uniqueidentifier
select @EventData=[EventData] from [dbo].[Subscriptions] s
join [dbo].[ReportSchedule] r on s.SubscriptionID=r.SubscriptionID
join [dbo].[Schedule] c on r.ScheduleID=c.ScheduleID
where s.Description=N'<建立訂閱時的描述>’
exec [ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=@eventData

image

Where 要填的內容就是上述的描述欄位

透過設定報表變數改變單雙列的背景顏色

建立報表變數 LineTotalNo 後

image

在報表增加函數

Public Function SetNo(val as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable)  As Integer
    val.Value = val.Value + 1
    Return val.Value
End Function

Public Function GetNo(val as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable)  As Integer
    Return val.Value
End Function

一個格子設定變數值,其他的格子取值就可以設定背景顏色單雙列不同

image
簡單報表範例:

https://onedrive.live.com/?cid=BF14192BD27975CB&id=bf14192bd27975cb%212277

比較 Cursor 的執行狀況

Ellie 和 Abby 提了一個有趣的問題,因為 Oracle 對 SQL Server 的存取會透過 Cursor,而針對 table 與 view 的 cursor 查詢,是否有效能差異?

簡單實驗如下:

use tempdb
create table tb(c1 int identity primary key,c2 varchar(100) default(‘Hello’),c3 datetime2(0) default(getdate()))
insert tb default values
go
insert tb(c2) select c2 from tb
go 20

create view vw
as
select top(10000) * from tb order by c1
go

declare @p1 int,@p2 int,@p5 int=1,@p6 int=1,@p7 int=1000000
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N’select * from tb’, @p5 output,@p6 output,@p7 output
select @p1,@p2,@p5,@p6,@p7
set statistics io on
set statistics time on
exec sp_cursorfetch @p2,2,1,100
set statistics io off
set statistics time off

–觀察 cursor
declare @cur cursor
exec sp_cursor_list @cur output,3 –local 和 global
fetch @cur
/*
model:smallint  
1 = 不區分 (或靜態) 
2 = 索引鍵集 
3 = 動態 
4 = 向前快轉
*/
DEALLOCATE @cur

go

declare @p1 int,@p2 int,@p5 int=1,@p6 int=1,@p7 int=1000000
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N’select * from vw’, @p5 output,@p6 output,@p7 output
select @p1,@p2,@p5,@p6,@p7
set statistics io on
set statistics time on
exec sp_cursorfetch @p2,2,1,100
set statistics io off
set statistics time off
–觀察 cursor
declare @cur cursor
exec sp_cursor_list @cur output,3 –local 和 global
fetch @cur
/*
model:smallint  
1 = 不區分 (或靜態) 
2 = 索引鍵集 
3 = 動態 
4 = 向前快轉
*/
DEALLOCATE @cur

就我實驗的結果,兩者雖然開的 Cursor Type 相同,都是 Keyset:

clip_image001

但 Keyset type 的 Cursor 在跑的時候,會參照開 Cursor 時的定義,所以會有效能的差異

clip_image002

換句話說,View 要寫得有效率才好,畢竟 View 有可能有欄位計算定義,而不僅是查詢資料表欄位,所以執行計畫需要照 View 來做。但 Order by 一定不是一個有效的語法…

若不是整個資料表都要轉到 Oracle,我想透過 View 是比較好,直接取 table 有可能全資料表都過去 Oracle 了,但寫 View 的語法要小心…

SQL Server 2016 原生編譯預存程序終於較為好用了

SQL Server 2016 版線上說明表列對原生編譯預存程序的支援

https://msdn.microsoft.com/zh-tw/library/dn452283(v=sql.130).aspx

缺掉粗體字的這幾項,SQL Server 2014 的原生預存程序能用的情境真少…

關注

有新文章發表時,會立即傳送至你的收信匣。

加入其他 27 位關注者