1.目标:
创建父包
创建子包
保存包
2.目标具体实现技术分解:
程序集引用
创建包
创建包的变量
创建包任务
创建包连接器
设置属性由表达式生成
设置变量由父包传递(设置包配置)
3.目标实现类关系图与详细信息
4.目标实现代码
5.目标实现测试代码
i. 目标:
1. 创建一个父包:包含一个执行包任务;两个变量,子包路径变量和提供给子包OLEDB连接器的连接字符串的变量;一个文件类型连接管理器,连接器的连接字符串由变量赋值。
2. 创建一个子包:包含一个执行SQL任务;一个变最,OLEDB连接器连接字符串变量,该变量需要配置成由父包传递;
一个OLEDB类型连接管理器,连接器的连接字符串由变最提供。
3. 根据路径保存包;
ii. 目标具体实现技术分解:
1) 程序集引用
2) 创建包
3) 创建包的变量
4) 创建包任务
5) 创建包连接器
6) 设置属性由表达式生成
7) 设置变量由父包传递(设置包配置)
1) 程序集引用
安装MS SQL 后,SSIS相关程序集基本可以从以下目录得到
"Program Files"Microsoft SQL Server"90"DTS 相关子目录
"Program Files"Microsoft SQL Server"90"SDK"Assemblies 根目录
但也有特殊情况,包含Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask的Microsoft.SqlServer.ExecPackageTaskWrap程序集并不能在"Program Files"Microsoft SQL Server"90"DTS"Task 目录下找到,因为这个程序集入在全集缓存中,引用时,在引用文件路径中输入
%windir%"assembly"GAC_MSIL"Microsoft.SqlServer.ExecPackageTaskWrap"9.0.242.0__89845dcd8080cc91"Microsoft.SqlServer.ExecPackageTaskWrap.dll,按确定即可引用
2) 创建包
Package 包对象可以直接实例化
示例:
Package package = new Package()
设置包的名称
package.Name = "Parent";
3) 创建包的变量
创建变量需要调用 Package.Variables.Add 方法返回一个变量实例,再进行一些变量属性的设置。
示例:
Variable varConStr = package.Variables.Add("ConStr", false, "Parent", conString);
设置变量的命名空间
varConStr.Namespace = "User";
4) 创建包任务
创建包任务需要调用Package.Executables.Add 返回一个Executable 实例,第二步把Executable转化成TaskHost实例,第三步从TaskHost.InnerObject属性转化为具体的包任务实例
Package.Executables.Add,可以对过使用包任务的通用名称返回Executable实例,也可以通过包的长名称返回Executable实例。
示例:
Executable package= package.Executables.Add("STOCK:ExecutePackageTask");
Executable exec2 = package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask.ExecutePackageTask, Microsoft.SqlServer.ExecutePackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
以下列表就是通用包任务的通用名称:
l ActiveXScriptTask
l BulkInsertTask
l ExecuteProcessTask
l ExecutePackageTask
l Exec80PackageTask
l FileSystemTask
l FTPTask
l MSMQTask
l PipelineTask
l ScriptTask
l SendMailTask
l SQLTask
l TransferStoredProceduresTask
l TransferLoginsTask
l TransferErrorMessagesTask
l TransferJobsTask
l TransferObjectsTask
l TransferDatabaseTask
l WebServiceTask
l WmiDataReaderTask
l WmiEventWatcherTask
l XMLTask
示例:
Executable exc = package.Executables.Add("STOCK:ExecutePackageTask");
TaskHost hoskHost = exc as TaskHost
ExecutePackageTask packageTask = hoskHost.InnerObject as ExecutePackageTask;
5) 创建包连接器
创建包连接器需要调用Package.Connections.Add方法反回连接器实例
下面列常用连接器名称
"OLEDB"
"ODBC"
"ADO"
"ADO.NET:SQL"
"ADO.NET:ODBC"
"FLATFILE"
"FILE"
"MULTIFLATFILE"
"MULTIFILE"
"SQLMOBILE"
"MSOLAP90"
"FTP"
"HTTP"
"MSMQ"
"SMTP"
"WMI"
示例:
ConnectionManager conn = package.Connections.Add("File");
conn.Name = "Children";
6) 设置属性由表达式生成
设置属性由表达式生成只需要调用SetExpression方法,表达式中访问变量时使用的字符串格式@[变量的唯一标识],变量的唯一标识符可以从变量的Variable.QualifiedName属性中获取。
示例:
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));
7) 设置变量由父包传递(设置包配置)
启用名包配置可以通过Package.EnableConfigurations 属性来设置;创建包配置实例可以调用Package.Configurations.Add方法反回配置实例,设置包配置为父包变量配置需要调用Configuration.ConfigurationTyp;配置目标变量为子包变量,通过Configuration .PackagePath 配置变量的包路径可以通过Variable.GetPackagePath方法获取;配置相应的父包变量通过设置Configuration. ConfigurationString属性的值为父包相应变量的名称。
示例:
package.EnableConfigurations = true;
string varPackagePath = variable.GetPackagePath();
Configuration conf = package.Configurations.Add();
conf.ConfigurationType = conf.ConfigurationType = DTSConfigurationType.ParentVariable;
conf.PackagePath = varPackagePath
conf.ConfigurationString = "ConStr";
iii. 目标实现类关系图与详细信息
iv. 目标实现代码
Code
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();
}
}
}
结束!