DTS with Excel Output

Question

DTS with Excel Output

Kimberly Evans

Valued Member

Points: 52
More actions
December 1, 2005 at 11:13 am

#110394

I have a dts package that I am using to generate an excel output file. The problem I am encountering is that I want my global variables to give the excel spreadsheet a unique name each time based on data, but when the package is called from a sql agent it fails. Basically the error comes down to Excel not knowing what to call the worksheet in the workbook. Everything I have tried to get around this results in the output file not being named according to what my global variables pull back. How do I overcome this?

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

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

Gagandeep Singh SSCommitted Points: 1513 More actions · Answer 1

U said - "when the package is called from a sql agent it fails." Does it succeed when started manually without scheduling via Agent?

Not sure what u r doing. But u can try xp_cmdshell and create an excel file with a unique name and then do whatever u want to the file.

Kimberly Evans Valued Member Points: 52 More actions · Answer 2

I have attempted the xp_cmdshell method with no positive results as of yet. Using this method I get "DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)"

Interestingly if I run the package manually it will tell me "Microsoft Jet Engine could not find the table New_Table" (new table is what I have assigned as the worksheet name for Excel). Hope that makes sense.

Kimberly Evans Valued Member Points: 52 More actions · Answer 3

Ok, got it so it works when manually executing the dts package now, but executing it via xp_cmdshell still fails with the same error given above. Any thoughts?

Gagandeep Singh SSCommitted Points: 1513 More actions · Answer 4

Run the TSQL (xp_cmdshell) script inside the package in Query Analyzer and find out the error. Use the same login which is used by the package to run. Le me know what is the exact error?

Kimberly Evans Valued Member Points: 52 More actions · Answer 5

This is the error I get from xp_cmdshell.

DTSRun OnError: DTSStep_DTSDynamicPropertiesTask_1, Error = -2147467259 (80004005)

Error string: Failure creating file.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003436

Error Detail Records:

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

Error string: Failure creating file.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003436

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

Are you trying to do this to a local disk or to a UNC/share ?