Tag Archives: SSIS

將封裝資料流程工作的 Data Streaming Destination 物件之輸出,透過 SQL Server 的 View 查詢

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/walkthrough-publish-an-ssis-package-as-a-sql-view?view=sql-server-2017

SSIS 2017 後,透過 SQL Server Integration Services 資料摘要發行精靈將封裝資料流程工作的 Data Streaming Destination 物件之輸出,透過 SQL Server 的 View 查詢

SSISDataFeedPublishWizard.png

精靈會自動產生一個連結伺服器,以及 View

SSISPackageView

廣告

讓 SSIS 可以執行 PowerShell 指令

image

要開啟 Policy:

gpedit.msc

image

image

SSIS 部署不同版本專案時的描述

image

image

SSIS 2017 不支援 Web Service Task?

上課時發現的錯誤:

[Web 服務工作] 錯誤: 發生錯誤,並出現下列錯誤訊息: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: 無法執行 Web 方法。錯誤為: 無法載入檔案或組件 ‘Microsoft.SqlServer.WebServiceTask, Version=14.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ 或其相依性的其中之一。 系統找不到指定的檔案。。
   於 Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   於 Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()"。

SSDT 的 SSIS 專案退到 2016,就可以正常呼叫 Web Service 並傳回結果。因為純粹上課 Demo,這 Task 與 Web Service 已經不流行,也就不追究了…

SSIS 一般檔案連線管理員指定不可見的控制字元

先取到控制字元,例如

image

再把結果 Copy/Past 到資料行分隔符號

image

參考:https://stackoverflow.com/questions/28201271/how-to-set-the-flat-file-column-delimiter-to-an-unprintable-character

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