Post reply

When exporting data from SQL Server to Excel file getting error message.

  • July 29, 2024 at 7:26 pm

    #4440818

    I am working with a SSIS package that loads .CSV files from a network drive to the database, processes the data, then it is supposed to export the data from 5 tables to 5 tabs in the same excel workbook on a network drive. I am constantly getting the following error message, DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. I am having to run the task in the package one at a time to get the data to extract.

    I have tried several things to get the package to release the connection to the Excel file before the next table is extracted. My current iteration of the package is to put in a wait task, then a script object in that is suppose to release the connection to the file and then another wait task in hopes that the connection will be released, sometimes it works and sometimes it doesn't.

    Any suggestions on how I can get the package to release the connection to the files between task would be greatly appreciated. I have searched and searched to try and find something but it has be unsuccessful.

    Thanks for any help you can give.

  • July 30, 2024 at 7:42 am

    #4440922

    Are you doing the export as five data flows?

    Are they running in series or in parallel?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 30, 2024 at 12:15 pm

    #4440982

    Hi Phil,

    I am running the exports in series and in separate 5 data flows.

  • July 30, 2024 at 3:26 pm

    #4441052

    BradWilk wrote:

    Hi Phil, I am running the exports in series and in separate 5 data flows.

    OK good start. Can you post a screenshot of your control flow? Redact the text as necessary, of course.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 30, 2024 at 3:48 pm

    #4441065

    Phil,

    attached is the screen shot

    Attachments:
    You must be logged in to view attached files.

  • August 2, 2024 at 12:37 pm

    #4441935

    Sorry for the slow reply.

    Don't know whether it will help, but might be worth a try. On the Excel connection properties, try setting 'RetainSameConnection' to true.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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