Category Archives: SQL Server 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

將簡單的 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)就完全不可能這麼做了,升級或選用封裝開發版本務必小心

查詢 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 嗎?

Agent Job 若 Owner 是一般 SQL Login,Job Step 又要採用 Proxy 所需做的授權

以 SSIS 為例,參看:http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

SSIS Catalog 資料庫的備份與還原

SQL Server 2012 後,若 SSIS 採用 SSIS Catalog,則除了常態性地備份 SSISDB 資料庫外,還需要額外做一些動作: http://blogs.msdn.com/b/mattm/archive/2012/03/23/ssis-catalog-backup-and-restore.aspx

以 SSIS 2012 當作資料源,提供其他程式如 RS 取用

下載 http://www.microsoft.com/zh-tw/download/details.aspx?id=39931 

其詳細資料內有建立與執行範例:http://office.microsoft.com/en-us/publish-ssis-packages-as-odata-feed-sources-HA104079177.aspx

但要注意的是 SQL Server 內 Linked Server 之 SSISOLEDB Provider 的 Allow inprocess 屬性要勾選

image