Post reply

Error while extracting data to Excel Destination

  • August 10, 2019 at 3:36 pm

    #3670492

    Hi All,

    I have a simple package in which I am extracting data from SQL table to excel file ( jut one DFT ). As I want to deploy this package to Production server, I have created config file and in that config file I have taken the connection string details on SQL source as well as Excel connection manager. I have also made the delay validation = True for Excel Connection Manager as well as for DFT.

    In the Production server, I have copied .dtsx and .config file, and I have changed the SQL server details and Excel destination path as per the Production environment. I have also placed a dummy excel file in the path mentioned as Excel destination.

    However, when I executed this package through a .bat file, I am getting error as Excel destination connection is not valid.

    What could be the reason for this error?

    Regards,

    MC

    Thanks & Regards,
    MC

  • August 11, 2019 at 4:10 pm

    #3670549

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 11, 2019 at 7:32 pm

    #3670555

    Please provide some additional background information:

    1. Why did you decide not to use SSISDB environments to handle those parameters which vary between Dev/QA/Production? They make config files redundant.
    2. Why have you chosen not to use SQL Agent to automate the running of the package?

  • August 12, 2019 at 6:04 am

    #3670599

    Hi Parkin,

    Definitely I will be using SQL Agent / Control M to automate the Job. At present these Production environments ( SQL , SSIS , Shared drive) are new severs and I am validating whether the end to end load is working fine.  I am able to read .csv file from shared folder and write into SQL, and in the same way I am able to extract data from SQL and write back to .csv. But when in comes to excel, I am facing the mentioned issue.

    Ultimately when the server validation is done and when it is released to wider audience, they will be using SQL Agent / Control M to trigger the batch file and that batch file will trigger the .dtsx and it will be using the .config file ( this is the process we follow).

     

     

    Thanks & Regards,
    MC

Viewing 4 posts - 1 through 3 (of 3 total)

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