Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to copy data from multiple queries to same excel destination in ssis Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 3:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:35 AM
Points: 13, Visits: 19
Hi

I have a requirement where i have data from multiple queries to be copied to a template in ssis. i have used he following:
File system task to copy template
DFT in which data is copied from table using OLEDB
Data Conversion
and loaded in excel destination after data conversion.
This works fine for single table.
I have multiple tables whose data should be appended to the above template. Finally, mail should be sent with the excel as attachment.

Please help.
Post #1517578
Posted Tuesday, November 26, 2013 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
If all of the tables have the same format you can append the results together using UNION ALL (either in the source query itself or in the SSIS data flow).

If the tables have different formatting, you need a data flow per table.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1517581
Posted Tuesday, November 26, 2013 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:35 AM
Points: 13, Visits: 19
Forgot to mention that the tables have different formats.
Post #1517584
Posted Tuesday, November 26, 2013 3:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:35 AM
Points: 13, Visits: 19
I can have separate data flow. but how do i append the data to the already generated excel? i have separate headers for the other tables.

I have a table with 15 columns like id,desc, onsite hours ,offshore hours etc.
i have another query which consolidates the data like total hours for offshore,onsite etc.it has just 3 columns and it should appear after all the data has been populated for the first table but in the same sheet. How to achieve this?
Post #1517587
Posted Tuesday, November 26, 2013 3:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
You could use named ranges in the Excel destination to write to different parts of the Excel sheet, but you'd need to know the number of rows of each extract to make sure they do not overlap.

Personally, I would put each extract in a different Excel sheet.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1517588
Posted Tuesday, November 26, 2013 3:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:35 AM
Points: 13, Visits: 19
Can you please help me in that? i need all data in a single sheet.
Post #1517590
Posted Tuesday, November 26, 2013 3:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
This article sums it up rather nicely:

Import data to Excel sheet's specific region




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1517591
Posted Tuesday, November 26, 2013 4:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 5,047, Visits: 11,797
Personally, I would put each extract in a different Excel sheet.


Me too.

Seems like a neater solution all round. Are you sure that this is not a possibility?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1517604
Posted Tuesday, November 26, 2013 9:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:35 AM
Points: 13, Visits: 19
I am also thinking the same. Putting it in different worksheets. but in the second sheet, data from two tables need to be put . on data below the other.
Post #1517893
Posted Wednesday, November 27, 2013 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
Koen Verbeeck (11/26/2013)
This article sums it up rather nicely:

Import data to Excel sheet's specific region


Thanks for the link. Also, what do you mean by "formatting" over here -

Koen Verbeeck (11/26/2013)
If all of the tables have the same format you can append the results together using UNION ALL (either in the source query itself or in the SSIS data flow). If the tables have different formatting, you need a data flow per table.


Is it the order of column names ?
Post #1518210
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse