编程方式创建SSIS包

原创 于 2006-12-05 11:01:00 发布 · 1.9k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#ssis #编程 #string #path #null #constraints

本文介绍了一个使用SSIS（SQL Server Integration Services）创建数据整合包的示例，包括包的创建、连接设置、数据流任务配置、源与目的地设置等关键步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using DTS = Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace CreatePackage
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        #region 创建不存在的插入，存在的修改
        private void btnCreate_Click(object sender, EventArgs e)
        {
            //Step 1.Create Package
            DTS.Package package = CreatePackage("Question1", "Question1");
            //Step 2.Create Connection
            AddConnection(package, "Question", "LocalHost.Question");
           
            #region Step 3.Create DataFlow Task
            DTS.TaskHost _dataFlowTask = null;
            MainPipe dataFlow =  AddDataFlow(package, "DataFlow1",ref _dataFlowTask);
            #endregion

            #region Step 4.Create OLEDB Source
            IDTSComponentMetaData90  _oledbSource = AddOleDbSource(package, dataFlow, "OLEDBSource", "LocalHost.Question", "Table_1");
            #endregion

            #region Step 5.Createa Look Up
            IDTSComponentMetaData90 _lookup = null;
            CManagedComponentWrapper _lookupInstance = AddLookupComponent(package, dataFlow, "Look Up", "LocalHost.Question", ref  _lookup);
         
            IDTSPath90 path = dataFlow.PathCollection.New();
            path.Name = "path";
            //path.AttachPathAndPropagateNotifications(OleDbSource.OutputCollection[0], unionall.InputCollection[i]);
            path.AttachPathAndPropagateNotifications(_oledbSource.OutputCollection[0],_lookup.InputCollection[0]);


            _lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;
            //映射输入
            IDTSVirtualInput90 vInput = _lookup.InputCollection[0].GetVirtualInput();
            foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
            {
                if (vColumn.Name == "Id")
                {
                    _lookupInstance.SetUsageType(_lookup.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                }
            }
            foreach (IDTSInputColumn90 col in _lookup.InputCollection[0].InputColumnCollection)
            {
                if (col.Name == "Id")
                {
                    _lookupInstance.SetInputColumnProperty(_lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", col.Name);
                }
            }
            #endregion

            #region Step 6.Create Destination
            IDTSComponentMetaData90 _oledbDestination = null;
            CManagedComponentWrapper _oledbDestinationInstance = AddOleDbDestination(package, dataFlow, "OleDB Destination", "LocalHost.Question", ref _oledbDestination);

            IDTSPath90 path1 = dataFlow.PathCollection.New();
            path1.AttachPathAndPropagateNotifications(_lookup.OutputCollection[1], _oledbDestination.InputCollection[0]);
            path1.Name = "查找错误输出";

            IDTSVirtualInput90 vInput1 = _oledbDestination.InputCollection[0].GetVirtualInput();
           
            foreach(IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)
            {
                if (vColumn.Name != "ErrorCode" && vColumn.Name != "ErrorColumn")
                {
                    _oledbDestinationInstance.SetUsageType(_oledbDestination.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

                }
            }
          
            foreach (IDTSInputColumn90 col in _oledbDestination.InputCollection[0].InputColumnCollection)
            {
                    IDTSExternalMetadataColumn90 exCol = _oledbDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
                    _oledbDestinationInstance.MapInputColumn(_oledbDestination.InputCollection[0].ID, col.ID, exCol.ID);

            }
            #endregion

            #region Step 7.Create OleDB Command
            IDTSComponentMetaData90 _oledbCommand = null;
            CManagedComponentWrapper _oledbCommandInstance = AddOleDbCommandCompponent(package, dataFlow, "OleDB Command", "LocalHost.Question", ref _oledbCommand);
          
            IDTSPath90 path2 = dataFlow.PathCollection.New();
            path2.AttachPathAndPropagateNotifications(_lookup.OutputCollection[0], _oledbCommand.InputCollection[0]);
            path2.Name = "path2";

            IDTSVirtualInput90 vInput2 = _oledbCommand.InputCollection[0].GetVirtualInput();
            foreach (IDTSVirtualInputColumn90 vColumn in vInput2.VirtualInputColumnCollection)
            {
                _oledbCommandInstance.SetUsageType(_oledbCommand.InputCollection[0].ID, vInput2, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }

            IDTSExternalMetadataColumn90[] exCols = new IDTSExternalMetadataColumn90[_oledbCommand.InputCollection[0].InputColumnCollection.Count];
            for(int i=0;i<_oledbCommand.InputCollection[0].InputColumnCollection.Count;i++)
            {
                exCols[i] = _oledbCommand.InputCollection[0].ExternalMetadataColumnCollection[i];
            }
            //映射最后一个和第一个
            _oledbCommandInstance.MapInputColumn(_oledbCommand.InputCollection[0].ID, _oledbCommand.InputCollection[0].InputColumnCollection[0].ID, exCols[exCols.Length - 1].ID);
            //其他的顺序映射
            for (int i = 1; i < _oledbCommand.InputCollection[0].InputColumnCollection.Count; i++)
            {
                _oledbCommandInstance.MapInputColumn(_oledbCommand.InputCollection[0].ID, _oledbCommand.InputCollection[0].InputColumnCollection[i].ID, exCols[i - 1].ID);
            }
            #endregion


            DTS.Application application = new Microsoft.SqlServer.Dts.Runtime.Application();
            application.SaveToXml("Question1.dtsx", package, null);
        }
        /// <summary>
        /// Create Package
        /// </summary>
        /// <param name="packageName">Package Name</param>
        /// <param name="description">Description</param>
        /// <returns></returns>
        public DTS.Package CreatePackage(string packageName, string description)
        {
            DTS.Package p = new Microsoft.SqlServer.Dts.Runtime.Package();
            p.PackageType = DTS.DTSPackageType.DTSDesigner90;
            p.Name = packageName;
            p.Description = description;
            p.CreatorComputerName = System.Environment.MachineName;
            p.CreatorName = System.Environment.UserName;

            return p;
        }
        /// <summary>
        /// Create Connection
        /// </summary>
        /// <param name="package">Package</param>
        /// <param name="databaseName">Database Name</param>
        /// <param name="connectionName">Connection Name</param>
        public void AddConnection(DTS.Package package, string databaseName, string connectionName)
        {
            DTS.ConnectionManager mConnection = package.Connections.Add("OLEDB");
            mConnection.Name = connectionName;
            mConnection.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Data Source=(local);Auto Translate=False;Initial Catalog=" + databaseName + ";";
        }
        /// <summary>
        /// Create ExecuteSql Task
        /// </summary>
        /// <param name="package">Package</param>
        /// <param name="name">Task Name</param>
        /// <param name="connectionName">Connection Name</param>
        public void AddExecuteFlow(DTS.Package package, string name,string connectionName,string sqlCommand,ref DTS.TaskHost executeSqlTask)
        {
            executeSqlTask = package.Executables.Add("STOCK:SQLTask") as DTS.TaskHost;
            executeSqlTask.Name = name;
            executeSqlTask.Properties["Connection"].SetValue(executeSqlTask, connectionName);
            executeSqlTask.Properties["SqlStatementSourceType"].SetValue(executeSqlTask, SqlStatementSourceType.DirectInput);
            executeSqlTask.Properties["SqlStatementSource"].SetValue(executeSqlTask, sqlCommand);
        }
        /// <summary>
        /// Create DataFlow
        /// </summary>
        ///<param name="name"></param>
        ///<param name="package"></param>
        ///<param name="th"></param>
        public MainPipe AddDataFlow(DTS.Package package, string name,ref DTS.TaskHost dataflowTask)
        {
            MainPipe dataFlow;
            dataflowTask = package.Executables.Add("DTS.Pipeline") as DTS.TaskHost;
            dataflowTask.Name = name;
            dataFlow = dataflowTask.InnerObject as MainPipe;
            return dataFlow;
        }
        /// <summary>
        /// Create OLEDB Source
        /// </summary>
        /// <param name="connectionName">Connection Name</param>
        /// <param name="sql">Sql Command</param>
        public IDTSComponentMetaData90 AddOleDbSource(DTS.Package package, MainPipe dataFlow, string name, string connectionName, string tableName)
        {
            IDTSComponentMetaData90 oledbSource = dataFlow.ComponentMetaDataCollection.New();
            oledbSource.ComponentClassID = "DTSAdapter.OleDbSource.1";

            CManagedComponentWrapper oledbSourceInstance = oledbSource.Instantiate();
            oledbSourceInstance.ProvideComponentProperties();

            oledbSource.Name = name;
            oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DTS.DtsConvert.ToConnectionManager90(package.Connections[connectionName]);
            oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connectionName].ID;

            oledbSourceInstance.SetComponentProperty("OpenRowset","[dbo].[" + tableName + "]");
            oledbSourceInstance.SetComponentProperty("AccessMode",0);
            oledbSourceInstance.AcquireConnections(null);
            oledbSourceInstance.ReinitializeMetaData();
            oledbSourceInstance.ReleaseConnections();

            return oledbSource;

        }
        /// <summary>
        /// create Look Up Component
        /// </summary>
        /// <param name="package"></param>
        /// <param name="name"></param>
        /// <param name="dataflowTask"></param>
        public CManagedComponentWrapper AddLookupComponent(DTS.Package package, MainPipe dataFlow, string name, string connectionName, ref IDTSComponentMetaData90 lookup)
        {
            lookup = dataFlow.ComponentMetaDataCollection.New();
            lookup.ComponentClassID = "DTSTransform.LookUp.1";
            CManagedComponentWrapper lookupInstance = lookup.Instantiate();
            lookupInstance.ProvideComponentProperties();
            lookup.Name = "Look Up";

            lookup.RuntimeConnectionCollection[0].ConnectionManager = DTS.DtsConvert.ToConnectionManager90(package.Connections[connectionName]);
            lookup.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connectionName].ID;

            lookupInstance.SetComponentProperty("SqlCommand", "select * from [dbo].[Table_2]");

            lookupInstance.AcquireConnections(null);
            lookupInstance.ReinitializeMetaData();
            lookupInstance.ReleaseConnections();

            return lookupInstance;
        }
        /// <summary>
        /// Create OleDb Destination
        /// </summary>
        /// <param name="dataFlow"></param>
        /// <param name="name"></param>
        public CManagedComponentWrapper AddOleDbDestination(DTS.Package package, MainPipe dataFlow, string name,string connectionName,ref IDTSComponentMetaData90 OledbDestination)
        {
            OledbDestination = dataFlow.ComponentMetaDataCollection.New();
            OledbDestination.ComponentClassID = "DTSAdapter.OleDbDestination.1";
            CManagedComponentWrapper OledbDestinationInstance = OledbDestination.Instantiate();
            OledbDestinationInstance.ProvideComponentProperties();

            OledbDestination.RuntimeConnectionCollection[0].ConnectionManager = DTS.DtsConvert.ToConnectionManager90(package.Connections[connectionName]);
            OledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connectionName].ID;

           
            OledbDestinationInstance.SetComponentProperty("OpenRowset","[dbo].[Table_2]");
            OledbDestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS");
            OledbDestinationInstance.SetComponentProperty("AccessMode",3);

            OledbDestinationInstance.AcquireConnections(null);
            OledbDestinationInstance.ReinitializeMetaData();
            OledbDestinationInstance.ReleaseConnections();

            return OledbDestinationInstance;
        }
        /// <summary>
        /// Create OleDb Command
        /// </summary>
        /// <param name="dataFlow"></param>
        /// <param name="name"></param>
        public CManagedComponentWrapper AddOleDbCommandCompponent(DTS.Package package, MainPipe dataFlow, string name, string connectionName, ref IDTSComponentMetaData90 olddbCommand)
        {
            olddbCommand = dataFlow.ComponentMetaDataCollection.New();
            olddbCommand.ComponentClassID = "{C60ACAD1-9BE8-46B3-87DA-70E59EADEA46}";
            CManagedComponentWrapper oledbCommandInstance = olddbCommand.Instantiate();
            oledbCommandInstance.ProvideComponentProperties();


            olddbCommand.RuntimeConnectionCollection[0].ConnectionManager = DTS.DtsConvert.ToConnectionManager90(package.Connections[connectionName]);
            olddbCommand.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connectionName].ID;

            oledbCommandInstance.SetComponentProperty("SqlCommand", "Update Table_2 set Name = ?,Psd = ? where Id = ?");

            oledbCommandInstance.AcquireConnections(null);
            oledbCommandInstance.ReinitializeMetaData();
            oledbCommandInstance.ReleaseConnections();

            return oledbCommandInstance;
        }
        #endregion


        #region 创建导事实数据的包
        /// <summary>
        /// 创建导数据包
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreateLoadData_Click(object sender, EventArgs e)
        {
            #region 创建包
            DTS.Package package = CreatePackage("LoadData", "LoadData");

            package.Variables.Add("BeginTime",false,"User",Convert.ToDateTime("2000-01-01"));
            package.Variables.Add("EndTime",false,"User",Convert.ToDateTime("2000-01-01"));
            package.Variables.Add("Excursion",false,"User",Convert.ToInt32(0));

            #endregion

            #region 创建连接
            AddConnection(package, "DatabaseName", "DatabaseName");
            AddConnection(package, "DatabaseName", "DatabaseName");
            #endregion

            #region 创建ForLoop

            DTS.ForLoop  forLoop = (DTS.ForLoop)package.Executables.Add("STOCK:ForLoop");


            forLoop.InitExpression = "@BeginTime";
            forLoop.EvalExpression = "@BeginTime < @EndTime";
            forLoop.AssignExpression = "DATEADD("+'"'+"dd"+'"'+",@Excursion,@BeginTime)";

            forLoop.Name = "For Loop Container";
          

            #endregion

            #region 在ForLoop中创建DataFolw

            DTS.TaskHost dataflow = forLoop.Executables.Add("DTS.Pipeline") as DTS.TaskHost;
 
            #endregion

            #region 保存包
            DTS.Application application = new Microsoft.SqlServer.Dts.Runtime.Application();
            application.SaveToXml("LoadData.dtsx", package, null);
            #endregion

        }
        #endregion
    }
}

      有什么问题,可以发邮件和我交流!

     dcstudio@126.com

dcstudio
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值