[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1815
  • Last Modified:

Export data to Excel - sheet1 & sheet2

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
VBdotnet2005
Asked:
VBdotnet2005
  • 8
  • 8
  • 3
  • +2
1 Solution
 
Reza RadCommented:
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
 
carsRSTCommented:
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
 
carsRSTCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
vdr1620Commented:
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
 
Simone BCommented:
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
 
VBdotnet2005Author Commented:
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
 
VBdotnet2005Author Commented:
see image
ee.bmp
0
 
carsRSTCommented:
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
 
vdr1620Commented:
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
 
VBdotnet2005Author Commented:
I have a template(with all headers I need). Now do I still need to create Excel using Execute SQL task?
0
 
vdr1620Commented:
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
 
VBdotnet2005Author Commented:
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
 
vdr1620Commented:
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
 
VBdotnet2005Author Commented:
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
 
VBdotnet2005Author Commented:
it says "invalid object name #mytable1"
ee.bmp
0
 
vdr1620Commented:
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
 
VBdotnet2005Author Commented:
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
 
vdr1620Commented:
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
 
VBdotnet2005Author Commented:
Do I do it here?
ee.bmp
0
 
vdr1620Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now