Solved

Export data to Excel - sheet1 & sheet2

Posted on 2010-08-19
21
1,747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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