dotnet interop: 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.InvalidCastException'. 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.DataPumpTask DT =3D (DTS.DataPumpTask)package.Task=ADs.New
("DTSDataPumpTask");


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 =3D
(DTS.Connection)package.Connec=ADtions.New("Microsoft.Jet.OLEDB=AD.4.0");

oConnection.Name =3D"Connection 1";
oConnection.DataSource =3D "C:\\MySourceDB.MDB";
oConnection.ID =3D 1;
oConnection.Reusable =3D true;
oConnection.ConnectImmediate =3D false;
oConnection.ConnectionTimeout =3D 60;
oConnection.UseTrustedConnecti=ADon =3D false;
oConnection.UseDSL =3D false;
oConnection =3D null;
DTS.Connection oConnection2 =3D
(DTS.Connection)package.Connec=ADtions.New("SQLOLEDB");
oConnection2.Name =3D "Connection 2";
oConnection2.ID =3D 2;
oConnection2.Reusable =3D true;
oConnection2.ConnectImmediate=3D false;
oConnection2.DataSource=3D "MyServerName";
oConnection2.UserID =3D "MyUserID";
oConnection2.ConnectionTimeout =3D 60;
oConnection2.Catalog =3D "MyDestDB";
oConnection2.UseTrustedConnect=ADion =3D false;
oConnection2.UseDSL =3D false;
oConnection2.Password =3D "MyPassword";
oConnection2 =3D null;
DTS.Step2 oStep =3D
(DTS.Step2)package.Steps.New()=AD;
oStep.Name =3D "Copying Data from MyTable";
oStep.Description =3D "Copying Data from
MyTable";
oStep.TaskName =3D "Copying Data from MyTable";
oStep.CommitSuccess =3D false;
oStep.RollbackFailure =3D false;
oStep.ScriptLanguage =3D "VBScript";//not sure
about this
oStep.AddGlobalVariables =3D true;
oStep.CloseConnection =3D false;
oStep.ExecuteInMainThread =3D true;
oStep.IsPackageDSORowset =3D false;
oStep.JoinTransactionIfPresent =3D false;
oStep.DisableStep =3D false;
oStep.FailPackageOnError =3D true;
package.Steps.Add(oStep);
oStep =3D null;
DTS.Task oTask =3D
(DTS.Task)package.Tasks.New("D=ADTSDataPumpTask");
oTask.Name =3D "Copying Data from MyTable";
DTS.CustomTask oCustomTask =3D oTask.CustomTask;
oCustomTask.Name =3D "Copying Data from MyTable";

oCustomTask.Description =3D "Copying Data from
MyTable to
MyDestDB.MyTable";
DTS.DataPumpTask DT =3D
(DTS.DataPumpTask)package.Task=ADs.New("DTSDataPumpTask");
DT.SourceConnectionID =3D 1;
DT.SourceSQLStatement =3D "SELECT `TestField`
FROM MyTable";
DT.DestinationConnectionID =3D2;
DT.DestinationObjectName =3D "MyTable";
DT.ProgressRowCount =3D 1000;
DT.MaximumErrorCount =3D 0;
DT.FetchBufferSize =3D 1;
DT.UseFastLoad=3Dtrue;
DT.InsertCommitSize =3D 0;
DT.InsertCommitSize =3D 500000;
DT.ExceptionFileColumnDelimite=ADr =3D "|";
DT.ExceptionFileRowDelimiter =3D "\r\n";
DT.AllowIdentityInserts =3D false;
DT.FirstRow =3D 0;
DT.LastRow =3D 0;
DTS.Transformation Trans =3D
(DTS.Transformation)package.Ta=ADsks.New("DataPumpTransformCopy=AD");
Trans.Name =3D "DirectCopyXform";
Trans.TransformFlags =3D 63;
Trans.ForceSourceBlobsBuffered =3D 0;
Trans.ForceBlobsInMemory =3D false;
Trans.InMemoryBlobSize =3D 1048576;
Trans.SourceColumns.AddColumn(=AD"TestField",1);


Trans.DestinationColumns.AddCo=ADlumn("TestField",1);
DT.Transformations.Add(Trans);
package.Tasks.Add(oTask);
oCustomTask =3D null;
oTask =3D null;=20


}

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 invalidcastexception.

DTS.Task oTask = (DTS.Task)package.Tasks.New("DTSDataPumpTask");
DTS.CustomTask oCustomTask = oTask.CustomTask;

This was how I corrected it.

DTS.DataPumpTask2 oCustTask;
DTS.Task oTask;
oTask = pkg.Tasks.New("DTSDataPumpTask");
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;

Here is the entire example:


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DTS = Microsoft.SQLServer.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:\Microsoft.SQLServer.DTSPkg80.dll
/Keyfile:c:\DTS.KEY
///gacutil.exe -i C:\Microsoft.SQLServer.DTSPkg80.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.ComponentModel.Container components = null;
private System.Windows.Forms.Button button1;
public DTS.Package2Class pkg = new DTS.Package2Class();

private void button1_Click(object sender, System.EventArgs e)
{
initpackage();
}

public void initpackage()
{
CreateConnections();
CreatePackageSteps();
DefinTasks(pkg);
pkg.Name="MyCSharpDTSTest";
pkg.Description = "CShart DTS Test";
object MIA=System.Reflection.Missing.Value;
pkg.SaveToSQLServer("MyServerName", "MyUserID", "MyPassword",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","","",ref
MIA,false);
pkg.Execute();
pkg.UnInitialize();
pkg = null;
}

public void CreateConnections()
{

DTS.Connection oConnection =
(DTS.Connection)pkg.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.Name ="Connection 1";
oConnection.DataSource = "C:\\MySoureDB.MDB";
oConnection.ID = 1;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
pkg.Connections.Add(oConnection);
oConnection = null;
DTS.Connection oConnection2 =
(DTS.Connection)pkg.Connections.New("SQLOLEDB");
oConnection2.Name = "Connection 2";
oConnection2.ID = 2;
oConnection2.Reusable = true;
oConnection2.ConnectImmediate= false;
oConnection2.DataSource= "MyServerName";
oConnection2.UserID = "MyUserID";
oConnection2.ConnectionTimeout = 60;
oConnection2.Catalog = "MyDestDB";
oConnection2.UseTrustedConnection = false;
oConnection2.UseDSL = false;
oConnection2.Password = "MyPassword";
pkg.Connections.Add(oConnection2);
oConnection2 = null;
}

public void CreatePackageSteps()
{
DTS.Step2 oStep = (DTS.Step2)pkg.Steps.New();
oStep.Name = "Copying Data from myTableName";
oStep.Description = "Copying Data from myTableName";
oStep.TaskName = "Copying Data from myTableName";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = true;
pkg.Steps.Add(oStep);
oStep = null;

}

public void DefinTasks(DTS.Package2Class package)
{
DTS.DataPumpTask2 oCustTask;
DTS.Task oTask;
oTask = pkg.Tasks.New("DTSDataPumpTask");
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;
oCustTask.Name = "Copying Data from myTableName";
oCustTask.Description = "Copying Data from myTableName to
MyDestDB.myTableName";
DTS.DataPumpTask2 oDataPump = (DTS.DataPumpTask2)oTask.CustomTask;
oDataPump.SourceConnectionID = 1;
oDataPump.SourceSQLStatement = "SELECT `MyField` FROM myTableName";
oDataPump.DestinationConnectionID =2;
oDataPump.DestinationObjectName = "myTableName";
oDataPump.ProgressRowCount = 1000;
oDataPump.MaximumErrorCount = 0;
oDataPump.FetchBufferSize = 1;
oDataPump.UseFastLoad=true;
oDataPump.InsertCommitSize = 0;
oDataPump.InsertCommitSize = 500000;
oDataPump.ExceptionFileColumnDelimiter = "|";
oDataPump.ExceptionFileRowDelimiter = "\n\r";
oDataPump.AllowIdentityInserts = false;
oDataPump.FirstRow = 0;
oDataPump.LastRow = 0;
CreateTaskTrans(oDataPump, oCustTask);
pkg.Tasks.Add(oTask);
oCustTask = null;
oTask = null;

}
public void CreateTaskTrans(DTS.DataPumpTask DatPump,
DTS.DataPumpTask2 CustTask)
{
DTS.Transformation2 oTransformation;
oTransformation =
(DTS.Transformation2)CustTask.Transformations.New("DTS.DataPumpTransformCopy");
oTransformation.Name = "DirectCopyXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = 0;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.SourceColumns.AddColumn("MyField",1);
oTransformation.DestinationColumns.AddColumn("MyField",1);
DatPump.Transformations.Add(oTransformation);

}
}
}