DTSrun not working

[JeffMachine]

I've created a DTS package that exports data from a Excel spreadsheet into a sql table. It works fine when run from the Enterprise Manager. But it errors out when run from a stored procedure.

Stored Procedure:

CREATE PROCEDURE [run_haas_po_import] AS
exec master..xp_cmdshell 'dtsrun /S SQLSERVER /N "haas po import" /E '

Error:

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: Copy Data from Sheet1$ to [Data_41].[dbo].[haas_po_import] Step

DTSRun OnError: Copy Data from Sheet1$ to [Data_41].[dbo].[haas_po_import] Step, Error = -2147008507 (80074005)

Error string: Unspecified error



Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 700



Error Detail Records:



Error: -2147008507 (80074005); Provider Error: 0 (0)

Error string: Unspecified error



Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 700





Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54)

Error string: Failure creating file.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003436



DTSRun OnFinish: Copy Data from Sheet1$ to [Data_41].[dbo].[haas_po_import] Step

DTSRun: Package execution complete.


Any ideas? Thanks in advance ......

 

[AlexCuse]

I'd look at drive mappings first (your SQL Server might not have the same drives set up, or even be able to access the share that your file is stored on, depending on permissions). If it is coming from a mapped drive, try using the UNC path for the file location.

Hope this helps,

Alex



[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[JeffMachine]

Thanks for the prompt reply Alex, it got me to thinking.

Both servers have the same mapped drive to the excel file. The Enterprise Manager that I created the DTS script on and successfully ran is on a Terminal Server that our users use to access our ERP application (and this is from where they will be calling the stored procedure from). If I go to the actual SQL Server server, and open Enterprise Manager and try to run the DTS package, it doesn't work. I get this error:

Error Source : Microsoft OLE Provider for SQL Server

Error Description: The property 'Use Encryption for Data' is not supported. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

 

[AlexCuse]

sounds like your SQL Server doesn't have the appropriate permissions. Remember the package will run on the server when executed through a stored proc.

I'd try using a UNC path for the file location first, and if that doesn't work maybe try searching the web for the error message you got. Often the error messages from DTS are less than clear, until you find a posting by someone else who's had the exact same problem.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[JeffMachine]

It seems this message is related to the MDAC versions. The terminal server has 2.8 sp2 and the SQL server had something like 2.3. I created the dts script on the terminal server and apparently some previous versions of MDAC did not have a property called "Use Encrytion for Data".

I will test this out, but I need users to exit the system first so I can reboot after the MDAC upgrade. Dang users, always a pain in my arse.

 

[AlexCuse]

[LOL] updating the MDAC ought to do it then. Post back whether it works or not, so that anyone else who stumbles on this thread can tell what the solution was.

Good Luck!

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[JeffMachine]

Updating the MDAC fixed the "Use Encrytion for Data" error. But it still errored out because, I think, there is a permissions issue on the excel file even though I could access this file from the SQL server.

I got around this whole problem by just creating the DTS package directly in Enterprise Manager on the SQL server. And since this is where the package runs (when called by a job or by a command line) it eliminates all the problems I was getting by creating the DTS package on the SQL Enterprise Manager running on our terminal server.