Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Location
    Sacramento, CA
    Posts
    6

    Unanswered: 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. I’m 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. I’ve Google’d 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.
    Jim

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    right 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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    Sacramento, CA
    Posts
    6

    Found issue

    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 wasn’t 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.

    Jim

  4. #4
    Join Date
    Oct 2012
    Posts
    1
    I found the following link to be useful:

    Protean » Blog Archive » Excel - a read-only database?

    This link says,

    "Check your connectionstring properties in your excel connection manager. If you have the extended property “IMEX=1″, then you will be opening your workbook in import mode and therefore you will be unable to modify the design. Remove the setting and you may find that your Execute SQL task now works."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •