DTS-Failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS-Failure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-20 : 07:40:55

Rajaraman writes "Hi All,
I have a DTS-Packages which runs and gets me the desired
result if i run it DTS-Desiner , but the same
Package fails when i run it from a Stored Proc. or as a
Job.
Could anyone advise me or suggest me how to proceed about
the same.

Thanks in Advance,
Raja

Herewith i am pasting the Stored Procedure Code i Used.
Pls. Have a look at it.
--------------------------------------------------------

create proc dbo.sp_executepackage_test
@pkgPwd varchar(255) = '' --package password
as

declare @hr int
declare @object int
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
print 'error in creating the package'+@hr
RETURN
end
Print 'Check for Object creation in hr-value '+@hr
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer
("AHO0SQLD"," "," ",256, , , ,"pw_load_dlr")',null

IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
Print 'error in creating the package'+@hr
RETURN
END

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO"

ramdas
Posting Yak Master

181 Posts

Posted - 2003-03-20 : 09:04:02

Hi,
When you run the DTS package from the designer it runs on your local machine with the previlages you have on that machine. When the DTS package is scheduled as job or run through a stored procedure, it is run on the sql server box with the credentials avialable on the sql server box. This could cause pks to fail especially if you are mappuing to a network drive for a data source et.al. So make sure that the account under which the job runs has the necessar previlages. Also look up the microsoft knowledge base on Scheduling DTS pkgs.

Bye
Ramdas

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources