Solved

Export data to Excel - sheet1 & sheet2

Posted on 2010-08-19
21
1,686 Views
Last Modified: 2013-11-10
I have a store procedure to create a couple of talbe an insert data to tables like below sample.
I want to export data from these two table to my excel file, the table a on sheet1 and table b on sheet2. I am really new to SSIS. When the package runs again, it should truncate the two table first. Could you provide me steps?

creteat table a
(
)
insert table a
select
creteat table b
(
)
insert table b
select


0
Comment
Question by:VBdotnet2005
  • 8
  • 8
  • 3
  • +2
21 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33474723
where did you execute stored procedure above?
is it in an Execute SQL Task?

if yes, just you need to put two data flow task after execute sql task,
first data flow task for source table a to excel sheet 1
second data flow task to source table b to excel sheet 2

does it make sense to you?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33474785
On  your dataflow, you will provide an Excel destination.  When you double click that destination, you will be given the option to select which sheet you want to put it to.

Steps:
1.  Add a dataflow and double click it
2.  Within dataflow, you will add your source (or sources) for your two tables.  Just do selects or export whole table if desired
3.  Add an Excel destination and connect your source to this.  Within that destination, you will configure mappings but you will also select which sheet of your Excel file you want the output to go to.

0
 
LVL 16

Expert Comment

by:carsRST
ID: 33474851
Here's a sample Excel source.  The Excel destination is pretty similar, so you can see that you can select which sheet to export to.
SQL1008-FigF.png
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33474965
The jet provider does not allow to delete the data from Excel File.. You will need to do it using a Linked server with an Exceute SQL task if you are so particular about it..But i would advise, this below method,

Create Excel file using Execute SQL task as Described above

CREATE TABLE [SheetName1] (`HeaderID` INTEGER, `HeaderName` NVARCHAR(50), `LineID` INTEGER, `LineName` NVARCHAR(50), `LineDetails` NVARCHAR(50))


CREATE TABLE [SheetName2] (`HeaderID` INTEGER, `HeaderName` NVARCHAR(50), `LineID` INTEGER, `LineName` NVARCHAR(50), `LineDetails` NVARCHAR(50))

Connect the Execute SQL Task to Data Flow task. In the Data Flow task you will need to selec t your Source as OLE DB Source and EXCEL destination.. Connect Source to destination
In the Source You will need to write the Select your database and the write the Select statement as would in the SSMS (You will need to create two source and Two destinations one for each in the Data Flow task Or you can use two dataflow task seperately )

And above the Execute SQL task you will need to create an FILE System Task to Delete your old Excel File..

Note: you will need to create the Excel file manually for the first time for mapping the columns and also remember to Change the DELAY VALIDATION Property of the Data Flow task to TRUE..
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33475060
0
 
LVL 11

Expert Comment

by:Simone B
ID: 33487030
Is there a specific reason for using SSIS for this? If you want to add new rows to the spreadsheet every time the package runs (and preserve the previous rows), SSIS is the way to go.
But if you're truncating each time, it sounds like you just want to get fresh data from your SQL tables into a spreadsheet. It is relatively easy to design an SQL query within the worksheet. Then you can set it to refresh each time it's opened.
0
 

Author Comment

by:VBdotnet2005
ID: 33487469
Sorry, I am a bit confused here. Plse see image

1. Execute SQL Task

   This is to create tmp(in memory) table in SQL
creteat table a
(
)
insert table a
select
creteat table b
(
)
insert table b
select

2. Data Flow  
   Ole DB source???  and Excel destination
   (I want table a in Excel - sheet 1, same excel sheet 2 with table b)
0
 

Author Comment

by:VBdotnet2005
ID: 33487474
see image
ee.bmp
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33487516
You don't need to put the table to memory.

Add a dataflow.  Within that dataflow, add your ole db source and put the SQL statement there (not outside the dataflow).  Then connect that to the Excel destination.

Repeat for second table.

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33487976
Yes, you don't need to store the data in temp table.. You will  need the execute SQL task to create Tables in excel File and Data Flow task to import data from SOURCE A , SOURCE B to Sheet1 TABLE A and Sheet2 TABLE B respectively...

Let me get the question right here.. I understand that you want to import data from Table A and Table B On SQL SERVER to TABLE A, TABLE B (Sheet1 and Sheet2) Respectively and Also want to truncate the data in the excel files Every time you run the package ? Is that correct

If so, Follow the steps I suggested in my previous post

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:VBdotnet2005
ID: 33488750
I have a template(with all headers I need). Now do I still need to create Excel using Execute SQL task?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33488842
Ok.. the problem here is you Cannot truncate the data in the excel file.. So, There are two ways around this..

1. When ever you run the package Either delete the File Using File System Task and then Create another file with the same format use OLE DB Task as i suggested
2. Create  a Template (as you have).. Copy the Template with a different/desired Name..load the data and then next time when the package runs again you need to delete this copied file and then copy the template with the same name again

2nd Method
1. Inside For Each loop
2. File system Task (Delete the old File)
3. Make a copy of the Template

Then you will need to attach the task to Data Flow task to load the data..

Hope you understand my point
0
 

Author Comment

by:VBdotnet2005
ID: 33489648
When I am in OLE DB source Editor, and click "Preview" can I see my data perfectly fine, but when I click on "Columns", it does not have any columns. Is this normal? I don't see any warning on Ole DB Source.

ee.bmp
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33490324
No,Its not Normal you should see the columns in the columns tab... When you reopen the OLE DB Source is the query there.. Ther's something wrong can i see the Query and you screen shots of OLE DB Source ?
0
 

Author Comment

by:VBdotnet2005
ID: 33495533
something like this

create table #mytable1(
      
)


create table #myResult(

)

-- select only the account that have some activity
INSERT #mytable1
SELECT...

INSERT #myResult
select
      
from #mytable1
UNION
select
      
from #mytable1
where account1<> ''
UNION
SELECT
      
from #mytable1
where acount2<> ''

-- sort by custordered
select
       from #myResult


drop table #mytable1
drop table #myResult
0
 

Author Comment

by:VBdotnet2005
ID: 33495561
it says "invalid object name #mytable1"
ee.bmp
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33496311
you will need to create a table 1st in Management studio... This is what you need to do as described in the Link
http://www.sqllike.com/using-temporary-tables-with-ssis.html

In the Ole DB Source You will just need to write the Single Select statement without any insert or create commands..

The create should be written in execute SQL Task in Control flow  
0
 

Author Comment

by:VBdotnet2005
ID: 33512581
Now, it errors out because of this warnings....

Truncation may occur due to inserting  data from data flow column "mycolumn" with a length of 1024 to  database column "mycolumn" with a length of 255.

...
...

etc

there is no other error
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33512674
It errors out because the Source column Value is greater than Destination Column Value and Hence gives you the truncation error..

Increase the Destination Column Length to 1024 ..
0
 

Author Comment

by:VBdotnet2005
ID: 33629390
Do I do it here?
ee.bmp
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33629806
No.. You will need to change the definition of your Temp Table

Change the length of the Column in the destination temp table (#myResult)to 1024

Also Execute the Create statement from Management studio ..Map the Fields again in the DataFlow Task, so that the Columns are back in sync and then you can re run your SSIS Package
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now