Exporting data from SSIS to Excel

335 pts.
Tags:
Microsoft Excel
microsoft excel functions
SSIS
I am trying to export data into Excel from SSIS. For the purpose of this question, the table is called “products” and has two columns. I would like to extract each row of data into a separate Excel file and save it with the product name so I can email it accordingly. How do I go about this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

You can do in tow ways. You can create an Excel file with those predefined columns, essentially your empty output file – this would act as your ‘Template File’. Your flow would then be this:
  1. File System Task – Copy template file to output or working directory (rename if necessary)
  2. OLEDB Source Task – Query your source for the data (3000)
  3. Data Conversion Task
  4. Excel Destination Task – Put data into new Excel file

Note: You already have steps 2 thru 3 complete, you just need to make sure you are connecting to the new Excel file. Also, to clarify, step 1 is outside the Control Flow Task.

This way is helpful because you always have a blank and consistently formatted Excel file to copy and work with.

or

The other option is to use a Script Task and create the Excel file – you could also load the data into the file in this task. This requires some basic understanding of VB.NET or C#. Basically you would need to get a XLS library (like NPOI). This is more complicated, but gives you the best functionality.

Hope this helps.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • AshishSingh10
    Hi,

    You can get help from the following instructions: -

    1. Drop Excel Table
    2. Create Excel Table with format as of my Select Query which i used to retrieve data from database
    3. Insert Data from Database to Excel file
    Thanks and regards
    Ashish@S
    875 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following