Hello,

I'm newbie in dts and i try to export date from msde2000 to excel with
vb.net or c#.

So i defined 3 steps in my programm
1. Drop table
2. Create table
3. Add Data

When i execute this program, the data are not deleted in my
spreadsheet
but it continues to append rows to existing rows.

I don't understand where is the problem !!!

Thanks in advance.


This is my programm :

using DT = Microsoft.SQLServer.DTSPkg80;


DT.PackageClass pack = new DT.PackageClass();
pack.Name = "test";
pack.Description = " test de DTS";
pack.FailOnError = true;

// Définition des connections

// Connection 1
DT.Connection cnn = pack.Connections.New("SQLOLEDB.1");
cnn.Name = "Source";
cnn.ID = 1;
cnn.ConnectionTimeout = 60;
pack.Connections.Add(cnn);
cnn = null;
// Connection 2
cnn = pack.Connections.New("Microsoft.Jet.OLEDB.4.0");
cnn.ID = 2;
cnn.ConnectionProperties.Item("Extended Properties").Value = "Excel
8.0;HDR=YES;";
pack.Connections.Add(cnn);
cnn = null;

// ---------------------------------------------------
// Creation des étapes
// ----------------------------------------------------

DT.Step ostep = pack.Steps.New();
ostep.Name = "Etape1";
ostep.TaskName = "Tache1";
pack.Steps.Add(ostep);
ostep = null;

ostep = pack.Steps.New();
ostep.Name = "Etape2";
ostep.TaskName = "Tache2";
pack.Steps.Add(ostep);
ostep = null;

ostep = pack.Steps.New();
ostep.Name = "Etape3";
ostep.TaskName = "Tache3";
pack.Steps.Add(ostep);
ostep=null;

ostep = pack.Steps.Item("Etape2");
DT.PrecedenceConstraint pr =
ostep.PrecedenceConstraints.New("Etape1");
pr.StepName = "Etape1";
pr.PrecedenceBasis =0;
pr.Value = 4;
ostep.PrecedenceConstraints.Add(pr);
pr = null;
ostep = null;
ostep = pack.Steps.Item("Etape3");
pr = ostep.PrecedenceConstraints.New("Etape2");
pr.StepName = "Etape2";
pr.PrecedenceBasis = 0;
pr.Value = 4;
ostep.PrecedenceConstraints.Add(pr);
pr = null;
ostep = null;

//------------------------------------------------------
// Création des taches
//---------------------------------------------------

DT.Task ta = pack.Tasks.New("DTSExecuteSQLTask");
DT.ExecuteSQLTask oc = (Microsoft.SQLServer.DTSPkg80.ExecuteSQLTask
)ta.CustomTask;
oc.Name = "Tache1";
oc.ConnectionID = 2;
oc.SQLStatement = "DROP TABLE `table1`";
pack.Tasks.Add(ta);
oc = null;
ta = null;

ta = pack.Tasks.New("DTSExecuteSQLTask");
oc = (Microsoft.SQLServer.DTSPkg80.ExecuteSQLTask )ta.CustomTask;
oc.Name = "Tache2";
oc.ConnectionID = 2;
oc.SQLStatement = "CREATE TABLE `table1` (`col1` varchar(40)) ";
pack.Tasks.Add(ta);
oc = null;
ta = null;

ta = pack.Tasks.New("DTSDataPumpTask");
DT.DataPumpTask oc1 = (Microsoft.SQLServer.DTSPkg80.DataPumpTask)
ta.CustomTask;
oc1.Name = "Tache3";
oc1.SourceConnectionID = 1;
oc1.SourceSQLStatement = " SELECT sqlcol1 from sqltable ";
oc1.DestinationConnectionID = 2;
oc1.DestinationObjectName = "table1$";
DT.Transformation trans = oc1.Transformations.New
"DTSPump.DataPumpTransformCopy");
trans.Name = "Trans1";
trans.SourceColumns.AddColumn ("sqlcol1",1);
trans.DestinationColumns.AddColumn("col1",1);
oc1.Transformations.Add(trans);
pack.Tasks.Add(ta);
oc = null;
ta = null;
// ----------------------------------------
// execution
// -------------------------------------

{
pack.Execute();
foreach(DT.Step st in pack.Steps)
{
MessageBox.Show(st.Name + " -> " + st.ExecutionResult.ToString() );
}
pack.UnInitialize();

How do you "drop table?" My understanding is that you would need to get into the Excel object model and clear or delete a range of cells or rows

Russ

Russel Loski, MCSD <RLoski (AT) NSPMhotmail (DOT) com> wrote

Thanks
That's right.
In fact, i thought that instruction "drop table" and "create table" was equivalent.
but if i use an instruction "Create table", program create a new spreadsheet
(if spreadsheet doesn't exist),
if i use "DROP TABLE", spreadsheet are not delete, it's only the structure
of spreadsheet who is remove.

New question ? How can i clear spreadsheet in using DTS
(using macro excel,DTS,run a macro with dts !!!)