C# 呼叫 SQL Server DTS 套件與帶回傳值的預存程序（三）

C# 端呼叫 DTS 的兩種方式

方式一：xp_cmdshell 執行 DTSRun

private void TestRunDTS() {
    string constr = "Data Source=localhost;Initial Catalog=master;User ID=sa;Password='';Connect Timeout=3000";
    using (SqlConnection conn = new SqlConnection(constr)) {
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        // DTSRun 後接套件 ID（可從排程屬性取得）
        cmd.CommandText = "EXEC master..xp_cmdshell 'DTSRun /~Z0x8E9EF55158ABA56C3C3346137F1F7B7B090F1F61D54D3981CFA1DB0E8B50C4E0D416AF2F746FA482B5E3C2AABA52D4838DAA496938F1E61155CBB055FF4082181E6BE53F08A47D7E6A82E6B77E3F83FCBAAD1B'";
        try { cmd.ExecuteNonQuery(); }
        finally { conn.Close(); }
    }
}

注意：需授權呼叫帳號存取 xp_cmdshell 擴充預存程序權限。

方式二：使用 Microsoft.SQLServer.DTSPkg80.dll COM 元件

private void TestDTS() {
    Package2Class package = new Package2Class();
    object pVarPersistStgOfHost = null;
    package.LoadFromSQLServer("localhost", "sa", "", 
        DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
        null, null, null, "DTSTest", ref pVarPersistStgOfHost);
    package.Execute();
    package.UnInitialize();
    package = null;
}

參數說明：LoadFromSQLServer(ServerName, userID, password, Flags, PackagePassword, PackageGuid, PackageVersionGuid, PackageName)

T-SQL 端建立 DTS 物件並執行

DECLARE @object INT, @hr INT, @src VARCHAR(255), @desc VARCHAR(255)
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
IF @hr <> 0 BEGIN
    PRINT 'error create DTS.Package'
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=CONVERT(VARBINARY(4),@hr), Source=@src, Description=@desc
    RETURN
END
PRINT 'create DTS.Package successful'
-- 後續接載入、設定屬性、執行、銷毀步驟（見下篇）