C#調用SSIS包(DTS)－自由手記

首先，有兩種調用方式:直接用(需要引用Microsoft.SqlServer.DTSRuntimeWrap.dll), 通過Web Service進行執行SSIS包.

如果是直接調用那麼需要引用Microsoft.SqlServer.DTSRuntimeWrap.dll，用戶端需要安裝MS SQL 2005的用戶端元件,調用的代碼如下：

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Configuration;
using DtsRunTime = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace SQLHelper
{
    public class CallSSIS{
        private string YOUR_SERVER_NAME  = ConfigurationManager.AppSettings["DTSServerName"].ToString();
        private string YOUR_PACKAGE_NAME = ConfigurationManager.AppSettings["DTSPackageName"].ToString();
        private string SQL_USER          = ConfigurationManager.AppSettings["DTSSQLUser"].ToString();
        private string SQL_PASSWORD      = ConfigurationManager.AppSettings["DTSSQLPassword"].ToString();

        /// <summary>
        /// Run DTS for SQL 2005.
        /// </summary>
        //===============================================================
        // Revision History
        // Date          Author                   Description
        // 12/07/2009    Terry Sun                Created
        //===============================================================
        public int DTSRun2005()
        {
            
            DtsRunTime.Application dtsApplication = new DtsRunTime.Application();
            // Call the .dtsx file.
            //DtsRunTime.IDTSPackage90 package = dtsApplication.LoadPackage(@"c:\test.dtsx", true, null);


            // Call SSIS package from SQL Server.
            DtsRunTime.IDTSPackage90 package = dtsApplication.LoadFromSQLServer(
                this.YOUR_PACKAGE_NAME, 
                this.YOUR_SERVER_NAME, 
                this.SQL_USER,
                this.SQL_PASSWORD,
                true, 
                null);
            
            DtsRunTime.DTSExecResult result = package.Execute();
            if (result.Equals(DtsRunTime.DTSExecResult.DTSER_SUCCESS))
                return 1;
            else
                return 0;
        }
    }
}