How to copy data from multiple queries to same excel destination in ssis

Question

How to copy data from multiple queries to same excel destination in ssis

suchitra.venkataramani

SSC-Addicted

Points: 441
More actions
November 26, 2013 at 3:03 am

#301696

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.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 2

Forgot to mention that the tables have different formats.

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 3

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?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 5

Can you please help me in that? i need all data in a single sheet.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

This article sums it up rather nicely:

Import data to Excel sheet's specific region

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 8

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.

blasto_max SSCertifiable Points: 5025 More actions · Answer 9

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 ?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

With formatting I mean the table schema: columns, column names, data types et cetera.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

LutzM SSC Guru Points: 107049 More actions · Answer 11

I'm not sure what your template looks like...

Do you want to have each table loaded to a separate Workbook?

If so, you could have a look at this link how to do it using SSIS.

But if you need to have all in a single workbook (together with a unknown number of rows returned), all I can think of is to create a single return table using UNION ALL with the number of columns matching the max. number of columns returned by one of the tables involved (using blank values for the other tables). You could even add blank rows and column names if needed. This solution would be rather dirty since it would throw away any difference of the information (as it is represented using separate tables). But this would match the requirement to have all in one workbook....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 12

That link was really useful. i am planning to get data from execute sql task and then i will put in script task as i will have only 2 rows of data as output in my table . i have written the script as well but what i am facing now is the variable is not getting replaced with the value. i used msgbox to view the values in the script.i have declared the variable and used them in result set (single row) in execute sql and selected it as read only variable.

This is my VB script

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports Microsoft.Office.Interop.Excel

Imports System.Collections.Generic

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Private Const Location As String = "User::VarLocation" // declared as variable

Public Sub Main()

'

' Add your code here

Dim var1 As String = DirectCast(Location, String)

MsgBox(Location)

Dim ObjExcel As Object

Dim xlsheet As Object

Dim wb As Object

Dim filetemplate As String = "D:\OutPutFiles\OutputFile.xlsx"

ObjExcel = CreateObject("Excel.Application")

ObjExcel.visible = True

wb = ObjExcel.workbooks.open(filetemplate)

xlsheet = ObjExcel.worksheets("Utilization")

ObjExcel.displayAlerts = False

Dim Rowindex As Integer = 3

If (var1 = "") Then

xlsheet.cells(Rowindex, 1).value = "No Results"

End If

Dim var2 As String = var1

xlsheet.cells(Rowindex, 1).value = var2

MsgBox(var2)

wb.save()

ObjExcel.quit()

ObjExcel = Nothing

Dts.TaskResult = ScriptResults.Success

End Sub

Where did i go wrong?

suchitra.venkataramani SSC-Addicted Points: 441 More actions · Answer 13

More details

I have a table Reporting and my sample query is Select distinct location from Reporting

For this,

i have declared VarLocation as String,

in Execute SqL task, i have given this query with single Row as ResultSet.

In Resultset tab, i have given

ResultName VariableName

0 User::VarLocation

and then running the above script in vb.

Please help me reg this.

TIA

Phil Parkin SSC Guru Points: 246965 More actions · Answer 14

Remove the line

Private Const Location As String = "User::VarLocation" // declared as variable

And then ...

Dim var1 As String = DirectCast(Location, String)

should become something like

Dim var1 As String = Dts.Variables("Location").Value

I said 'something like' because I haven't written any VB for a while.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin