DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Check Pkg Errors
EXEC @.ret=spDisplayPkgErrors @.oPKG

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

RETURN @.ret
GO

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTS 2000

Hi,

I am having a dts 2000 package which is accessing oracle source and loading data into sql server .The package runs fine when ran individually or from designer.

But when it is scheduled into a job, the job is being shown as ran successfully whereas the package is not run or for sure the data is not loaded.

I looked into microsoft articles regarding this problem and found that this job will be run under the profile who started the SQL Server Agent.There is another job which is running properly whose owner is xxx. So i changed the owner of my job into xxx.

But still the same condition is prevailing, job is being shown as ran successfully while the data is not loaded.

Kindly help me on this.

Thanks and Regards

Arobind

Are you executing this DTS package via SSIS? If not, you're in the wrong forum as this is an SSIS forum. The DTS forum can be found here: http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg

If in SSIS, make sure that you set the FailParentOnFailure to true.|||

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

|||

Then your problem is within SQL Server Agent...the Job is being reported as Successfully run but the results indicate it actully does not run....

|||

Arobind Balakrishnan wrote:

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

Again, is this an SSIS package, or an older DTS package? This forum can help you only if it's an SSIS package.|||

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

|||

Arobind Balakrishnan wrote:

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

Nope, I'm not sure that they even had MSDN forums when DTS was released. So they created a USENET newsgroup for DTS, which is the link I provided above. They felt it was important to segregate the two products.

I could be wrong on some of those points though.