firing a dts package's execution

  • Thread starter Thread starter someone
  • Start date Start date
S

someone

Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?

Thanks in advance.
 
someone said:
Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?
Click to expand...

I tried this a month back. There are a number of solutions but I found
that there were alot of security/permissions issues that prevented me
from triggering a DTS package from an application. The most promising
solution I thought was the triggering of a job from within a stored
procedure using sp_start_job. I finally got the permissions worked out
to execute the job but then the job wouldn't execute the DTS.

Ultimately I gave up and went to a scheduled job solution that ran every
10 minutes. I'm sure it's possible to trigger a DTS but my
organization's security policies (which are there for a reason) made
that difficult to accomplish.

Anyway, google for sp_start_job, you should get alot of information that
will help you out.
 
someone said:
Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?

Thanks in advance.
Click to expand...

Some combination of xp_cmdshell and DTSRUN would probably do it. Both
are documented in BOL.
 
someone said:
Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?

Thanks in advance.
Click to expand...

I've just added something to my VB.Net app something that should also
work from VB 6, and anything that can use COM. Add a reference to the
Microsoft DTSPackage Object Library. Then it's a couple of lines like:

Dim pkg As New DTS.Package2
pkg.LoadFromSQLServer("SERVERNAME", "", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, "",
"", "", "PACKAGENAME", "")
AddHandler pkg.OnError, AddressOf RecordExtractError
pkg.Execute()

If you need more/something else, it might help if you tell us what the
app is being developed in (incidentally I think the above can also be
written as a script file)
 
Back
Top