Post reply

Export multiple SQL data to multiple Excel files using SSIS

  • August 28, 2009 at 12:55 am

    #74093

    I need to run 10-20 SQL scripts every day for reporting purpose and they took about 5-10 hours to run. I manually run these SQLs to fetch the data and export them to different Excel files.

    This is very painful as I have to wait for the each resource hungry SQL to complete.

    So I thought to automate the process by using SSIS.

    But the issue is my SQLs are not uniform. Every SQL fetches different number of columns. The main problem is to export them into different Excel sheets with variable number of columns.

    Please suggest any example.

    Thanks,

    manu

    ~manoj | LinkedIn | Facebook | YouTube
    Blog: SQLwithManoj.com

  • August 28, 2009 at 4:56 am

    #1046105

    Set up a separate data flow task for each.

    In each data flow task, set up your oledb source to your sql server, and an Excel Destination.

    This should handle what you're trying to do.

    You can't use just one oledb data source and one Excel Destination and loop through all of your operations. Well, you can if they all have the same structure, and thus the same mappings. But since you did say that the sql commands are getting a different number of columns, so you will need a separate source/destinations for each one.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

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