Creating DTS in C#

[Michael.Covington]

I am trying to create a DTS package using C#. I have done this in VB6
without any problems. However, when I convert everything over to C# I
get the 'System.Invalid CastException'. I am running windows 2000, SQL
2000 sp3a. I have also followed the instructions in using DTS provided
by "http://sqldev.net/DTS/dotnetcookbook. htm".

The error is occurs at this line:

DTS.DataPumpTas k DT = (DTS.DataPumpTa sk)package.Task s.New
("DTSDataPumpTa sk");

Does anyone know what causes this and is there a fix for it?

Below is a copy of the code.


public void createPackag()
{
DTS.Connection oConnection =
(DTS.Connection )package.Connec tions.New("Micr osoft.Jet.OLEDB .4.0");
oConnection.Nam e ="Connection 1";
oConnection.Dat aSource = "C:\\MySourceDB .MDB";
oConnection.ID = 1;
oConnection.Reu sable = true;
oConnection.Con nectImmediate = false;
oConnection.Con nectionTimeout = 60;
oConnection.Use TrustedConnecti on = false;
oConnection.Use DSL = false;
oConnection = null;
DTS.Connection oConnection2 =
(DTS.Connection )package.Connec tions.New("SQLO LEDB");
oConnection2.Na me = "Connection 2";
oConnection2.ID = 2;
oConnection2.Re usable = true;
oConnection2.Co nnectImmediate= false;
oConnection2.Da taSource= "MyServerNa me";
oConnection2.Us erID = "MyUserID";
oConnection2.Co nnectionTimeout = 60;
oConnection2.Ca talog = "MyDestDB";
oConnection2.Us eTrustedConnect ion = false;
oConnection2.Us eDSL = false;
oConnection2.Pa ssword = "MyPassword ";
oConnection2 = null;
DTS.Step2 oStep = (DTS.Step2)pack age.Steps.New() ;
oStep.Name = "Copying Data from MyTable";
oStep.Descripti on = "Copying Data from MyTable";
oStep.TaskName = "Copying Data from MyTable";
oStep.CommitSuc cess = false;
oStep.RollbackF ailure = false;
oStep.ScriptLan guage = "VBScript";//not sure about this
oStep.AddGlobal Variables = true;
oStep.CloseConn ection = false;
oStep.ExecuteIn MainThread = true;
oStep.IsPackage DSORowset = false;
oStep.JoinTrans actionIfPresent = false;
oStep.DisableSt ep = false;
oStep.FailPacka geOnError = true;
package.Steps.A dd(oStep);
oStep = null;
DTS.Task oTask = (DTS.Task)packa ge.Tasks.New("D TSDataPumpTask" );
oTask.Name = "Copying Data from MyTable";
DTS.CustomTask oCustomTask = oTask.CustomTas k;
oCustomTask.Nam e = "Copying Data from MyTable";
oCustomTask.Des cription = "Copying Data from MyTable to
MyDestDB.MyTabl e";
DTS.DataPumpTas k DT =
(DTS.DataPumpTa sk)package.Task s.New("DTSDataP umpTask");
DT.SourceConnec tionID = 1;
DT.SourceSQLSta tement = "SELECT `TestField` FROM MyTable";
DT.DestinationC onnectionID =2;
DT.DestinationO bjectName = "MyTable";
DT.ProgressRowC ount = 1000;
DT.MaximumError Count = 0;
DT.FetchBufferS ize = 1;
DT.UseFastLoad= true;
DT.InsertCommit Size = 0;
DT.InsertCommit Size = 500000;
DT.ExceptionFil eColumnDelimite r = "|";
DT.ExceptionFil eRowDelimiter = "\r\n";
DT.AllowIdentit yInserts = false;
DT.FirstRow = 0;
DT.LastRow = 0;
DTS.Transformat ion Trans =
(DTS.Transforma tion)package.Ta sks.New("DataPu mpTransformCopy ");
Trans.Name = "DirectCopyXfor m";
Trans.Transform Flags = 63;
Trans.ForceSour ceBlobsBuffered = 0;
Trans.ForceBlob sInMemory = false;
Trans.InMemoryB lobSize = 1048576;
Trans.SourceCol umns.AddColumn( "TestField" ,1);
Trans.Destinati onColumns.AddCo lumn("TestField ",1);
DT.Transformati ons.Add(Trans);
package.Tasks.A dd(oTask);
oCustomTask = null;
oTask = null;

}

[Angel J. Hernández M.]

Hope this helps you...

http://www.15seconds.com/issue/030909.htm
http://www.c-sharpcorner.com/Code/2002/Sept/DTSnCS.asp
http://www.c-sharpcorner.com/Code/20...TSCustTask.asp
Regards,

--
Angel J. Hernández M.
MCP - MCAD - MCSD - MCDBA
http://groups.msn.com/desarrolladoresmiranda
http://www.consein.com

<Mi************ ***@TheNetResul tsGroup.com> escribió en el mensaje
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

I am trying to create a DTS package using C#. I have done this in VB6
without any problems. However, when I convert everything over to C# I
get the 'System.Invalid CastException'. I am running windows 2000, SQL
2000 sp3a. I have also followed the instructions in using DTS provided
by "http://sqldev.net/DTS/dotnetcookbook. htm".

The error is occurs at this line:

DTS.DataPumpTas k DT = (DTS.DataPumpTa sk)package.Task s.New
("DTSDataPumpTa sk");

Does anyone know what causes this and is there a fix for it?

Below is a copy of the code.


public void createPackag()
{
DTS.Connection oConnection =
(DTS.Connection )package.Connec tions.New("Micr osoft.Jet.OLEDB .4.0");
oConnection.Nam e ="Connection 1";
oConnection.Dat aSource = "C:\\MySourceDB .MDB";
oConnection.ID = 1;
oConnection.Reu sable = true;
oConnection.Con nectImmediate = false;
oConnection.Con nectionTimeout = 60;
oConnection.Use TrustedConnecti on = false;
oConnection.Use DSL = false;
oConnection = null;
DTS.Connection oConnection2 =
(DTS.Connection )package.Connec tions.New("SQLO LEDB");
oConnection2.Na me = "Connection 2";
oConnection2.ID = 2;
oConnection2.Re usable = true;
oConnection2.Co nnectImmediate= false;
oConnection2.Da taSource= "MyServerNa me";
oConnection2.Us erID = "MyUserID";
oConnection2.Co nnectionTimeout = 60;
oConnection2.Ca talog = "MyDestDB";
oConnection2.Us eTrustedConnect ion = false;
oConnection2.Us eDSL = false;
oConnection2.Pa ssword = "MyPassword ";
oConnection2 = null;
DTS.Step2 oStep = (DTS.Step2)pack age.Steps.New() ;
oStep.Name = "Copying Data from MyTable";
oStep.Descripti on = "Copying Data from MyTable";
oStep.TaskName = "Copying Data from MyTable";
oStep.CommitSuc cess = false;
oStep.RollbackF ailure = false;
oStep.ScriptLan guage = "VBScript";//not sure about this
oStep.AddGlobal Variables = true;
oStep.CloseConn ection = false;
oStep.ExecuteIn MainThread = true;
oStep.IsPackage DSORowset = false;
oStep.JoinTrans actionIfPresent = false;
oStep.DisableSt ep = false;
oStep.FailPacka geOnError = true;
package.Steps.A dd(oStep);
oStep = null;
DTS.Task oTask = (DTS.Task)packa ge.Tasks.New("D TSDataPumpTask" );
oTask.Name = "Copying Data from MyTable";
DTS.CustomTask oCustomTask = oTask.CustomTas k;
oCustomTask.Nam e = "Copying Data from MyTable";
oCustomTask.Des cription = "Copying Data from MyTable to
MyDestDB.MyTabl e";
DTS.DataPumpTas k DT =
(DTS.DataPumpTa sk)package.Task s.New("DTSDataP umpTask");
DT.SourceConnec tionID = 1;
DT.SourceSQLSta tement = "SELECT `TestField` FROM MyTable";
DT.DestinationC onnectionID =2;
DT.DestinationO bjectName = "MyTable";
DT.ProgressRowC ount = 1000;
DT.MaximumError Count = 0;
DT.FetchBufferS ize = 1;
DT.UseFastLoad= true;
DT.InsertCommit Size = 0;
DT.InsertCommit Size = 500000;
DT.ExceptionFil eColumnDelimite r = "|";
DT.ExceptionFil eRowDelimiter = "\r\n";
DT.AllowIdentit yInserts = false;
DT.FirstRow = 0;
DT.LastRow = 0;
DTS.Transformat ion Trans =
(DTS.Transforma tion)package.Ta sks.New("DataPu mpTransformCopy ");
Trans.Name = "DirectCopyXfor m";
Trans.Transform Flags = 63;
Trans.ForceSour ceBlobsBuffered = 0;
Trans.ForceBlob sInMemory = false;
Trans.InMemoryB lobSize = 1048576;
Trans.SourceCol umns.AddColumn( "TestField" ,1);
Trans.Destinati onColumns.AddCo lumn("TestField ",1);
DT.Transformati ons.Add(Trans);
package.Tasks.A dd(oTask);
oCustomTask = null;
oTask = null;

}

[Michael]

I was able to resolve this issue and thought I would post the solution
so that others may benefit from it. The following example creates,
saves, and executes a DTS package in C#. This example is setup to only
DTS one field from one table but it can easily be modified to do a
complete database.
This particular section of code gave me the most trouble so I thought I
would point it out as well. My original code looked like this and
would cause an invalidcastexce ption.

DTS.Task oTask = (DTS.Task)packa ge.Tasks.New("D TSDataPumpTask" );
DTS.CustomTask oCustomTask = oTask.CustomTas k;

This was how I corrected it.

DTS.DataPumpTas k2 oCustTask;
DTS.Task oTask;
oTask = pkg.Tasks.New(" DTSDataPumpTask ");
oCustTask = (DTS.DataPumpTa sk2)oTask.Custo mTask;

Here is the entire example:
using System;
using System.Drawing;
using System.Collecti ons;
using System.Componen tModel;
using System.Windows. Forms;
using System.Data;
using DTS = Microsoft.SQLSe rver.DTSPkg80;

namespace MyDTSTest
{
/// <summary>
/// Summary description for Form1.
///This is assuming that all steps have been taken in the following
document:
///http://SQLDEV.NET/DTS/DotNetCookBook. htm
///SN.EXE -K c:\DTS.KEY
///tlbimp.exe "C:\program files\microsoft SQL
Sever\80\Tools\ Bin\dtspkg.dll" /out:c:\Microsof t.SQLServer.DTS Pkg80.dll
/Keyfile:c:\DTS. KEY
///gacutil.exe -i C:\Microsoft.SQ LServer.DTSPkg8 0.dll
///These steps are needed for interop with dtspkg.dll
/// </summary>
public class Form1 : System.Windows. Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.Componen tModel.Containe r components = null;
private System.Windows. Forms.Button button1;
public DTS.Package2Cla ss pkg = new DTS.Package2Cla ss();

private void button1_Click(o bject sender, System.EventArg s e)
{
initpackage();
}

public void initpackage()
{
CreateConnectio ns();
CreatePackageSt eps();
DefinTasks(pkg) ;
pkg.Name="MyCSh arpDTSTest";
pkg.Description = "CShart DTS Test";
object MIA=System.Refl ection.Missing. Value;
pkg.SaveToSQLSe rver("MyServerN ame", "MyUserID", "MyPassword ",
DTS.DTSSQLServe rStorageFlags.D TSSQLStgFlag_De fault, "","","",re f
MIA,false);
pkg.Execute();
pkg.UnInitializ e();
pkg = null;
}

public void CreateConnectio ns()
{

DTS.Connection oConnection =
(DTS.Connection )pkg.Connection s.New("Microsof t.Jet.OLEDB.4.0 ");
oConnection.Nam e ="Connection 1";
oConnection.Dat aSource = "C:\\MySoureDB. MDB";
oConnection.ID = 1;
oConnection.Reu sable = true;
oConnection.Con nectImmediate = false;
oConnection.Con nectionTimeout = 60;
oConnection.Use TrustedConnecti on = false;
oConnection.Use DSL = false;
pkg.Connections .Add(oConnectio n);
oConnection = null;
DTS.Connection oConnection2 =
(DTS.Connection )pkg.Connection s.New("SQLOLEDB ");
oConnection2.Na me = "Connection 2";
oConnection2.ID = 2;
oConnection2.Re usable = true;
oConnection2.Co nnectImmediate= false;
oConnection2.Da taSource= "MyServerNa me";
oConnection2.Us erID = "MyUserID";
oConnection2.Co nnectionTimeout = 60;
oConnection2.Ca talog = "MyDestDB";
oConnection2.Us eTrustedConnect ion = false;
oConnection2.Us eDSL = false;
oConnection2.Pa ssword = "MyPassword ";
pkg.Connections .Add(oConnectio n2);
oConnection2 = null;
}

public void CreatePackageSt eps()
{
DTS.Step2 oStep = (DTS.Step2)pkg. Steps.New();
oStep.Name = "Copying Data from myTableName";
oStep.Descripti on = "Copying Data from myTableName";
oStep.TaskName = "Copying Data from myTableName";
oStep.CommitSuc cess = false;
oStep.RollbackF ailure = false;
oStep.ScriptLan guage = "VBScript";
oStep.AddGlobal Variables = true;
oStep.CloseConn ection = false;
oStep.ExecuteIn MainThread = true;
oStep.IsPackage DSORowset = false;
oStep.JoinTrans actionIfPresent = false;
oStep.DisableSt ep = false;
oStep.FailPacka geOnError = true;
pkg.Steps.Add(o Step);
oStep = null;

}

public void DefinTasks(DTS. Package2Class package)
{
DTS.DataPumpTas k2 oCustTask;
DTS.Task oTask;
oTask = pkg.Tasks.New(" DTSDataPumpTask ");
oCustTask = (DTS.DataPumpTa sk2)oTask.Custo mTask;
oCustTask.Name = "Copying Data from myTableName";
oCustTask.Descr iption = "Copying Data from myTableName to
MyDestDB.myTabl eName";
DTS.DataPumpTas k2 oDataPump = (DTS.DataPumpTa sk2)oTask.Custo mTask;
oDataPump.Sourc eConnectionID = 1;
oDataPump.Sourc eSQLStatement = "SELECT `MyField` FROM myTableName";
oDataPump.Desti nationConnectio nID =2;
oDataPump.Desti nationObjectNam e = "myTableNam e";
oDataPump.Progr essRowCount = 1000;
oDataPump.Maxim umErrorCount = 0;
oDataPump.Fetch BufferSize = 1;
oDataPump.UseFa stLoad=true;
oDataPump.Inser tCommitSize = 0;
oDataPump.Inser tCommitSize = 500000;
oDataPump.Excep tionFileColumnD elimiter = "|";
oDataPump.Excep tionFileRowDeli miter = "\n\r";
oDataPump.Allow IdentityInserts = false;
oDataPump.First Row = 0;
oDataPump.LastR ow = 0;
CreateTaskTrans (oDataPump, oCustTask);
pkg.Tasks.Add(o Task);
oCustTask = null;
oTask = null;

}
public void CreateTaskTrans (DTS.DataPumpTa sk DatPump,
DTS.DataPumpTas k2 CustTask)
{
DTS.Transformat ion2 oTransformation ;
oTransformation =
(DTS.Transforma tion2)CustTask. Transformations .New("DTS.DataP umpTransformCop y");
oTransformation .Name = "DirectCopyXfor m";
oTransformation .TransformFlags = 63;
oTransformation .ForceSourceBlo bsBuffered = 0;
oTransformation .ForceBlobsInMe mory = false;
oTransformation .InMemoryBlobSi ze = 1048576;
oTransformation .SourceColumns. AddColumn("MyFi eld",1);
oTransformation .DestinationCol umns.AddColumn( "MyField",1 );
DatPump.Transfo rmations.Add(oT ransformation);

}
}
}