Tag Archives: SSIS

SSIS 專案部署時的版本說明

若要為每一次部屬的版本加上說明:

image

一是為專案加說明

image

另一是直接修改系統資料表 internal.object_versions

image

要注意的是改完之後,一直開啟的 SSMS 若要看到這些文字,需要先退版一次,就看得到

image

SSIS 2012/2014 專案部署只部署修改某個有 Bug 的封裝

若專案已經部署且上線使用,當下開發中的 SSIS 專案繼續開發,這時線上的某個封裝錯了要修 Bug,而 2016 才可以針對特定專案版本的特定封裝個別部署(號稱的新功能,現在還沒看到),則避免開發中的封裝也一併被專案部署發行到正式機,可能的作法有:

1.透過 Source Control 調出上線的版本,單改有 Bug 的封裝後部署

2.從 Production 匯出

image

產生 ispac 檔

image

透過 SSDT 匯入後,將專案名稱取與 Production 相同,修改有 bug 的封裝後再部署

image

SSIS 直接輸入到有格式的 Excel 內

定義名稱區塊,可以參考:http://yangxinde.pixnet.net/blog/post/26182398-excel-%E9%81%B8%E5%8F%96%E7%AF%84%E5%9C%8D%E7%9A%84%E5%BB%BA%E7%AB%8B%E8%88%87%E5%88%AA%E9%99%A4%E5%90%8D%E7%A8%B1

 

image

SSIS 會將資料填入到區塊的下方,並擴增區塊。所以 template 建好後,每次從 template copy 一個新的填入

一般資料輸入到範本 Excel

image

名稱一定要兩格以上 SSIS 才看得到,可以透過縮小格子來排版

image

可新增到不同的名稱下

image

專案部署模式中,SSIS 的連接密碼以加密的方式賦予並儲存

朋友的有趣問題,我的解法如下:

設定封裝參數是 Sensitive

image

image

設定"連接"的 Password 屬性要以 SSIS Expression 套用變數

image

設定 Expression 對話窗的"屬性"欄位下拉選單選擇 Password,並賦予剛才建立需要加密的封裝參數

image

image

將專案部署到SSIS 目錄,透過 SSMS 開啟 Folder 下的環境結點

image

在 SSIS Catalog 建立需要加密的環境變數

image

專案參考剛才建立的環境

image

加入參考環境

image

針對需要加密的專案參數設定需要加密的環境變數

image

執行時搭配環境即可

image

 

之後要更改帳號密碼時,只需要呼叫 SP 即可

DECLARE @var sql_variant = N’HelloWorld’
EXEC [SSISDB].[catalog].[set_environment_variable_value] @variable_name=N’password’, @environment_name=N’env’, @folder_name=N’myFolder’, @value=@var

透過自訂 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

將簡單的 SSIS 2014 封裝轉成 2012 版本

有 30 來個相同的封裝,僅是內含執行封裝工作呼叫其他的封裝,但用的是 VS2013/BIDS 2014 開發的,但實際的 SQL Server 卻是 2012,所以簡單寫段 PowerShell 取代

$source=’C:\temp\ssis\2014\Integration Services 專案1\’
$files=dir ($source + ‘*.dtsx’)
$dest=Join-Path $source ‘temp\’
foreach($file in $files)
{
(((((Get-Content $file.FullName -encoding utf8 ) `
-replace “Microsoft.Package", “SSIS.Package.3″) `
-replace “12.0.2456.0″,"11.0.5058.0″) `
-replace ‘DTS:VersionBuild="2″‘,’DTS:VersionBuild="1″‘) `
-replace ‘DTS:Name="PackageFormatVersion">8</DTS:Property>’,’DTS:Name="PackageFormatVersion">6</DTS:Property>’ `
-replace ‘Microsoft.ExecutePackageTask’,’SSIS.ExecutePackageTask.3′) `
| Out-File (join-path $dest $file.Name) -encoding utf8
}

若封裝複雜,或是 2012(Attribute centric)換 2008 R2(Element centric)就完全不可能這麼做了,升級或選用封裝開發版本務必小心

以程式碼、console 指令、PowerShell 自動化部署 SSIS 2012 後的 ispac 檔案

http://www.mattmasson.com/2013/05/can-i-automate-ssis-project-deployment/

查詢 SQL Job 正在執行的狀態

雖然 http://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs 提供以下的查詢

SELECT sj.name,sj.*
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL

exec   sp_help_job @job_name=’test’

但我需要知道 job 實際叫起的 dtexec 之 pid,畢竟同時有一堆 SSIS Job 在執行,無法知道哪個 job 是對應到哪個 windows 上正在 run 的 dtexec process,不知大家有何解法…用 script task 自己取得 pid 存進 log 嗎?