Thursday, March 29, 2012

DTS from MS SQL to Excel Spreadsheet Issue

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. Im using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named Results. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. Ive Googled this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jimright click on the spreadsheet and go to properties. what do you see?

the application dev team spent a week tossing something similar to this with Access for one their internal processes. I pointed it out a couple minutes after they asked me. I spent an hour laughing at my team of geniuses.

I am a joy to work with.|||Thanks for the idea. I thought of the read only flag as soon as I saw the read only error in the error log. Unfortunately that wasnt it. The issue was within the DTS package. If you open up the connection properties and click the top option to New Connection in an attempt to re-name the object, you actually create a new object with a new name leaving the older one in tacked but hidden in the background. This can only be seen if you go into Disconnected Edit under connections. There was an object names Connection1 and Connection2. These old connection objects where pointing to the development environment where the functional ID that was used to run the DTS did not have access to. Oops

Again thanks for the idea.

Jimsql

No comments:

Post a Comment