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.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 = (DTS.DataPumpTask)package.Tasks.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 =
(DTS.Connection)package.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.Name ="Connection 1";
oConnection.DataSource = "C:\\MySourceDB.MDB";
oConnection.ID = 1;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
oConnection = null;
DTS.Connection oConnection2 =
(DTS.Connection)package.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";
oConnection2 = null;
DTS.Step2 oStep = (DTS.Step2)package.Steps.New();
oStep.Name = "Copying Data from MyTable";
oStep.Description = "Copying Data from MyTable";
oStep.TaskName = "Copying Data from MyTable";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";//not sure about this
oStep.AddGlobalVariables = true;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = true;
package.Steps.Add(oStep);
oStep = null;
DTS.Task oTask = (DTS.Task)package.Tasks.New("DTSDataPumpTask");
oTask.Name = "Copying Data from MyTable";
DTS.CustomTask oCustomTask = oTask.CustomTask;
oCustomTask.Name = "Copying Data from MyTable";
oCustomTask.Description = "Copying Data from MyTable to
MyDestDB.MyTable";
DTS.DataPumpTask DT =
(DTS.DataPumpTask)package.Tasks.New("DTSDataPumpTask");
DT.SourceConnectionID = 1;
DT.SourceSQLStatement = "SELECT `TestField` FROM MyTable";
DT.DestinationConnectionID =2;
DT.DestinationObjectName = "MyTable";
DT.ProgressRowCount = 1000;
DT.MaximumErrorCount = 0;
DT.FetchBufferSize = 1;
DT.UseFastLoad=true;
DT.InsertCommitSize = 0;
DT.InsertCommitSize = 500000;
DT.ExceptionFileColumnDelimiter = "|";
DT.ExceptionFileRowDelimiter = "\r\n";
DT.AllowIdentityInserts = false;
DT.FirstRow = 0;
DT.LastRow = 0;
DTS.Transformation Trans =
(DTS.Transformation)package.Tasks.New("DataPumpTransformCopy");
Trans.Name = "DirectCopyXform";
Trans.TransformFlags = 63;
Trans.ForceSourceBlobsBuffered = 0;
Trans.ForceBlobsInMemory = false;
Trans.InMemoryBlobSize = 1048576;
Trans.SourceColumns.AddColumn("TestField",1);
Trans.DestinationColumns.AddColumn("TestField",1);
DT.Transformations.Add(Trans);
package.Tasks.Add(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/2003/Jan/DTSCustTask.asp


Regards,

 

[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 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);

}
}
}