RunDTSPackage

Question

RunDTSPackage

Chuck Waters

SSC Rookie

Points: 27
More actions
August 26, 2007 at 4:28 pm

#118088

The situation:
We have 2 servers, one for web and one that runs SQL Server 2000. Last week our server that runs SQL Server crashed and we had to reinstall the OS and all. We recovered from this and lost a few days of data but nothing severe. The problem facing me is that when calling this code we receive the error '*** Load Package failed' which can be traced to the SP below.
Basically what we do is to upload an excel sheet that contains 2 columns to our webserver. This excel doc is then renamed to the data&time and should be imported into a table in my database but it never gets that far.
Things I have done so far:
At first we were receiving the error: Could not find stored procedure 'sp_displayoaerrorinfo' but I tracked it down to Books Online and it is now loaded along with sp_hexadecimal. I most recently gave the "sql_user" (defined in the SP below) EXEC rights on all sp_OA* extended stored procs in the master database. Now I am stuck in the --Loading the package and I'm not sure where to look.
///////////////////////////////////////////////////////////////////////////
CREATE PROC sp___Admin_RunDTSPackage
@ExecPersonID int,
@PackageName varchar(100),
@Response varchar(100) OUTPUT
AS
DECLARE @hr int, --just a return code?
  @oPKG int, --just a return code?
  @LoadFromSQLServer varchar(200), --command to load DTS pkg
  @CRUDDetails varchar(100) --logging details
--The load from server command will differ depending on the server on which it is running.
--Rather than pass all these parameters in, i'll just provide the variations here, and we can
--comment out the unused ones on each server.
SET @LoadFromSQLServer = 'LoadFromSQLServer("ip_of_my_webserver", "sql_user", "sql_user_password", 0, , , , "' + @PackageName + '")'
--The following code was based on the sample given at the following URL
--http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1
--creating the package object:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    SET @Response = '*** Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END
--Loading the package:
EXEC @hr = sp_OAMethod @oPKG, @LoadFromSQLServer, NULL
IF @hr <> 0
BEGIN
    SET @Response = '*** Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END
--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
    SET @Response = '*** Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END
--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    SET @Response = '*** Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END
--END OF CODE based on example at http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1
--Log the execution
IF @Response IS NULL
SET @Response = 'Success'
SET @CRUDDetails = 'Run DTS pkg: ' + @PackageName + ' ::result msg.: ' + @Response
EXEC sp__CRUDLog_Insert
@CRUDObjectTypeID = 31, --DTS
@CRUDObjectID = 0, --object id not relevant in this case
@CRUDAction = 'E',
@CRUDDetails = @CRUDDetails,
@PersonID = @ExecPersonID
GO

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

SET @LoadFromSQLServer = 'LoadFromSQLServer("ip_of_my_webserver", "sql_user", "sql_user_password", 0, , , , "' + @PackageName + '")'

As you're loading from SQL Server, shouldn't the ip address be for your SQL Server not your web server?

--------------------
Colt 45 - the original point and click interface

Chuck Waters SSC Rookie Points: 27 More actions · Answer 2

philcart, that popped out to me as well and I changed it to the sql server address but I couldn't get as far as LOAD PACKAGE FAILED...so I'm almost positive that it needs to run to the web server. The excel document is uploaded to the web server and is then imported into a temporary table in my sql server on another server. Thanks for the reply!

philcart SSC-Forever Points: 47797 More actions · Answer 3

The LoadFromSQLServer method loads a DTS packages that resides in SQL Server into memory.

You will need the address of the SQL Server. I'd check that the package exists on the SQL Server. Maybe even try running the package manually.

--------------------
Colt 45 - the original point and click interface

Chuck Waters SSC Rookie Points: 27 More actions · Answer 4

Hmm, I know that the IP address worked for about 3 years before our sql server crashed and had to be reinstalled and the IP points to the web server and there is no instance of sql server on it.

When you say package.. what do you mean exactly? Can a package simply be the excel document that is used? And where within the sql server enterpise manager would I look for it?

philcart SSC-Forever Points: 47797 More actions · Answer 5

Chuck

Here are a few DTS articles that will hopefully bring you up to speed.

http://en.wikipedia.org/wiki/Data_Transformation_Services

http://www.sqlservercentral.com/columnists/rNageshwara/overviewofdtspackages.asp

http://www.devguru.com/features/tutorials/DTS/DTS1.html

--------------------
Colt 45 - the original point and click interface