Create DTS package in C#
- From: "Michael" <Michael.Covington@xxxxxxxxxxxxxxxxxxxxxx>
- Date: 5 Aug 2005 12:09:09 -0700
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;
}
.
- Prev by Date: Connecting to Paradox
- Next by Date: RE: Problem using GLobal Variables in DTS Package
- Previous by thread: Connecting to Paradox
- Next by thread: file conversion from SQL Server to Access
- Index(es):
Relevant Pages
|
