DTS Package Gives Me A Runtime Error

Hi,

 I am trying to create a DTS package dynamically. I have taken all these steps i.e.,

1. SN.EXE -K c:DTS.KEY
2.tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll /Keyfile:c:DTS.KEY
3.gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll.

Now when i compile the code, i didnt get any compilation error. But when execute the code it gives me a runtime error which goes as given below :

An unhandled exception of type 'System.InvalidCastException' occurred in DTSFactory.exe

Additional information: QueryInterface for interface Microsoft.SQLServer.DTSPkg80.CustomTask failed.


I  am getting this error near the line : oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;

The following is the code.

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using DTS = Microsoft.SQLServer.DTSPkg80;

namespace DTSFactory

{

/// <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 filesmicrosoft SQL Sever80ToolsBindtspkg.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 Form3 : 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();

public Form3()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

private void Form3_Load(object sender, System.EventArgs e)

{

 

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if(components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

 

#region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.button1 = new System.Windows.Forms.Button();

this.SuspendLayout();

//

// button1

//

this.button1.Location = new System.Drawing.Point(0, 0);

this.button1.Name = "button1";

this.button1.Size = new System.Drawing.Size(40, 56);

this.button1.TabIndex = 0;

this.button1.Text = "button1";

this.button1.Click += new System.EventHandler(this.button1_Click);

//

// Form3

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(648, 429);

this.Controls.Add(this.button1);

this.Name = "Form3";

this.Text = "Form3";

this.Load += new System.EventHandler(this.Form3_Load);

this.ResumeLayout(false);

}

#endregion

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 txtConn,sqlConn;

 

txtConn = pkg.Connections.New("DTSFlatFile");

//label1.Text = label2.Text = "";

 

//for (int i=1;i<txtConn.ConnectionProperties.Count;i++)

// label1.Text = label1.Text + "
" + txtConn.ConnectionProperties.Item(i).Name;

//for text connection

//start

txtConn.ConnectionProperties.Item(1).Value = "C:\FlatFiles\1.txt";

txtConn.ConnectionProperties.Item(3).Value = 1;

txtConn.ConnectionProperties.Item(4).Value = "
";

txtConn.ConnectionProperties.Item(5).Value = 1;

txtConn.ConnectionProperties.Item(7).Value = "|";

txtConn.ConnectionProperties.Item(9).Value = 1;

txtConn.ConnectionProperties.Item(10).Value = 0;

txtConn.ConnectionProperties.Item(11).Value = """;

txtConn.ConnectionProperties.Item(12).Value = false;

txtConn.ConnectionProperties.Item(14).Value = 0;

txtConn.ConnectionProperties.Item(18).Value = 255;

 

txtConn.Name = "Text File (Source)";

txtConn.ID = 1;

txtConn.Reusable = true;

txtConn.ConnectImmediate = false;

txtConn.DataSource = "C:\FlatFiles\1.txt";

txtConn.ConnectionTimeout = 60;

txtConn.UseTrustedConnection = false;

txtConn.UseDSL = false;

//end

pkg.Connections.Add(txtConn);

sqlConn = pkg.Connections.New("SQLOLEDB");

 

//for (int i=1;i<sqlConn.ConnectionProperties.Count;i++)

// label2.Text = label2.Text + "
" + sqlConn.ConnectionProperties.Item(i).Name;

sqlConn.ConnectionProperties.Item(3).Value = true;

sqlConn.ConnectionProperties.Item(4).Value = "sa";

sqlConn.ConnectionProperties.Item(5).Value = "FCSUAT";

sqlConn.ConnectionProperties.Item(6).Value = "(local)";

sqlConn.ConnectionProperties.Item(19).Value = "DTS Designer";

 

sqlConn.Name = "Microsoft OLE DB Provider for SQL Server";

sqlConn.ID = 2;

sqlConn.Reusable = true;

sqlConn.ConnectImmediate = false;

sqlConn.DataSource = "(local)";

sqlConn.UserID = "sa";

sqlConn.ConnectionTimeout = 60;

sqlConn.Catalog = "FCSUAT";

sqlConn.UseTrustedConnection = false;

sqlConn.UseDSL = false;

 

pkg.Connections.Add(sqlConn);

 

}

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 = package.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 = "
";

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

}

}

}

 

Can anyone help me in solving this? 

Thanks in advance.