Why am I having such a hard time in my Data Flow Task to copy my OLE DB Source Data to a Copied Template

Bobby P 231 Reputation points
2022-07-05T21:16:10.95+00:00

Here's what I'm trying to do

  • We have a Macro-Enabled Template that has the formatting necessary for a Daily Output to an Excel Spreadsheet
  • We copy the Macro-Enabled Template that has the formatting necessary using a File System Task and Naming the File appropriately with today's date
  • We then have a Data Flow Task which runs the OLE DB Source SQL Server Stored Procedure
  • We are trying in vain to send this to an Excel Destination

Everything seems to be A-Ok UNTIL I try to change the Excel Destination and its Excel Connection Manager Properties and Expression ExcelFilePath to the Derived Variable which will contain the Daily File Date Suffix in the File Name.

I have DelayVaildation as True on the Excel Connection Manager Property.
I have the ValidateExternalMetadata as False on the Excel Destination Property.

Everytime I change the Excel Connection Manager's Properties and Expression to ExcelFilePath referencing the Derived Variable which contains the Daily File Date Suffix in the File Name, we get an ugly error.

Exception from HRESULT: 0xC020801c
Error at FileName_Daily [Connection manager: "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occureed. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot update. Database or object is read-only.".

Error at Data Flow Task [Excel Destination [241]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The acquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be more messages posted before this with more information on why the AcquireConnection method call failed.

I know I have done this before where the Excel Destination and its Properties on its Excel Connection Manager has an Expression to ExcelFilePath to be the derived Variable with the Daily File Date.

What am I doing wrong here?!?!?!?!?

Any help would be GREATLY appreciated!

Thanks in advance for for your review and am hopeful for a quality reply.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,643 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 40,511 Reputation points
    2022-07-06T06:42:49.603+00:00

    Hi @Bobby P ,

    Cannot update. Database or object is read-only.

    It seems like a Permission Issue. Make sure the file is not read-only and you have the permission to read and write in the file.

    Or try to add IMEX=1 at the end of the connection query.

    Refer to What is the default value of IMEX in OLEDB.

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.