Post reply

Export data into excel file from a DTS Package -- Problem!

  • December 30, 2005 at 12:16 pm

    #68638

    Good Morning all...

    Hope that everyone is doing great today!

    I want to export data into excel files from a DTS package. The problem I am having right now is these excel files must have headers which are bold and highlighted with different colors to indicate the importance of the data under them.  Whenever I export data into these excel files, the data have the same format as the headers which are bold and in colors.  I have a template with the headers formated as the way I want, and everytime before exporting data into these excel files, I use activeX to copy the headers from the template into the excel files. But the exported data have the same format as the headers which I don't want. Is there anyway to avoid this? I just want to have the column headers bold and highlighted not the data under them. Can we do this in activeX task?

    Thank you in advance for your help!

     

  • January 2, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • January 2, 2006 at 9:08 am

    #612305

    Maybe move the ActiveX task to after the export and "reformat" the sheets?

  • January 3, 2006 at 11:57 am

    #612459

    You do not need to use ActiveX, by using an Execute SQL Task to DROP and CREATE the worksheet, if it is an existing Spreadsheet, it will keep the formatting.  It is a little tricky, but doing these steps will allow you to reuse the same spreadsheet, with formatting.

    In my testing, I have found the best way to create this is to do the following.

    1. Using Wizard, perform an Export from SQL to Excel
    2. Choose Table, View or SQL Query to use, if doing as a Query, change "Results" to <NAME> of Worksheet desired (<NAME> is whatever you want it to be)
    3. Modify the Transformations, Check the Drop and Create Destination Table
    4. Save the Package (Do Not Run It)
    5. Open Package
    6. Go To Transformation Task and Open to edit
    7. Click on Destination (will get error message about not finding Object), click OK
    8. The Table Name will be <NAME>$, this is what you will change, click on the "Create..." Button
    9. Change `New Table` to <NAME>, Click OK
    10. Now Run Package, Click "Done" when finished
    11. Open Spreadsheet, reformat as desired, CLOSE when finished
    12. Now re-Run Package, it will re export and NOT append to existing, keeping formatting

    Hope this helps, it has made it easy for me to create Excel Reports for users that come already formatted.  The trick is making sure you get the quotes correct in the formatting of the DROP and CREATE AND that you name the table without the "$".

  • January 5, 2006 at 9:09 am

    #612924

    I had experienced similar problems and found the following to actually clear up the situation.

    1. Open the Excel file after the formatting "fiasco".
    2. DELETE THE ROWS of data experiencing the problem.
    3. Save the Excel file.
    4. Execute the package again and it should no longer have the formatting on the data rows.

    Something about deleting the rows seems to tell Excel that the formatting no longer extends to subsequent rows.  At least I've never had the problem again after making these changes.

  • January 5, 2006 at 12:03 pm

    #613000

    While this may not be the solution you are looking for, have you considered using Reporting Services to do this? Populate a table with the data. Set up Reporting Services to run the report on a schedule and place the file in folder/email it/just make it available. You can export directly into Excel format (just get at least SP1 before doing that) and you can format the report to have the headers formatted one way, data another, even format certain values if they meet certain criteria. As it sounds like you need a report, this may be worth investigating.

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

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