Excel DTS Package from Proc

Question

Post reply

Excel DTS Package from Proc

KudahX

Old Hand

Points: 389
More actions
October 11, 2003 at 6:12 am

#106247

Hi,
I have created a procedure to run a DTS package from a proc, using the code below. It workes as long as my source/destination is a TXT file. As soon as my source/destination is an EXCEL workbook, it doesn't work, even though I can execute the Excel Package from SQL with no problems. I get no errors. Any ideas?
Thanks for your help
*******Code**********
CREATE Procedure sp_RunDTSPackage_Util@PackageName VARCHAR(100), @status VARCHAR(100) OUTPUT
AS
DECLARE @hr INT, @oPKG INT
DECLARE @ServerName SQL_VARIANT
DECLARE @SQLSTRING VARCHAR(100)
SET @ServerName = SERVERPROPERTY('ServerName')
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_DisplayOAErrorInfo @oPKG, @hr
SET @status = 'Create Package object failed'
RETURN
END
ELSE
BEGIN
SET @status = 'Success'
END
--Loading the Package:
--DTSSQLServerStorageFlags :
--DTSSQLStgFlag_Default = 0
--DTSSQLStgFlag_UseTrustedConnection = 256
SET @SQLSTRING = 'LoadFromSQLServer(' + CAST(@ServerName AS VARCHAR) + ', , , 256 , , , , ' + @PackageName + ')'
EXEC @hr = sp_OAMethod @oPKG, @SQLSTRING, NULL
IF @hr <> 0
BEGIN
EXEC sp_DisplayOAErrorInfo @oPKG, @hr
SET @status = 'Load Package failed'
RETURN
END
ELSE
BEGIN
SET @status = 'Success'
END
--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
SET @status = 'Execute failed'
EXEC sp_DisplayOAErrorInfo @oPKG, @hr
RETURN
END
ELSE
BEGIN
SET @status = 'Success'
END
--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_DisplayOAErrorInfo @oPKG, @hr
SET @status = 'Destroy Package failed'
END
ELSE
BEGIN
SET @status = 'Success'
END

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

What sort of error messages are you getting?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

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

KudahX Old Hand Points: 389 More actions · Answer 2

quote:
What sort of error messages are you getting?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

I get no error message, the hr variable returns a zero.

mahesh balshetwar SSC Veteran Points: 273 More actions · Answer 3

Hello,

I have problem of making connection to the Excel file with the following connection string...

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Test.xls;Extended Properties=Excel 8.0;"

In the above connection string version is hadwired and so I changed it to....

.Provider = "MSDASQL"

.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strWebFilePath & "; ReadOnly=False;"

but I am still facing the problem. The following error I get when I try to make ADO connection to the Excel file....

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] External table is not in the expected format.

Can anybody please help me in finding the solution please.

Regards,

Mahesh

philcart SSC-Forever Points: 47796 More actions · Answer 4

KudahX

Can you schedule the DTS package to execute successfully? It could be a permissions problem. When you manually execute the package it is using your security credentials. Executing via scheduled job or the sp_OA procedures will use the security credentials for the SQL Server account.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

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

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

quote:
Hello,
I have problem of making connection to the Excel file with the following connection string...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Test.xls;Extended Properties=Excel 8.0;"
In the above connection string version is hadwired and so I changed it to....
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strWebFilePath & "; ReadOnly=False;"
but I am still facing the problem. The following error I get when I try to make ADO connection to the Excel file....
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Excel Driver] External table is not in the expected format.
Can anybody please help me in finding the solution please.
Regards,
Mahesh

Mahesh,

The error message means that the Excel file is not int he correct format for the driver you are using. Which version of Excel was the file created in?

Also, in future please start a new topic for a new problem.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

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

nhiggs SSC Eights! Points: 944 More actions · Answer 6

I had a lot of trouble with DTS packages, until i tried a different "Loading" method. Using the method you use, I was unable to get the package to run, but when i changed it to:

EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer', NULL, @ServerName = '(local)', @Flags = 256, @PackageName = @sDTSPackageName

I didn't have a problem.

Hope this works for you too.

Nigel

Nigel H.
Infrastructure
Lockheed Martin Australia

amcleod SSC Journeyman Points: 91 More actions · Answer 7

I had a similar problem, but went another route. I placed the DTS package execution in an unscheduled job, the used system procedures to start the job.