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 files\microsoft SQL Sever\80\Tools\Bin\dtspkg.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 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>publicclass 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//

}

privatevoid Form3_Load(object sender, System.EventArgs e)

{

}

///<summary>/// Clean up any resources being used.///</summary>protectedoverridevoid 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>privatevoid 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

privatevoid button1_Click(object sender, System.EventArgs e)

{

initpackage();

}

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

}

publicvoid 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 + "\r\n" + 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 = "\r\n";

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 + "\r\n" + 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);

}

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

}

publicvoid 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 = "\n\r";

oDataPump.AllowIdentityInserts =

false;

oDataPump.FirstRow = 0;

oDataPump.LastRow = 0;

CreateTaskTrans(oDataPump, oCustTask);

pkg.Tasks.Add(oTask);

oCustTask =

null;

oTask =

null;

}

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

Hi muser,

This seems to be an interop issue. I suggest you put the interop assembly together with your .exe file in the same directory and have another try.

Please make it as an unstrong named assembly for test.