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.

DTS Package Fails after 1000 Rows......

I am having a DTS package which copies data from Foxpro DBF to Sql Server. This DTS package is called in a VB program and Global Variables for DTS is set inside the VB program based on which DTS package copies the Data.

The problem which i am facing is, after 1000 rows transferred using DTS package, DTS package fails and starts giving Errors.

Inside the VB program i have ensured that the DTS package is released properly.

Please help me out, need a solution to this very badly......

Thanks

Pranjal Sharma

This may be an extreme but may be relative. Blank records or special characters being filtered through VB can cause a halt in the transfer.

If it errors after exactly 1000 records every time, I'd look at the content of that record specifically. If it errors after approximately 1000 records, it maybe timing out.

Adamus

|||Posting the error message will help...

DTS Package Fails after 1000 Rows......

I am having a DTS package which copies data from Foxpro DBF to Sql Server. This DTS package is called in a VB program and Global Variables for DTS is set inside the VB program based on which DTS package copies the Data.

The problem which i am facing is, after 1000 rows transferred using DTS package, DTS package fails and starts giving Errors.

Inside the VB program i have ensured that the DTS package is released properly.

Please help me out, need a solution to this very badly......

Thanks

Pranjal Sharma

This may be an extreme but may be relative. Blank records or special characters being filtered through VB can cause a halt in the transfer.

If it errors after exactly 1000 records every time, I'd look at the content of that record specifically. If it errors after approximately 1000 records, it maybe timing out.

Adamus

|||Posting the error message will help...

DTS Jobs Troubles

I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.
What can I do?
I hope your answer
Thanks
Cristopher SerratoCheck your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

Originally posted by cjserrato
I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.

What can I do?

I hope your answer

Thanks

Cristopher Serrato|||Originally posted by Ben Mansouri
Check your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

I CHECKED MY CONECTION AND IT'S GOOD, EVEN IF I PLAY THE DTS PACKAGE, IT WORKS BUT WHEN I PUT IT INTO A JOB IT FAILS.

I DOWNLOADED THE SERVICE PACK 3A, BUT IT DOES NOT WORK YET.

WHAT COULD IT BE SO?|||Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Is there any transformation envolved on DTS ?

Originally posted by Satya
Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Originally posted by Ben Mansouri
Is there any transformation envolved on DTS ?

No, the only one that I got is

AS400 -> SQL SERVER 2000

DTS Invocation

I am wondering if DTS packages can be invoked by a stand along program, say a
C# program?
I not really up on DTS, so this might sound like a silly question, but if I
have several DTS packages which need to be run one after another, is there a
way that they can be packaged up such that all I have to do is run one
package and it will run them one by one in the order I have specified?
Thanks in advance for your assistance!!!!
Hello Jim,
you can easily run other DTS packages inside a DTS package with the "DTS
execute" action. With the workflow management you are able to select the
order and can do simple fail back and abort switches between DTS packages.
Greetings
Helmut Rieder
"Jim Heavey" wrote:

> I am wondering if DTS packages can be invoked by a stand along program, say a
> C# program?
> I not really up on DTS, so this might sound like a silly question, but if I
> have several DTS packages which need to be run one after another, is there a
> way that they can be packaged up such that all I have to do is run one
> package and it will run them one by one in the order I have specified?
> Thanks in advance for your assistance!!!!

DTS Invocation

I am wondering if DTS packages can be invoked by a stand along program, say
a
C# program?
I not really up on DTS, so this might sound like a silly question, but if I
have several DTS packages which need to be run one after another, is there a
way that they can be packaged up such that all I have to do is run one
package and it will run them one by one in the order I have specified?
Thanks in advance for your assistance!!!!Hello Jim,
you can easily run other DTS packages inside a DTS package with the "DTS
execute" action. With the workflow management you are able to select the
order and can do simple fail back and abort switches between DTS packages.
Greetings
Helmut Rieder
"Jim Heavey" wrote:

> I am wondering if DTS packages can be invoked by a stand along program, sa
y a
> C# program?
> I not really up on DTS, so this might sound like a silly question, but if
I
> have several DTS packages which need to be run one after another, is there
a
> way that they can be packaged up such that all I have to do is run one
> package and it will run them one by one in the order I have specified?
> Thanks in advance for your assistance!!!!

DTS Invocation

I am wondering if DTS packages can be invoked by a stand along program, say a
C# program?
I not really up on DTS, so this might sound like a silly question, but if I
have several DTS packages which need to be run one after another, is there a
way that they can be packaged up such that all I have to do is run one
package and it will run them one by one in the order I have specified?
Thanks in advance for your assistance!!!!Hello Jim,
you can easily run other DTS packages inside a DTS package with the "DTS
execute" action. With the workflow management you are able to select the
order and can do simple fail back and abort switches between DTS packages.
Greetings
Helmut Rieder
"Jim Heavey" wrote:
> I am wondering if DTS packages can be invoked by a stand along program, say a
> C# program?
> I not really up on DTS, so this might sound like a silly question, but if I
> have several DTS packages which need to be run one after another, is there a
> way that they can be packaged up such that all I have to do is run one
> package and it will run them one by one in the order I have specified?
> Thanks in advance for your assistance!!!!