楼上的，可以打开，我把那段拷过来给你吧。During last few months I was busy doing different (mostly boring) projects and in one of them some DTS stuff was involved. After searching internet for examples I found few on Microsoft抯 How To and two examples on the rest of the web (one was in Italian). That is main motivation to write this article. To immediately make it clear, I didn抰 find solution for CustomTask problem and I don抰 have desire to do it, in this article I will concentrate on enumerating, executing and changing properties or global variables of DTS package. Since I抦 using desktop engine I will also skip repository packages. For the start we will enumerate packages which are stored on (local) or (local)\NetSDK server as local packages. Key for handling DTS in .NET is adding reference to 揗icrosoft DTSPackage Object Library? If you don抰 know how to do it, it抯 nicely described in DotNETCookBook.pdf which is available for download on http://SQLDev.Net/DTS/ DotNETCookBook.htm. It also explains how to sign assembly and register it with GAC which is a question for new MCSD exams. For enumerating local packages on SQL server we will use the following code: static void Main(string[] args) { DTS.Application App=new DTS.Application(); DTS.PackageSQLServer package= App.GetPackageSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default); DTS.PackageInfos Infos; Infos = package.EnumPackageInfos("", false, ""); foreach(DTS.PackageInfo Info in Infos) { Console.WriteLine("Name : {0}\tVersionID : {1}",Info.Name,Info.VersionID); } Console.Read(); } First interesting thing here is DTS.Application which is actually 損ublic interface Application?according to Object Browser. So we are creating our instance of that interface and using it to create our package object (PackageSQLServer) which is again interface. Further we are getting our infos and retrieving two out of 11 possible properties. Certainly I was quite surprised that we can create instance of interface simply by calling constructor of that interface. If we open EXE with ILDASM we will find:newobj instance void [Interop.DTS]DTS.ApplicationClass::.ctor() So it is really a constructor. Of course next in queue to be examined is Interop.DTS.dll (RCW created by VS.NET)
.class interface public abstract auto ansi import Application implements DTS._Application { .custom instance void [mscorlib]System.Runtime.InteropServices.GuidAttribute::.ctor(string) = ( 01 00 24 31 30 30 33 30 30 30 31 2D 45 42 31 43 // ..$10030001-EB1C 2D 31 31 43 46 2D 41 45 36 45 2D 30 30 41 41 30 // -11CF-AE6E-00AA0 30 34 41 33 34 44 35 00 00 ) // 04A34D5.. .custom instance void [mscorlib]System.Runtime.InteropServices.CoClassAttribute::.ctor(class [mscorlib]System.Type) = ( 01 00 14 44 54 53 2E 41 70 70 6C 69 63 61 74 69 // ...DTS.Applicati 6F 6E 43 6C 61 73 73 00 00 ) // onClass.. } // end of class Application So we must be careful with Object Browser or IntelliSense when we are using unmanaged libraries in future. Procedure to enumerate what is in structured storage is almost identical :static void Main(string[] args) { DTS.Package2Class package=new DTS.Package2Class(); DTS.SavedPackageInfos Infos = package.GetSavedPackageInfos("C:\\Pubs2Pubs2Package.dts"); foreach(DTS.SavedPackageInfo Info in Infos) { Console.WriteLine("Name : {0}\tVersionID : {1}", Info.PackageName,Info.VersionID); } package.UnInitialize(); Console.Read(); } Number of properties is somewhat smaller for info but treatment is about the same. Package2Class is object which we are going to use for execution or manipulation of properties.static void Main(string[] args) { DTS.Package2Class package=new DTS.Package2Class(); try { Object MIA=System.Reflection.Missing.Value; package.LoadFromSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "{F580DFC4-1F6A-4D70-B6A1-1C200B0D7890}", "CustomTaskPKG",ref MIA); Console.WriteLine(package.Description); package.Description="My description"; Console.WriteLine(package.Description); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { package.UnInitialize(); package=null; } Console.Read(); } If you want the freshest version of the package than just omit VersionID. To save new description use :package.SaveToSQLServer("(local)\\NetSDK", "sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "","",ref MIA,false); For global variables procedure is again very similar.static void Main(string[] args) { DTS.Package2Class package = new DTS.Package2Class(); try { Object MIA=System.Reflection.Missing.Value; package.LoadFromStorageFile("C:\\Pubs2Pubs2Package.dts","","","", "Pubs2Pubs2Package",ref MIA); package.GlobalVariables.AddGlobalVariable("MyVariable","MyValue"); foreach(DTS.GlobalVariable GVar in package.GlobalVariables) Console.WriteLine("Name : {0}\tValue : {1}",GVar.Name,GVar.Value); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { package.UnInitialize(); } Console.Read(); }
To save changes to file use :package.SaveToStorageFile("C:\\Pubs2Pubs2Package.dts","","",ref MIA,false); Finally if you want to change value of some variable use :package.GlobalVariables.Item("MyVariable").let_Value("Hello from C#"); To execute package use :package.Execute(); As you noticed so far the number of lines of code to achieve any of these operations is extremely small. This covers some simple manipulations of existing DTS package(s). Thanks to reach System.Collections namespace it抯 easy to manipulate DTS packages and their belonging properties and variables and create associations between packages, servers, CSV files and so on. As a matter of fact I have an application written in VB.NET ( as well as one in C#) which does that, so if anybody is interested in buying it my e-mail is at the bottom of the page.
{
DTS.Application App=new DTS.Application();
DTS.PackageSQLServer package= App.GetPackageSQLServer("(local)\\NetSDK", "sa", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default);
DTS.PackageInfos Infos;
Infos = package.EnumPackageInfos("", false, "");
foreach(DTS.PackageInfo Info in Infos)
{
Console.WriteLine("Name : {0}\tVersionID : {1}",Info.Name,Info.VersionID);
}
Console.Read();
}
First interesting thing here is DTS.Application which is actually 損ublic interface Application?according to Object Browser. So we are creating our instance of that interface and using it to create our package object (PackageSQLServer) which is again interface. Further we are getting our infos and retrieving two out of 11 possible properties. Certainly I was quite surprised that we can create instance of interface simply by calling constructor of that interface. If we open EXE with ILDASM we will find:newobj instance void [Interop.DTS]DTS.ApplicationClass::.ctor()
So it is really a constructor. Of course next in queue to be examined is Interop.DTS.dll (RCW created by VS.NET)
.class interface public abstract auto ansi import Application
implements DTS._Application
{
.custom instance void [mscorlib]System.Runtime.InteropServices.GuidAttribute::.ctor(string) =
( 01 00 24 31 30 30 33 30 30 30 31 2D 45 42 31 43 // ..$10030001-EB1C
2D 31 31 43 46 2D 41 45 36 45 2D 30 30 41 41 30 // -11CF-AE6E-00AA0
30 34 41 33 34 44 35 00 00 ) // 04A34D5..
.custom instance void [mscorlib]System.Runtime.InteropServices.CoClassAttribute::.ctor(class
[mscorlib]System.Type) =
( 01 00 14 44 54 53 2E 41 70 70 6C 69 63 61 74 69 // ...DTS.Applicati
6F 6E 43 6C 61 73 73 00 00 ) // onClass..
} // end of class Application So we must be careful with Object Browser or IntelliSense when we are using unmanaged libraries in future. Procedure to enumerate what is in structured storage is almost identical :static void Main(string[] args)
{
DTS.Package2Class package=new DTS.Package2Class();
DTS.SavedPackageInfos Infos =
package.GetSavedPackageInfos("C:\\Pubs2Pubs2Package.dts");
foreach(DTS.SavedPackageInfo Info in Infos)
{
Console.WriteLine("Name : {0}\tVersionID : {1}",
Info.PackageName,Info.VersionID);
}
package.UnInitialize();
Console.Read();
}
Number of properties is somewhat smaller for info but treatment is about the same. Package2Class is object which we are going to use for execution or manipulation of properties.static void Main(string[] args)
{
DTS.Package2Class package=new DTS.Package2Class();
try
{
Object MIA=System.Reflection.Missing.Value;
package.LoadFromSQLServer("(local)\\NetSDK",
"sa", "", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "",
"{F580DFC4-1F6A-4D70-B6A1-1C200B0D7890}", "CustomTaskPKG",ref MIA);
Console.WriteLine(package.Description);
package.Description="My description";
Console.WriteLine(package.Description);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
package.UnInitialize();
package=null;
}
Console.Read();
}
If you want the freshest version of the package than just omit VersionID. To save new description use :package.SaveToSQLServer("(local)\\NetSDK", "sa", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "","",ref MIA,false);
For global variables procedure is again very similar.static void Main(string[] args)
{
DTS.Package2Class package = new DTS.Package2Class();
try
{
Object MIA=System.Reflection.Missing.Value;
package.LoadFromStorageFile("C:\\Pubs2Pubs2Package.dts","","","",
"Pubs2Pubs2Package",ref MIA);
package.GlobalVariables.AddGlobalVariable("MyVariable","MyValue");
foreach(DTS.GlobalVariable GVar in package.GlobalVariables)
Console.WriteLine("Name : {0}\tValue : {1}",GVar.Name,GVar.Value);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
package.UnInitialize();
}
Console.Read();
}
To save changes to file use :package.SaveToStorageFile("C:\\Pubs2Pubs2Package.dts","","",ref MIA,false);
Finally if you want to change value of some variable use :package.GlobalVariables.Item("MyVariable").let_Value("Hello from C#");
To execute package use :package.Execute();
As you noticed so far the number of lines of code to achieve any of these operations is extremely small. This covers some simple manipulations of existing DTS package(s). Thanks to reach System.Collections namespace it抯 easy to manipulate DTS packages and their belonging properties and variables and create associations between packages, servers, CSV files and so on. As a matter of fact I have an application written in VB.NET ( as well as one in C#) which does that, so if anybody is interested in buying it my e-mail is at the bottom of the page.