I am attempting to create a DTS Package in C# so that I can manipulate a large TSQL statement at run time. I am first trying to copy an excel sheet into a temporary table, then I plan on performing the query.

I created a basic DTS package using the designer then saved it as a vb file. I am in the process of converting the vb to C#. I ran into one major problem, I keep getting the following error:

Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Can anyone assist me with getting past this error?

I am using MSSQL 2000.

Here is my code....


 //Create DTS Package....
DTS.Package dtsP = new DTS.Package();
DTS.Package2 dtsP2 = new DTS.Package2();
dtsP2 = (DTS.Package2)dtsP;//cast.

//A
//Complete required fields for new package.
dtsP2.Name = "New Package";
dtsP2.WriteCompletionStatusToNTEventLog = false;
dtsP2.FailOnError = false;
dtsP2.PackagePriorityClass = ((DTS.DTSPackagePriorityClass)2);
dtsP2.MaxConcurrentSteps = 4;
dtsP2.LineageOptions = 0;
dtsP2.UseTransaction = true;
dtsP2.TransactionIsolationLevel = ((DTS.DTSIsolationLevel)4096);
dtsP2.AutoCommitTransaction = true;
dtsP2.RepositoryMetadataOptions = 0;
dtsP2.UseOLEDBServiceComponents = true;
dtsP2.LogToSQLServer = false;
dtsP2.LogServerFlags = ((DTS.DTSSQLServerStorageFlags)256);
dtsP2.FailPackageOnLogFailure = false;
dtsP2.ExplicitGlobalVariables = false;
dtsP2.PackageType = 0;

//---------------------------------------------------------------
// Create and Add Global Variables.
//---------------------------------------------------------------
//DTS.GlobalVariable supGlobal;
//supGlobal = dtsP2.GlobalVariables.New("supplier");
//supGlobal.Value = txtSupplierID.Text;
//dtsP2.GlobalVariables.Add(supGlobal);
//---------------------------------------------------------------

//-------------------------------------------------------------------------------------
//Create a connection to the database.
//-------------------------------------------------------------------------------------
//B
DTS.Connection dtcDTSConn = dtsP2.Connections.New("SQLOLEDB");
//C
dtcDTSConn.ConnectionProperties.Item("Integrated Security").Value = "SSPI";
dtcDTSConn.ConnectionProperties.Item("Persist Security Info").Value = "true";
dtcDTSConn.ConnectionProperties.Item("Initial Catalog").Value = "QuotingTool";
dtcDTSConn.ConnectionProperties.Item("Data Source").Value="AUTODESK";
dtcDTSConn.ConnectionProperties.Item("Application Name").Value="DTS Designer";

//D
dtcDTSConn.Name = "Microsoft OLE DB Provider for SQL Server";
dtcDTSConn.ID = 1;
dtcDTSConn.Reusable = true;
dtcDTSConn.ConnectImmediate = false;
dtcDTSConn.DataSource = "AUTODESK";
dtcDTSConn.ConnectionTimeout = 120;
dtcDTSConn.Catalog = "AUTODESK";
dtcDTSConn.UseTrustedConnection = true;
dtcDTSConn.UseDSL = false;
dtcDTSConn.Password = "alabamalabama";

//E
dtsP2.Connections.Add(dtcDTSConn);

//-------------------------------------------------------------------------------------
//Create a connection to the incoming Excel worksheet.
//-------------------------------------------------------------------------------------
//F
DTS.Connection dtcExcelConn = dtsP2.Connections.New("Microsoft.Jet.OLEDB.4.0");

//G
dtcExcelConn.ConnectionProperties.Item("User ID").Value = "Admin";
dtcExcelConn.ConnectionProperties.Item("Data Source").Value = strIncomingFileName;
dtcExcelConn.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";
dtcExcelConn.Name = "Microsoft Excel 97-2000";
dtcExcelConn.ID = 2;
dtcExcelConn.Reusable = true;
dtcExcelConn.ConnectImmediate = false;
dtcExcelConn.DataSource = strIncomingFileName;
dtcExcelConn.UserID = "Admin";
dtcExcelConn.ConnectionTimeout = 60;
dtcExcelConn.UseTrustedConnection = false;
dtcExcelConn.UseDSL = false;
//H
dtsP2.Connections.Add(dtcExcelConn);

//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
//Create a Data Pump Task
//-------------------------------------------------------------------------------------
//I
DTS.Step2 dtcDTSStep = (DTS.Step2)dtsP2.Steps.New();
//J
dtcDTSStep.Name = "DTSStep_DTSDataPumpTask_1";
dtcDTSStep.Description = "Transform Data Task: undefined";
dtcDTSStep.ExecutionStatus=((DTS.DTSStepExecStatus )4);
dtcDTSStep.TaskName = "DTSTask_DTSDataPumpTask_1";
dtcDTSStep.CommitSuccess = false;
dtcDTSStep.RollbackFailure = false;
dtcDTSStep.ScriptLanguage = "VBScript";
dtcDTSStep.AddGlobalVariables = true;
dtcDTSStep.RelativePriority=((DTS.DTSStepRelativeP riority)3);
dtcDTSStep.CloseConnection = true;
dtcDTSStep.ExecuteInMainThread = false;
dtcDTSStep.IsPackageDSORowset = false;
dtcDTSStep.JoinTransactionIfPresent = false;
dtcDTSStep.DisableStep = false;
dtcDTSStep.FailPackageOnError = false;
//K
dtsP2.Steps.Add(dtcDTSStep);

//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
// Create a Custom Task
//-------------------------------------------------------------------------------------
//L

DTS.DataPumpTask2 dtcDTSCustomTask1;
DTS.Task dtcDTSTask;
dtcDTSTask = dtsP2.Tasks.New("DTSDataPumpTask");
dtcDTSCustomTask1 = (DTS.DataPumpTask2)dtcDTSTask.CustomTask;
dtcDTSTask.Name = "DTSTask_DTSDataPumpTask_1";

dtcDTSCustomTask1.SourceConnectionID = 2;
//-------------------------------------------------------------------------------------
//
//-------------------------------------------------------------------------------------
//N
dtcDTSCustomTask1.SourceSQLStatement = " SELECT * " + "\n";
dtcDTSCustomTask1.SourceSQLStatement += " FROM [Sheet1$] ";
//M
dtcDTSCustomTask1.DestinationConnectionID = 1;
dtcDTSCustomTask1.DestinationObjectName = "[QuotingTool].[dbo].[tblSearchResults]";//Search Results to be held in the db.
dtcDTSCustomTask1.ProgressRowCount = 1000;
dtcDTSCustomTask1.MaximumErrorCount = 0;
dtcDTSCustomTask1.FetchBufferSize = 1;
dtcDTSCustomTask1.UseFastLoad = true;
dtcDTSCustomTask1.InsertCommitSize = 0;
dtcDTSCustomTask1.ExceptionFileColumnDelimiter = "|";
dtcDTSCustomTask1.ExceptionFileRowDelimiter = Convert.ToString(Convert.ToChar(13))
+ Convert.ToString(Convert.ToChar(10));
dtcDTSCustomTask1.AllowIdentityInserts = false;
dtcDTSCustomTask1.FirstRow = 0;
dtcDTSCustomTask1.LastRow = 0;
dtcDTSCustomTask1.FastLoadOptions = ((DTS.DTSFastLoadOptions)2);
dtcDTSCustomTask1.ExceptionFileOptions = ((DTS.DTSExceptionFileOptions)1);
dtcDTSCustomTask1.DataPumpOptions = 0;

int intTransformationCount = 2;

//-------------------------------------------------------------------------------------
//Define our transformations.
//-------------------------------------------------------------------------------------
DTS.Transformation2 quotingTrans = (DTS.Transformation2)dtcDTSCustomTask1.Transformat ions.New("DTSPump.DataPumpTransformScript");
quotingTrans.Name = "DTSTransformation__1";
quotingTrans.TransformFlags = 63;
quotingTrans.ForceSourceBlobsBuffered = 0;
quotingTrans.ForceBlobsInMemory = false;
quotingTrans.InMemoryBlobSize = 1048576;
quotingTrans.TransformPhases = 4;

DTS.Column itemIDColumn = quotingTrans.SourceColumns.New("itemID", 1);

itemIDColumn.Name = "itemID";
itemIDColumn.Ordinal = 1;
itemIDColumn.Flags = 120;
itemIDColumn.Size = 0;
itemIDColumn.DataType = 131;
itemIDColumn.Precision = 19;
itemIDColumn.NumericScale = 0;
itemIDColumn.Nullable = true;
quotingTrans.DestinationColumns.Add(itemIDColumn);

DTS.Column itemDescriptionColumn = quotingTrans.SourceColumns.New("itemDescription", 2);

itemDescriptionColumn.Name = "itemDescription";
itemDescriptionColumn.Ordinal = 1;
itemDescriptionColumn.Flags = 120;
itemDescriptionColumn.Size = 0;
itemDescriptionColumn.DataType = 131;
itemDescriptionColumn.Precision = 19;
itemDescriptionColumn.NumericScale = 0;
itemDescriptionColumn.Nullable = true;
quotingTrans.DestinationColumns.Add(itemDescriptio nColumn);

DTS.Column supplierIDNumberColumn = quotingTrans.SourceColumns.New("supplierIDNumber", 3);

supplierIDNumberColumn.Name = "supplierIDNumber";
supplierIDNumberColumn.Ordinal = 1;
supplierIDNumberColumn.Flags = 120;
supplierIDNumberColumn.Size = 0;
supplierIDNumberColumn.DataType = 131;
supplierIDNumberColumn.Precision = 19;
supplierIDNumberColumn.NumericScale = 0;
supplierIDNumberColumn.Nullable = true;
quotingTrans.DestinationColumns.Add(supplierIDNumb erColumn);

DTS.Column supplierPartNumberColumn = quotingTrans.SourceColumns.New("supplierPartNumber ", 4);

supplierPartNumberColumn.Name = "supplierPartNumber";
supplierPartNumberColumn.Ordinal = 1;
supplierPartNumberColumn.Flags = 120;
supplierPartNumberColumn.Size = 0;
supplierPartNumberColumn.DataType = 131;
supplierPartNumberColumn.Precision = 19;
supplierPartNumberColumn.NumericScale = 0;
supplierPartNumberColumn.Nullable = true;
quotingTrans.DestinationColumns.Add(supplierPartNu mberColumn);

DTS.Column supplierNameColumn = quotingTrans.SourceColumns.New("supplierName", 5);

supplierNameColumn.Name = "supplierName";
supplierNameColumn.Ordinal = 1;
supplierNameColumn.Flags = 120;
supplierNameColumn.Size = 0;
supplierNameColumn.DataType = 131;
supplierNameColumn.Precision = 19;
supplierNameColumn.NumericScale = 0;
supplierNameColumn.Nullable = true;
quotingTrans.DestinationColumns.Add(supplierNameCo lumn);


DTS.Properties quotingTransProps = quotingTrans.TransformServerProperties;
dtcDTSCustomTask1.Transformations.Add(quotingTrans );

dtsP2.Tasks.Add(dtcDTSTask);
dtsP2.Execute();

dtsP2.Execute();
string str1 = "";
string str2 = "";
string str3 = "";
int errcode = 0; ;
string str4 = "";
int int2 = 0;
dtsP2.Steps.Item(1).GetExecutionErrorInfo(out errcode, out str1, out str2, out str3, out int2, out str4);
DTS.DTSStepExecResult drst = dtsP2.Steps.Item(1).ExecutionResult;
int i = 0;




Thanks,


David