Hi All,

I have created one DTS Packages in with 4 Global Variables. I am able to
Execute the Package from with in a Stored procedure. Then I have Copied the
DTS Packages to another server (using save as - structured storage ...) and
loaded the packages to target using open package. I was able to restore in
the target system BUT UNABLE to Execute the package from with in the Stored
Procedure.

I do not get any Error too.

The Code which CALLS the Package is:

===========================================
DECLARE
@SERVERNAME VARCHAR(100),
@SOURCEID VARCHAR(4),
@FILEPATH VARCHAR(250),
@FILENAME VARCHAR(40),
@FILEID INT,
@ERR_NUM INT


DECLARE @DTSNAME VARCHAR(30),
@RES INT,
@oPKG INT,
@CMD VARCHAR(1000),
@GVName VARCHAR(255),
@GVValue VARCHAR(255)

SELECT @SERVERNAME = 'RADSOFTECH-113', @SOURCEID = 'PA51',
@FILEPATH = 'D:\Database\PA_Files\BRISTOL_COMMUNITY', @FILENAME = 'BNC
072303.dbf'

SELECT @ERR_NUM = 0, @DTSNAME = 'BRISTOL_COMMUNITY'

-- Create Package Object.
EXEC @RES = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @RES <> 0
BEGIN -- Create Package object failed
SET @ERR_NUM = 1
print 'Create Package object failed'
END

-- Load the Data Transformation Services (DTS) Package.
SET @CMD = 'LoadFromSQLServer("' + @SERVERNAME +'", "", "", 256, , , , "' +
@DTSNAME + '")'

EXEC @RES = sp_OAMethod @oPKG, @CMD, NULL
IF @RES <> 0
BEGIN -- Load From SQL Server failed.
SET @ERR_NUM = 1
print @oPKG
print @CMD
print @SERVERNAME
print @DTSNAME
print 'Load From SQL Server failed'
END

-- Set and Assign Global Variables.
SET @GVName = 'gSourceFileID'
SET @GVValue = @FILEID
SET @CMD = 'GlobalVariables("' + @GVName + '").Value'

EXEC @RES = sp_OASetProperty @oPKG, @CMD, @GVValue
IF @RES <> 0 -- Global Variable Assignment failed.
BEGIN
SET @ERR_NUM = 1
print 'lobal Variable Assignment failed'
END
-- Set and Assign Global Variables.
SET @GVName = 'gPACode'
SET @GVValue = @SOURCEID
SET @CMD = 'GlobalVariables("' + @GVName + '").Value'

EXEC @RES = sp_OASetProperty @oPKG, @CMD, @GVValue
IF @RES <> 0 -- Global Variable Assignment failed.
BEGIN
SET @ERR_NUM = 1
print 'lobal Variable Assignment failed'
END
-- Set and Assign Global Variables.
SET @GVName = 'gSourceFilePath'
SET @GVValue = @FILEPATH
SET @CMD = 'GlobalVariables("' + @GVName + '").Value'

EXEC @RES = sp_OASetProperty @oPKG, @CMD, @GVValue
IF @RES <> 0 -- Global Variable Assignment failed.
BEGIN
SET @ERR_NUM = 1
print 'lobal Variable Assignment failed'
END
-- Set and Assign Global Variables.
SET @GVName = 'gSourceFileName'
SET @GVValue = @FILENAME
SET @CMD = 'GlobalVariables("' + @GVName + '").Value'

EXEC @RES = sp_OASetProperty @oPKG, @CMD, @GVValue
IF @RES <> 0 -- Global Variable Assignment failed.
BEGIN
SET @ERR_NUM = 1
print 'lobal Variable Assignment failed'
END

-- Execute the Package.
EXEC @RES = sp_OAMethod @oPKG, 'Execute'
IF @RES <> 0
BEGIN -- Execute failed.
SET @ERR_NUM = -99
print 'Execute failed'
END

-- Unitialize the Package.
EXEC @RES = sp_OAMethod @oPKG, 'UnInitialize'
IF @RES <> 0
BEGIN -- UnInitialize failed.
SET @ERR_NUM = 1
print 'UnInitialize failed.'
END

-- Clean Up.
EXEC @RES = sp_OADestroy @oPKG
IF @RES <> 0
BEGIN -- Cleanup failed
SET @ERR_NUM = 1
print 'Cleanup failed'
END
================================================

I am not able to understand why it is not executing the package in that
system (the .dbf file and server both are in the same system) while I am
able to execute the stored procedure and able to run the package.

Is there any thing which need to be register after i move the pacakge to
another system.

Thanks
Prabhat

Hi All Again... This is a ADDITION to above Message: Does the DTS require
any Disconnected Edit before I move to another system?


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

Depends what you mean by disconnected Edit.

The package will be exactly the same as before looking in the exact same
places as before.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

Then Why I am able to execute the package from with in the Stored procedure
in my system but not in the system where i moved the packages?

Thanks
Prabhat

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

What if you have a text file location specified that no longer exists from
the destination server point of view?
What if you are using a custom DLL that is not registered on the other
server?

It is my expereince that sp_OA* procs are not very good at returning error
messages even when there are some. How about you run the package on the
server manually and make sure all is well.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote