Board index » delphi » Sql Server / DTS / Delphi 6

Sql Server / DTS / Delphi 6

Greetings,

    Is there a way to call a DTS routine in SQL Server 2000 from a Delphi
app connected via ADO?

Thanks...LH

 

Re:Sql Server / DTS / Delphi 6


Take a look at some of the samples on http://www.sqldts.com. There are
basically 2 ways, use xp_cmdshell to call dtsrun or use SP_Oa to call the
object model

-Euan

Quote
"Lawrence Haake" <l...@nospamcompuserve.com> wrote in message

news:3dc1dbb9$1@newsgroups.borland.com...
Quote
> Greetings,

>     Is there a way to call a DTS routine in SQL Server 2000 from a Delphi
> app connected via ADO?

> Thanks...LH

Re:Sql Server / DTS / Delphi 6


There is one more - via OLE Automation:
var
    oPackage   : OleVariant;
    oConnection : OleVariant;
    oStep       : OleVariant;

  oPackage := CreateOleObject('DTS.Package2');
  oPackage.Name := 'My Cool DTS Package';
  oPackage.Description := 'DTS package for me';
...
  oConnection := oPackage.Connections.New('SQLOLEDB');
...
  oPackage.Connections.Add(oConnection);
  oStep := oPackage.Steps.New;
...
  oPackage.Steps.Add(oStep);
...
  oPackage.Execute;
Just hint: design DTS package by means of MS SQL EM and save as VB code - it
is easy to translate into ObjPascal.
Regards

Quote
"Euan Garden" <euan_gar...@spicedham.hotmail.com> wrote in message

news:3dc21742$1@newsgroups.borland.com...
Quote
> Take a look at some of the samples on http://www.sqldts.com. There are
> basically 2 ways, use xp_cmdshell to call dtsrun or use SP_Oa to call the
> object model

> -Euan

> "Lawrence Haake" <l...@nospamcompuserve.com> wrote in message
> news:3dc1dbb9$1@newsgroups.borland.com...
> > Greetings,

> >     Is there a way to call a DTS routine in SQL Server 2000 from a
Delphi
> > app connected via ADO?

> > Thanks...LH

Re:Sql Server / DTS / Delphi 6


Hi Euan,

I have the same problem as Lawrence, wanting to run a DTS routine (I have a
saved .DTS file, I don't know if he's referring to the same thing) against a
SQL Server 2000 DB via Delphi.

I'm not familiar with xp_cmdshell (or SP_Oa), would I be able to use the
ShellExecute function (or similar) to call dtsrun with the name of the .DTS
file to use? I realize I'm likely totally out to lunch here - I'm hoping a
more well-informed individual like yourself (or whoever else wants to reply)
can provide some helpful advice.

Thanks!

Jerry

Re:Sql Server / DTS / Delphi 6


Didn't catch the first question but does this help Only good on single item
dts files.

var
TheDTS,objConnect: Variant;
i:integer;
Begin
TheDTS:= CreateOleObject('DTS.Package');

try
TheDTS.LoadFromStorageFile(ExtractFilePath(ParamStr(0)) +
'\UpdateAM5.dts','');

//TheDTS.GlobalVariables.Item('TheMDB').value:='C:\Program Files\Agency
Manager\Data\amdata.mdb';

for i := 1 to TheDTS.Steps.count do
TheDTS.Steps.Item(i).ExecuteinMainThread := True;

TheDTS.FailOnError := true;

TheDTS.Execute;
except
on E: Exception do
      begin
        //Display the error message.//
        ShowMessage('{*word*81} something went wrong = ' + E.Message);
      end;

end;
TheDTS.UnInitialize;
TheDTS:= Unassigned;
ShowMessage('Done');

--

Brian Slack
http://www.depicus.com
"Wake On Lan" and "Remote Shutdown" Software

Quote
"Jerry Nichol" <je...@traxis.ca> wrote in message

news:3dd2c5e6@newsgroups.borland.com...
Quote
> Hi Euan,

> I have the same problem as Lawrence, wanting to run a DTS routine (I have
a
> saved .DTS file, I don't know if he's referring to the same thing) against
a
> SQL Server 2000 DB via Delphi.

> I'm not familiar with xp_cmdshell (or SP_Oa), would I be able to use the
> ShellExecute function (or similar) to call dtsrun with the name of the
.DTS
> file to use? I realize I'm likely totally out to lunch here - I'm hoping a
> more well-informed individual like yourself (or whoever else wants to
reply)
> can provide some helpful advice.

> Thanks!

> Jerry

Re:Sql Server / DTS / Delphi 6


Brian posted a way to do this through COM, if you want to use some sort of
shellexecute or createprocess then look into using the DTSRUN commandline
tool this is an executable, there are examples in Books OnLine and on
http://www.sdqldts.com

-Euan

Quote
"Jerry Nichol" <je...@traxis.ca> wrote in message

news:3dd2c5e6@newsgroups.borland.com...
Quote
> Hi Euan,

> I have the same problem as Lawrence, wanting to run a DTS routine (I have
a
> saved .DTS file, I don't know if he's referring to the same thing) against
a
> SQL Server 2000 DB via Delphi.

> I'm not familiar with xp_cmdshell (or SP_Oa), would I be able to use the
> ShellExecute function (or similar) to call dtsrun with the name of the
.DTS
> file to use? I realize I'm likely totally out to lunch here - I'm hoping a
> more well-informed individual like yourself (or whoever else wants to
reply)
> can provide some helpful advice.

> Thanks!

> Jerry

Other Threads