C# 后台代码怎么利用ssis导入EXCEL,TXT,XML到数据库

Question

如题，这种导入方法的话应该很快，可是代码不知道怎么写

Answer 1

ssis导入excel用的也是oledb方式,速度应该一样的

Answer 2

这里有个图解的例子,看完了就会了~~
图解SSIS批量导入Excel文件

Answer 3

其实这个我也会的，不过是想在vs2008 后台代码，生成SSIS包后执行SSIS包，达到导入结果，

Answer 4

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;namespace ConSSIS
{
    public class MyPackage
    {
        /**//// <summary>
        /// 构造函数
        /// </summary>
        public MyPackage()
        { }
        /**//// <summary>
        /// 保存包
        /// </summary>
        /// <param name="path">保存包的路径</param>
        /// <param name="package">需要保存的包</param>
        /// <param name="idtsEvent">DTS事件</param>        public static void  SavePackage(string path,Package package,IDTSEvents idtsEvent )
        {            Application app = new Application();
            app.SaveToXml(path,package,idtsEvent);
        }
        /**//// <summary>
        ///创建一个执行一个任务的父包
        /// </summary>
        /// <returns>返回父包</returns>
        public static Package CreateParentPackage()
        {
            string conString = System.Configuration.ConfigurationManager.AppSettings["ConStr"];
            string strChild = System.Configuration.ConfigurationManager.AppSettings["ChildPath"];
            Package package = new Package();
            package.Name = "Parent";            Variable varConStr = package.Variables.Add("ConStr", false, "Parent", conString);
            varConStr.Namespace = "User";
            Variable varChildrenFilePath = package.Variables.Add("childrenFilePath", false, "Parent", strChild);
            varChildrenFilePath.Namespace = "User";            ConnectionManager conn = package.Connections.Add("File");
            conn.Name = "Children";
            conn.SetExpression("ConnectionString", string.Format("@[{0}]", varChildrenFilePath.QualifiedName));            
            Executable exc = package.Executables.Add("STOCK:ExecutePackageTask");            TaskHost hoskHost = exc as TaskHost;
            ExecutePackageTask packageTask = hoskHost.InnerObject as ExecutePackageTask;
            packageTask.Connection = "Children";            return package;
        }
        /**//// <summary>
        /// 创建一个执行一个SQL任务的子包，连接字符由父包传递。
        /// </summary>
        /// <returns>返回子包</returns>
        public static Package CreateChildrenPackage()
        {
            string executeCommand = System.Configuration.ConfigurationManager.AppSettings["ExecuteCommandStr"];
            Package package = new Package();
            package.Name = "Children";            Variable variable = package.Variables.Add("connectString", false, "Children", " ");
            variable.Namespace = "User";            package.EnableConfigurations = true;
            string varPackagePath = variable.GetPackagePath();
            Configuration conf = package.Configurations.Add();
            conf.ConfigurationType = conf.ConfigurationType = DTSConfigurationType.ParentVariable;
            conf.PackagePath = varPackagePath;
            conf.ConfigurationString = "ConStr";
            Executable exc = package.Executables.Add("STOCK:SQLTask");            ConnectionManager conn = package.Connections.Add("OLEDB");
            conn.SetExpression("ConnectionString", string.Format("@[{0}]", variable.QualifiedName));
            conn.Name = "LocalDB";            TaskHost hoskHost = exc as TaskHost;
            ExecuteSQLTask sqlTask = hoskHost.InnerObject as ExecuteSQLTask;
            sqlTask.Connection = "LocalDB";
            sqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
            sqlTask.SqlStatementSource = executeCommand;
            sqlTask.ResultSetType = ResultSetType.ResultSetType_None;
                        return package;        }
       
    }
 
}配置文件:Code
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="ConStr" value="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"/>
        <add key="ChildPath" value="d:\Children.dtsx"/>
        <add key="ExecuteCommandStr" value="select  ' this is a test !' as  test"/>           
       </appSettings>
</configuration>
 v.              目标实现测试代码 Code
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;namespace ConSSIS
{
    class Program
    {
        static void Main(string[] args)
        {
            //包保存路径变量
            string strChildPath = @"d:\Children.dtsx";
            string strParentPath = @"d:\Parent.dtsx";
            //创建子包
            Package pChild = MyPackage.CreateChildrenPackage();
            //创建父包
            Package pParent = MyPackage.CreateParentPackage();            //保存子包
            MyPackage.SavePackage(strChildPath,pChild,null);
            //保存父包
            MyPackage.SavePackage(strParentPath,pParent,null);
            
            //执行父包
            DTSExecResult result = pParent.Execute();            //设置执行包后的信息
            string message = null;
            if(result.Equals(DTSExecResult.Success))
            {
                message = "包执行成功";
            }
            if(result.Equals(DTSExecResult.Failure))
            {
                for (int i = 0; i < pParent.Errors.Count;i++ )
                {
                    message += pParent.Errors[i].Description;
                }
            }
            //输出包执行后信息
            Console.Write(message);
            Console.Read();
           
        }
    }
}

Answer 5

http://topic.csdn.net/u/20090915/10/426bb439-4d6a-4289-9294-c0bdcf1d7ca6.html

Answer 6

一开始我就用BCP，听说SSIS更好，就是想看下有没有更好的方法导入,看了几个外国网，很多都有详细说明

Answer 7

为什么我的找不到ExecutePackageTask的引用呢