CODEWITHSUNDEEP
sql-serverssis
Neo
Neo

Reputation: 16219

How to generate an Excel file from SQL table using SSIS?

I'm new to SSIS and I want to generate an Excel file from a SQL table.

SQL table: mydata

Id    date       value
1     2013-02-2  236823
2     2013-02-3  185454

Now I need to generate an Excel file which will gives me an input like only date and value.

How can I generate that Excel file?

I have tried to do start a data flow task.

Inside I have taken OLE db Source and write SQL query select date, value from mydata, then added Excel destination.

But it is giving me an error in Excel destination:

external table is not in expected format

Upvotes: 0

Views: 5693

Answers (1)

Anoop Verma
Anoop Verma

Reputation: 1505

My friend, what have you tried so far? This a simple click-click-click stuff. Next time, please share your effort. Here is the guidance:

1. Place a DFT on the Control Flow surface
2. Double-click on the DFT to enter into the Data Flow surface
3. Place an OLE DB Source. Double-click on it. Click on New... button and create a new OLE DB connection manager
4. Data access mode: Table or view. Select your table
5. Click on the columns. Select the fields you want.
6. Now place an Excel Destination. Connect it to the OLE DB Source.
7. Double-click and click on New... button. Give the Excel file path where you want to dump the data.
8. Click on New.. Some warning will appear. Accept it.
9. Select the Name of the Excel sheet from the drop down.
10. Run the package.

This is plain vanilla solution. Once you know the basics, polish your solution. For example, think about setting the connections through variables.

Good luck!

Upvotes: 1

Related Questions