Board index » delphi » Creation of DTS CustomTask in Delphi

Creation of DTS CustomTask in Delphi

I'm trying create DTS CustomTask in Delphi without inheritance from
CustomTask interface like in Microsoft Visual C++ examples that shipped with
SQL Server. The problem is that after registration of my task in SQL Package
Designer I can't add the task to the new package - there is Package Error
"No such interface supported".

Can anyone please suggest a way to solve this problem ?

Thank You.

Vadik

 

Re:Creation of DTS CustomTask in Delphi


You need to do a fair amount of work to get this to work but here is an
interface def for a custom task that works, but does nothing other than a
show message in the execute method. If you want UI then you need to add a
reference to CustomTaskUI in the class definition.

-Euan

unit NoInt;

interface

uses
  ComObj, ActiveX, CTask3_TLB, StdVcl, DTS_TLB;

type
  TNoInt = class(TAutoObject, CustomTask)
  private
   FName : WideString;
    FDescription : WideString;
  protected
    function Get_Description: WideString; safecall;
    function Get_Name: WideString; safecall;
    function Get_Properties: Properties; safecall;
    procedure Execute(const pPackage, pPackageEvents, pPackageLog:
IDispatch;
      var pTaskResult: DTSTaskExecResult); safecall;
    procedure Set_Description(const pRetVal: WideString); safecall;
    procedure Set_Name(const pRetVal: WideString); safecall;
  public
   property Name : WideString read Get_Name write Set_Name;
    property Description : WideString read Get_Description write
Set_Description;
    property Properties : Properties read Get_Properties;
  end;

Quote
"Vadik" <vad...@smarteam.com> wrote in message news:3a115212_2@dnews...
> I'm trying create DTS CustomTask in Delphi without inheritance from
> CustomTask interface like in Microsoft Visual C++ examples that shipped
with
> SQL Server. The problem is that after registration of my task in SQL
Package
> Designer I can't add the task to the new package - there is Package Error
> "No such interface supported".

> Can anyone please suggest a way to solve this problem ?

> Thank You.

> Vadik

Re:Creation of DTS CustomTask in Delphi


Thank You.

I have another question - all examples of CustomTasks return NULL (nil) on
Get_Properties function. I believe that SQL DTS Designer just use the
IDispatch interface of the CustomTask to retrieve the properties of the task
in default "grid" format. It didn't work for me in Delphi (I don't want to
implement CustomTaskUI).

Can You help me?

Vadik

Quote
"Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message

news:8utbts$pai5@bornews.inprise.com...
Quote
> You need to do a fair amount of work to get this to work but here is an
> interface def for a custom task that works, but does nothing other than a
> show message in the execute method. If you want UI then you need to add a
> reference to CustomTaskUI in the class definition.

> -Euan

> unit NoInt;

> interface

> uses
>   ComObj, ActiveX, CTask3_TLB, StdVcl, DTS_TLB;

> type
>   TNoInt = class(TAutoObject, CustomTask)
>   private
>    FName : WideString;
>     FDescription : WideString;
>   protected
>     function Get_Description: WideString; safecall;
>     function Get_Name: WideString; safecall;
>     function Get_Properties: Properties; safecall;
>     procedure Execute(const pPackage, pPackageEvents, pPackageLog:
> IDispatch;
>       var pTaskResult: DTSTaskExecResult); safecall;
>     procedure Set_Description(const pRetVal: WideString); safecall;
>     procedure Set_Name(const pRetVal: WideString); safecall;
>   public
>    property Name : WideString read Get_Name write Set_Name;
>     property Description : WideString read Get_Description write
> Set_Description;
>     property Properties : Properties read Get_Properties;
>   end;

> "Vadik" <vad...@smarteam.com> wrote in message news:3a115212_2@dnews...
> > I'm trying create DTS CustomTask in Delphi without inheritance from
> > CustomTask interface like in Microsoft Visual C++ examples that shipped
> with
> > SQL Server. The problem is that after registration of my task in SQL
> Package
> > Designer I can't add the task to the new package - there is Package
Error
> > "No such interface supported".

> > Can anyone please suggest a way to solve this problem ?

> > Thank You.

> > Vadik

Re:Creation of DTS CustomTask in Delphi


If you return null from the get properties then DTS will use its internal
default properties provider. If you do not return nil then you need to
implement the DTS.PropertiesProvider which requires a propbag
implementation. You basically have to read and write all the properties
yourself.

The Designer uses the type library for the COM object to get the
properties(to see the different behavior here try inheriting from TComObject
and TAutoObject). But something goes wrong, by using the debug VCL and the
CPU view its possible to see that between calls to ITypeInfo  to query for
the properties and IDispatch.Invoke to access them the memory ends up
garbled so in DTS you see either no rows in the grid or rows with no values.

Its annoying and there must be a trick as all you have to do to get this to
work in VB is declare the variables as public members of the class and DTS
will pick the info up.

So you have a couple of choices, implement the properties provider or
implement DTS.CustomTaskUI. for the latter all you need to do is stub out
all the methods except New and Edit wher you basically do a ShowModal on a
form.

When I get a version that supports all the exentisibility interfaces I'll
post it here and to codecentral, I pretty much have a custom transform
working as well, it just needs tidied up.

-Euan

Quote
"Vadik" <vad...@smarteam.com> wrote in message news:3a13f2a7_2@dnews...
> Thank You.

> I have another question - all examples of CustomTasks return NULL (nil) on
> Get_Properties function. I believe that SQL DTS Designer just use the
> IDispatch interface of the CustomTask to retrieve the properties of the
task
> in default "grid" format. It didn't work for me in Delphi (I don't want to
> implement CustomTaskUI).

> Can You help me?

> Vadik

> "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> news:8utbts$pai5@bornews.inprise.com...
> > You need to do a fair amount of work to get this to work but here is an
> > interface def for a custom task that works, but does nothing other than
a
> > show message in the execute method. If you want UI then you need to add
a
> > reference to CustomTaskUI in the class definition.

> > -Euan

> > unit NoInt;

> > interface

> > uses
> >   ComObj, ActiveX, CTask3_TLB, StdVcl, DTS_TLB;

> > type
> >   TNoInt = class(TAutoObject, CustomTask)
> >   private
> >    FName : WideString;
> >     FDescription : WideString;
> >   protected
> >     function Get_Description: WideString; safecall;
> >     function Get_Name: WideString; safecall;
> >     function Get_Properties: Properties; safecall;
> >     procedure Execute(const pPackage, pPackageEvents, pPackageLog:
> > IDispatch;
> >       var pTaskResult: DTSTaskExecResult); safecall;
> >     procedure Set_Description(const pRetVal: WideString); safecall;
> >     procedure Set_Name(const pRetVal: WideString); safecall;
> >   public
> >    property Name : WideString read Get_Name write Set_Name;
> >     property Description : WideString read Get_Description write
> > Set_Description;
> >     property Properties : Properties read Get_Properties;
> >   end;

> > "Vadik" <vad...@smarteam.com> wrote in message news:3a115212_2@dnews...
> > > I'm trying create DTS CustomTask in Delphi without inheritance from
> > > CustomTask interface like in Microsoft Visual C++ examples that
shipped
> > with
> > > SQL Server. The problem is that after registration of my task in SQL
> > Package
> > > Designer I can't add the task to the new package - there is Package
> Error
> > > "No such interface supported".

> > > Can anyone please suggest a way to solve this problem ?

> > > Thank You.

> > > Vadik

Re:Creation of DTS CustomTask in Delphi


Thank You again.

I chose to implement CustomTaskUI. Also I implemented PersistPropertyBag
Interface couse I have to save some objects. So, as was expected,  a new
problem appeared - for some reason DTS Designer call to Load method of
PersistPropertyBag Interface twice. Indeed, it just destroy the first loaded
task and then create the same task again. I think that it don't call AddRef
enough times (or it call Release more times then needed). The problem exists
in VB too. I put a dump MsgBox to Load method and received the message
twice.

Do You now how to solve this problem?

Vadik.

Quote
"Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message

news:8v2o0o$5jn1@bornews.inprise.com...
Quote
> If you return null from the get properties then DTS will use its internal
> default properties provider. If you do not return nil then you need to
> implement the DTS.PropertiesProvider which requires a propbag
> implementation. You basically have to read and write all the properties
> yourself.

> The Designer uses the type library for the COM object to get the
> properties(to see the different behavior here try inheriting from
TComObject
> and TAutoObject). But something goes wrong, by using the debug VCL and the
> CPU view its possible to see that between calls to ITypeInfo  to query for
> the properties and IDispatch.Invoke to access them the memory ends up
> garbled so in DTS you see either no rows in the grid or rows with no
values.

> Its annoying and there must be a trick as all you have to do to get this
to
> work in VB is declare the variables as public members of the class and DTS
> will pick the info up.

> So you have a couple of choices, implement the properties provider or
> implement DTS.CustomTaskUI. for the latter all you need to do is stub out
> all the methods except New and Edit wher you basically do a ShowModal on a
> form.

> When I get a version that supports all the exentisibility interfaces I'll
> post it here and to codecentral, I pretty much have a custom transform
> working as well, it just needs tidied up.

> -Euan

> "Vadik" <vad...@smarteam.com> wrote in message news:3a13f2a7_2@dnews...
> > Thank You.

> > I have another question - all examples of CustomTasks return NULL (nil)
on
> > Get_Properties function. I believe that SQL DTS Designer just use the
> > IDispatch interface of the CustomTask to retrieve the properties of the
> task
> > in default "grid" format. It didn't work for me in Delphi (I don't want
to
> > implement CustomTaskUI).

> > Can You help me?

> > Vadik

> > "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> > news:8utbts$pai5@bornews.inprise.com...
> > > You need to do a fair amount of work to get this to work but here is
an
> > > interface def for a custom task that works, but does nothing other
than
> a
> > > show message in the execute method. If you want UI then you need to
add
> a
> > > reference to CustomTaskUI in the class definition.

> > > -Euan

> > > unit NoInt;

> > > interface

> > > uses
> > >   ComObj, ActiveX, CTask3_TLB, StdVcl, DTS_TLB;

> > > type
> > >   TNoInt = class(TAutoObject, CustomTask)
> > >   private
> > >    FName : WideString;
> > >     FDescription : WideString;
> > >   protected
> > >     function Get_Description: WideString; safecall;
> > >     function Get_Name: WideString; safecall;
> > >     function Get_Properties: Properties; safecall;
> > >     procedure Execute(const pPackage, pPackageEvents, pPackageLog:
> > > IDispatch;
> > >       var pTaskResult: DTSTaskExecResult); safecall;
> > >     procedure Set_Description(const pRetVal: WideString); safecall;
> > >     procedure Set_Name(const pRetVal: WideString); safecall;
> > >   public
> > >    property Name : WideString read Get_Name write Set_Name;
> > >     property Description : WideString read Get_Description write
> > > Set_Description;
> > >     property Properties : Properties read Get_Properties;
> > >   end;

> > > "Vadik" <vad...@smarteam.com> wrote in message

news:3a115212_2@dnews...

- Show quoted text -

Quote
> > > > I'm trying create DTS CustomTask in Delphi without inheritance from
> > > > CustomTask interface like in Microsoft Visual C++ examples that
> shipped
> > > with
> > > > SQL Server. The problem is that after registration of my task in SQL
> > > Package
> > > > Designer I can't add the task to the new package - there is Package
> > Error
> > > > "No such interface supported".

> > > > Can anyone please suggest a way to solve this problem ?

> > > > Thank You.

> > > > Vadik

Re:Creation of DTS CustomTask in Delphi


The designer will actually call the load at least twice.

The first twice it is loaded are to do with loading the object model and
then loading the design surface itself.

You may well see the load being called multiple more times if you build a
COM object that supports both threading models. As DTS is free threaded it
can execute a task on any thread, so DTS marshalls all of the tasks info
across to worker threads for execution, and then marshalls the results back.

There are KB's on this stuff but they really do not tell you more than I
just did.

The only problem you may have now is if you are using SQL2000 and you want
to use the disconnected feature, not sure if the propbag stuff works with
that.

-Euan

Quote
"Vadik" <vad...@smarteam.com> wrote in message news:3a17e4d6_2@dnews...
> Thank You again.

> I chose to implement CustomTaskUI. Also I implemented PersistPropertyBag
> Interface couse I have to save some objects. So, as was expected,  a new
> problem appeared - for some reason DTS Designer call to Load method of
> PersistPropertyBag Interface twice. Indeed, it just destroy the first
loaded
> task and then create the same task again. I think that it don't call
AddRef
> enough times (or it call Release more times then needed). The problem
exists
> in VB too. I put a dump MsgBox to Load method and received the message
> twice.

> Do You now how to solve this problem?

> Vadik.

> "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> news:8v2o0o$5jn1@bornews.inprise.com...
> > If you return null from the get properties then DTS will use its
internal
> > default properties provider. If you do not return nil then you need to
> > implement the DTS.PropertiesProvider which requires a propbag
> > implementation. You basically have to read and write all the properties
> > yourself.

> > The Designer uses the type library for the COM object to get the
> > properties(to see the different behavior here try inheriting from
> TComObject
> > and TAutoObject). But something goes wrong, by using the debug VCL and
the
> > CPU view its possible to see that between calls to ITypeInfo  to query
for
> > the properties and IDispatch.Invoke to access them the memory ends up
> > garbled so in DTS you see either no rows in the grid or rows with no
> values.

> > Its annoying and there must be a trick as all you have to do to get this
> to
> > work in VB is declare the variables as public members of the class and
DTS
> > will pick the info up.

> > So you have a couple of choices, implement the properties provider or
> > implement DTS.CustomTaskUI. for the latter all you need to do is stub
out
> > all the methods except New and Edit wher you basically do a ShowModal on
a
> > form.

> > When I get a version that supports all the exentisibility interfaces
I'll
> > post it here and to codecentral, I pretty much have a custom transform
> > working as well, it just needs tidied up.

> > -Euan

> > "Vadik" <vad...@smarteam.com> wrote in message news:3a13f2a7_2@dnews...
> > > Thank You.

> > > I have another question - all examples of CustomTasks return NULL
(nil)
> on
> > > Get_Properties function. I believe that SQL DTS Designer just use the
> > > IDispatch interface of the CustomTask to retrieve the properties of
the
> > task
> > > in default "grid" format. It didn't work for me in Delphi (I don't
want
> to
> > > implement CustomTaskUI).

> > > Can You help me?

> > > Vadik

> > > "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> > > news:8utbts$pai5@bornews.inprise.com...
> > > > You need to do a fair amount of work to get this to work but here is
> an
> > > > interface def for a custom task that works, but does nothing other
> than
> > a
> > > > show message in the execute method. If you want UI then you need to
> add
> > a
> > > > reference to CustomTaskUI in the class definition.

> > > > -Euan

> > > > unit NoInt;

> > > > interface

> > > > uses
> > > >   ComObj, ActiveX, CTask3_TLB, StdVcl, DTS_TLB;

> > > > type
> > > >   TNoInt = class(TAutoObject, CustomTask)
> > > >   private
> > > >    FName : WideString;
> > > >     FDescription : WideString;
> > > >   protected
> > > >     function Get_Description: WideString; safecall;
> > > >     function Get_Name: WideString; safecall;
> > > >     function Get_Properties: Properties; safecall;
> > > >     procedure Execute(const pPackage, pPackageEvents, pPackageLog:
> > > > IDispatch;
> > > >       var pTaskResult: DTSTaskExecResult); safecall;
> > > >     procedure Set_Description(const pRetVal: WideString); safecall;
> > > >     procedure Set_Name(const pRetVal: WideString); safecall;
> > > >   public
> > > >    property Name : WideString read Get_Name write Set_Name;
> > > >     property Description : WideString read Get_Description write
> > > > Set_Description;
> > > >     property Properties : Properties read Get_Properties;
> > > >   end;

> > > > "Vadik" <vad...@smarteam.com> wrote in message
> news:3a115212_2@dnews...
> > > > > I'm trying create DTS CustomTask in Delphi without inheritance
from
> > > > > CustomTask interface like in Microsoft Visual C++ examples that
> > shipped
> > > > with
> > > > > SQL Server. The problem is that after registration of my task in
SQL
> > > > Package
> > > > > Designer I can't add the task to the new package - there is
Package
> > > Error
> > > > > "No such interface supported".

> > > > > Can anyone please suggest a way to solve this problem ?

> > > > > Thank You.

> > > > > Vadik

Other Threads