Error while extracting data to Excel Destination

  • 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

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

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

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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