running a DTS package from Query Analyzer

washaw · Mar 11, 2008

I have a DTS called myDTS wich has one input parameter called inputParam, I am trying to run it from query analyzer and wrote this query

Code:

EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
    print 'error create DTS.Package'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL, 
  @ServerName='myServer', @PackageName='myDTS', @Flags=256
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
END

what is wrong with the above code and how can excute the package with the input parameter?

Thanks

AlexCuse · Mar 11, 2008

Please don't cross-post.

You need to think about the DTS object model to do this.

From VB you'd use this to add a global variable =

Code:

objDTS.GlobalVariables.AddGlobalVariable("gvName", "gvValue)

So, I think you'd need to use sp_OAGetProperty to retrieve the GlobalVariables collection, and then use sp_OAMethod to execute the AddGlobalVariable method on this object.

Here's a start (not tested at all, but try adding it after what you've already got)

Code:

--store handle for GlobalVariables collection here
declare @object2 int

EXEC @hr = sp_OAGetProperty @object, 'GlobalVariables', @object2 OUTPUT
IF @hr <> 0
BEGIN
	PRINT 'Cant Get Global Vars'
END

EXEC @hr = sp_OAMethod @object2, 'AddGlobalVariable', @Name = 'myVarName', @Value = 'myVarValue'

IF @hr <> 0
BEGIN
	PRINT 'Cant Add Global Var'
END

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

washaw · Mar 11, 2008

THank you Alex

washaw · Mar 11, 2008

EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
@ServerName='myserver', @PackageName='mypackage', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
END

how do i use my windows login without mentioning my user name and password

(window login has DBO access)

THanks

AlexCuse · Mar 11, 2008

SQL Server 2000 doesn't really support impersonation. (SETUSER may work, but its' use is discouraged and to execute users need to be sysadmin or dbo)

Are you intending for this package to actually be executed by users from Query Analyzer, or from a different Client Application (of the home-grown variety)?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

running a DTS package from Query Analyzer

washaw

Programmer

AlexCuse

Programmer

washaw

Programmer

washaw

Programmer

AlexCuse

Programmer

Similar threads

Part and Inventory Search

Sponsor