写一个在程序中创建dts包，实现数据用dts导入到sql的类
//备注，从getItemToItem()取的字段对应，getSourceTableName()取得表关系对应。
using System;
using System.Data;
using System.Data.SqlClient;
namespace Import
{
/// <summary>
/// 导入学生中考成绩DTS包
/// </summary>
public class ImportStuGrade
{
//DTS包对象
public DTS.Package2Class goPackage = new DTS.Package2Class();
//要导入的文件路径
private string strFilePath;
//机构ID
private string strOrgID;
//SQL服务器地址
private string strSQLPath;
//SQL登录用户名
private string strSQLUserID;
//SQL登录密码
private string strSQLPassword;
//SQL数据库名
private string strDataBaseName;
//表对应关系ID
private string strRelationID;
//要导入的表名
private string strSourceTableName;
/// <summary>
/// 要导入的文件路径
/// </summary>
public string FilePath
{
get
{
return strFilePath;
}
set
{
strFilePath = value;
}
}
/// <summary>
/// //机构ID
/// </summary>
public string OrgID
{
get
{
return strOrgID;
}
set
{
strOrgID = value;
}
}
/// <summary>
/// SQL服务器地址
/// </summary>
public string SQLPath
{
get
{
return strSQLPath;
}
set
{
strSQLPath = value;
}
}
/// <summary>
/// SQL登录用户名
/// </summary>
public string SQLUserID
{
get
{
return strSQLUserID;
}
set
{
strSQLUserID = value;
}
}
/// <summary>
/// SQL登录密码
/// </summary>
public string SQLPassword
{
get
{
return strSQLPassword;
}
set
{
strSQLPassword = value;
}
}
/// <summary>
/// SQL数据库名
/// </summary>
public string DataBaseName
{
get
{
return strDataBaseName;
}
set
{
strDataBaseName = value;
}
}
/// <summary>
/// 表对应关系ID
/// </summary>
public string RelationID
{
get
{
return strRelationID;
}
set
{
strRelationID = value;
}
}
/// <summary>
/// 要导入的源表名
/// </summary>
public string SourceTableName
{
get
{
return strSourceTableName;
}
set
{
strSourceTableName = value;
}
}
public ImportStuGrade()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 导入学生中考信息
/// </summary>
public void ImportStuGradeToDB()
{
try
{
//设置包属性
goPackage.Name = "ImportStuGradeToDB";
goPackage.Description = "导入中考成绩信息DTS包";
goPackage.WriteCompletionStatusToNTEventLog = false;
goPackage.FailOnError = true;
goPackage.PackagePriorityClass = (DTS.DTSPackagePriorityClass)2;
goPackage.MaxConcurrentSteps = 4;
goPackage.LineageOptions = 0;
goPackage.UseTransaction = true;
goPackage.TransactionIsolationLevel = (DTS.DTSIsolationLevel)4096;
goPackage.AutoCommitTransaction = true;
goPackage.RepositoryMetadataOptions = 0;
goPackage.UseOLEDBServiceComponents = true;
goPackage.LogToSQLServer = false;
goPackage.LogServerFlags = 0;
goPackage.FailPackageOnLogFailure = false;
goPackage.ExplicitGlobalVariables = false;
goPackage.PackageType = 0;
//创建连接用户数据文件的连接
DTS.Connection2 oConnection;
//截取文件名后缀名
string strFileFix;
strFileFix = this.strFilePath.Substring(this.strFilePath.Length-3);
strFileFix = strFileFix.ToUpper();
oConnection = null;
//根据文件后缀名判断文件类型,并根据文件类型来定义连接的类型
if (strFileFix == "MDB")
{
oConnection = (DTS.Connection2)goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.ConnectionProperties.Item("Mode").Value = 1;
}
else if ( strFileFix == "XLS" )
{
oConnection = (DTS.Connection2)goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";
}
else if ( strFileFix == "MDC" )
{
oConnection = (DTS.Connection2)goPackage.Connections.New("vfpoledb.1");
}
//设置连接属性
oConnection.ConnectionProperties.Item("Data Source").Value = this.strFilePath;
oConnection.Name = "连接1";
oConnection.ID = 1;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = this.strFilePath;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
goPackage.Connections.Add((DTS.Connection)oConnection);
oConnection = null;
//创建连接SQL数据库的连接
oConnection = (DTS.Connection2)goPackage.Connections.New("SQLOLEDB");
//设置连接属性
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI";
oConnection.ConnectionProperties.Item("Persist Security Info").Value = true;
oConnection.ConnectionProperties.Item("Initial Catalog").Value = this.strDataBaseName;
oConnection.ConnectionProperties.Item("Data Source").Value = this.strSQLPath;
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS 导入/导出向导";
oConnection.Name = "连接2";
oConnection.ID = 2;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = this.strSQLPath;
oConnection.UserID = this.strSQLUserID;
oConnection.Password = this.strSQLPassword;
oConnection.ConnectionTimeout = 60;
oConnection.Catalog = this.strDataBaseName;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
goPackage.Connections.Add((DTS.Connection)oConnection);
oConnection = null;
//定义步骤对象
DTS.Step2 oStep;
oStep = (DTS.Step2)goPackage.Steps.New();
//设置步骤属性
oStep.Name = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 步骤";
oStep.Description = "Copy Data from student to [" + this.strDataBaseName + "].[dbo].[StuGrade] 步骤";
oStep.ExecutionStatus = (DTS.DTSStepExecStatus)1;
oStep.TaskName = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority =(DTS.DTSStepRelativePriority)3;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
goPackage.Steps.Add(oStep);
oStep = null;
Task_Sub1(goPackage);
//保存包
// object objStg;
// objStg = "dfd";
// goPackage.SaveToSQLServer("192.168.0.7", "sa", "",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",ref objStg,true);
//执行包
goPackage.Execute();
}
catch(Exception ex)
{
throw ex;
}
finally
{
//在内存中卸载DTS包
goPackage.UnInitialize();
goPackage = null;
}
}
/// <summary>
/// 具体定义DTS包中的任务
/// </summary>
/// <param name="goPackage">包对象</param>
private void Task_Sub1(DTS.Package2Class goPackage)
{
//任务对象
DTS.Task oTask;
DTS.DataPumpTask2 oCustomTask1;
oTask = goPackage.Tasks.New("DTSDataPumpTask");
oCustomTask1 = (DTS.DataPumpTask2)oTask.CustomTask;
//任务的名称
oCustomTask1.Name = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
//任务的描述
oCustomTask1.Description = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
//任务用来连接源数据文件的连接ID
oCustomTask1.SourceConnectionID = 1;
//设置查询源数据文件表中的数据的SQL语句
oCustomTask1.SourceSQLStatement = "select ";
DataTable dtabItemToItem;
dtabItemToItem = getItemToItem();
for ( int intLoop = 0 ; intLoop < dtabItemToItem.Rows.Count ; intLoop++ )
{
oCustomTask1.SourceSQLStatement += "`" + dtabItemToItem.Rows[intLoop]["SourceItem"] + "`,";
}
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement.Substring(0,oCustomTask1.SourceSQLStatement.Length-1);
if ( this.strSourceTableName =="" || this.strSourceTableName == null )
{
this.strSourceTableName = this.getSourceTableName();
}
oCustomTask1.SourceSQLStatement += " from `" + this.strSourceTableName + "`";
//任务用来连接目标数据的连接ID
oCustomTask1.DestinationConnectionID = 2;
//任务用到的目标数据库名称
oCustomTask1.DestinationObjectName = "[" + this.strDataBaseName + "].[dbo].[StuGrade]";
oCustomTask1.ProgressRowCount = 1000;
oCustomTask1.MaximumErrorCount = 0;
oCustomTask1.FetchBufferSize = 1;
oCustomTask1.UseFastLoad = true;
oCustomTask1.InsertCommitSize = 0;
oCustomTask1.ExceptionFileColumnDelimiter = "|";
oCustomTask1.ExceptionFileRowDelimiter = @"\n";
oCustomTask1.AllowIdentityInserts = false;
oCustomTask1.FirstRow = 0;
oCustomTask1.LastRow = 0;
oCustomTask1.FastLoadOptions = (DTS.DTSFastLoadOptions)2;
oCustomTask1.ExceptionFileOptions = (DTS.DTSExceptionFileOptions)1;
oCustomTask1.DataPumpOptions = 0;
oCustomTask1_Trans_Sub1(oCustomTask1);
goPackage.Tasks.Add(oTask);
oCustomTask1 = null;
oTask = null;
}
/// <summary>
/// 具体的任务中的对应关系设置
/// </summary>
/// <param name="oCustomTask1">任务对象</param>
private void oCustomTask1_Trans_Sub1(DTS.DataPumpTask2 oCustomTask1)
{
DTS.Transformation2 oTransformation ;
DTS.Properties oTransProps;
//定义一个列对象
DTS.Column oColumn;
oTransformation = (DTS.Transformation2)oCustomTask1.Transformations.New("DTS.DataPumpTransformScript");
oTransformation.Name = "AxScriptXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = 0;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.TransformPhases = 4;
#region 定义目标表中的列，目标表的列必须定义，源表列可不定义
oColumn = oTransformation.DestinationColumns.New("StuTestID", 1);
oColumn.Name = "StuTestID";
oColumn.Ordinal = 1;
oColumn.Flags = 104;
oColumn.Size = 16;
oColumn.DataType = 130;
oColumn.Precision = 0;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Chinese", 2);
oColumn.Name = "Chinese";
oColumn.Ordinal = 2;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Maths", 3);
oColumn.Name = "Maths";
oColumn.Ordinal = 3;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("English", 4);
oColumn.Name = "English";
oColumn.Ordinal = 4;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Politics", 5);
oColumn.Name = "Politics";
oColumn.Ordinal = 5;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Physics", 6);
oColumn.Name = "Physics";
oColumn.Ordinal = 6;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Chemistry", 7);
oColumn.Name = "Chemistry";
oColumn.Ordinal = 7;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Biology", 8);
oColumn.Name = "Biology";
oColumn.Ordinal = 8;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("History", 9);
oColumn.Name = "History";
oColumn.Ordinal = 9;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Geography", 10);
oColumn.Name = "Geography";
oColumn.Ordinal = 10;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Integration1", 11);
oColumn.Name = "Integration1";
oColumn.Ordinal = 11;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Integration2", 12);
oColumn.Name = "Integration2";
oColumn.Ordinal = 12;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("ShtickSubject1", 13);
oColumn.Name = "ShtickSubject1";
oColumn.Ordinal = 13;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("ShtickSubject2", 14);
oColumn.Name = "ShtickSubject2";
oColumn.Ordinal = 14;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Standby1", 15);
oColumn.Name = "Standby1";
oColumn.Ordinal = 15;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Standby2", 16);
oColumn.Name = "Standby2";
oColumn.Ordinal = 16;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Standby3", 17);
oColumn.Name = "Standby3";
oColumn.Ordinal = 17;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
oColumn = oTransformation.DestinationColumns.New("Standby4", 18);
oColumn.Name = "Standby4";
oColumn.Ordinal = 18;
oColumn.Flags = 120;
oColumn.Size = 0;
oColumn.DataType = 131;
oColumn.Precision = 18;
oColumn.NumericScale = 0;
oColumn.Nullable = true;
oTransformation.DestinationColumns.Add(oColumn);
oColumn = null;
#endregion
oTransProps = oTransformation.TransformServerProperties;
#region 用VB脚本设置字段对应关系
oTransProps.Item("Text").Value = "'**********************************************************************" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "' Visual Basic Transformation Script" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "' Copy each source column to the" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "' destination column" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "'************************************************************************" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "Function Main()" + "\n";
DataTable dtabItemToItem;
dtabItemToItem = getItemToItem();
for ( int intLoop = 0 ; intLoop < dtabItemToItem.Rows.Count ; intLoop++ )
{
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + " DTSDestination(\"" + dtabItemToItem.Rows[intLoop]["TargetItem"] + "\") = DTSSource(\"" + dtabItemToItem.Rows[intLoop]["SourceItem"] + "\")" + "\n";
}
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + " Main = DTSTransformStat_OK" + "\n";
oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "End Function";
oTransProps.Item("Language").Value = "VBScript";
oTransProps.Item("FunctionEntry").Value = "Main";
#endregion
oTransProps = null;
oCustomTask1.Transformations.Add(oTransformation);
oTransformation = null;
}
/// <summary>
/// 获得字段对应关系记录
/// </summary>
/// <returns>字段对应关系的DataTabl</returns>
private DataTable getItemToItem()
{
string strSQL;
string strSQLConnectionString = "server=" + this.strSQLPath + ";uid=" + this.strSQLUserID + ";pwd=" + this.strSQLPassword + ";database=" + this.strDataBaseName;
strSQL = "select * from ImportItemToItem where RelationID='" + this.strRelationID + "'";
SqlConnection sqlcon = new SqlConnection(strSQLConnectionString);
SqlDataAdapter sqlda = new SqlDataAdapter(strSQL,sqlcon);
DataSet ds = new DataSet();
sqlda.Fill(ds,"ImportItemToItem");
return ds.Tables["ImportItemToItem"];
}
/// <summary>
/// 获得用户表的表名
/// </summary>
/// <returns>用户表的表名</returns>
private string getSourceTableName()
{
string strSQL;
string strSQLConnectionString = "server=" + this.strSQLPath + ";uid=" + this.strSQLUserID + ";pwd=" + this.strSQLPassword + ";database=" + this.strDataBaseName;
strSQL = "select SourceTable from ImportTableToTable where SrNo='" + this.strRelationID + "'";
SqlConnection sqlcon = new SqlConnection(strSQLConnectionString);
SqlDataAdapter sqlda = new SqlDataAdapter(strSQL,sqlcon);
DataSet ds = new DataSet();
sqlda.Fill(ds,"ImportTableToTable");
return ds.Tables["ImportTableToTable"].Rows[0]["SourceTable"].ToString().Trim();
}
}
}
