Tsql query has to populate in SSIS pacakge using transformations

Question

Tsql query has to populate in SSIS pacakge using transformations

nhimabindhu

Ten Centuries

Points: 1242
More actions
February 6, 2012 at 2:18 am

#250282

hi,
i have a question can any one help.
My source data is like this
zoneproductsale_priceqty
North Tv 20000.00 2
South Tv 30000.00 3
NorthComputer15000.002
SouthComputer25000.003
I need to get result set as total_qtytotal_saletotal_qty_tvtotal_sale_tv
10 90000.005 50000.00
In sql server according to this query i got the result
select sum(t1.qty) total_qty ,sum(t1.sale_price) total_sale,t2.total_qty_tv,t2.total_sale_tv from PRODUCTs t1,
(select sum(qty) total_qty_tv,SUM(sale_price) total_sale_tv from products where product='Tv')t2
group by t2.total_qty_tv,t2.total_sale_tv
How to get the same result by using ssis package .can any help by sending as screen shot also.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Apparently you have a magic SQL query, because there is no quantity column in the source, but there is one in the output. :w00t:

Seriously now, you can just execute the SQL statement in an Execute SQL Task in SSIS.

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Sorry, I forgot the screenshot (see the attachment).

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

nhimabindhu Ten Centuries Points: 1242 More actions · Answer 3

without using the query .Means using oledb source and aggregate tranfomations i need to send to destination .

Not using that query in execute task or in oledb source

Phil Parkin SSC Guru Points: 246986 More actions · Answer 4

nhimabindhu (2/6/2012)
without using the query .Means using oledb source and aggregate tranfomations i need to send to destination .
Not using that query in execute task or in oledb source

Please explain why not.

The query will run faster in SQL, so it's wise to use it if possible.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

nhimabindhu Ten Centuries Points: 1242 More actions · Answer 5

Requirement is like that without using sql query .populating data using only ssis transfomations.:-)

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

Is this a school project or something?

Whoever made that requirement needs to justify it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

nhimabindhu (2/6/2012)
Requirement is like that without using sql query .populating data using only ssis transfomations.:-)

That is a silly requirement. TSQL will outperform the SSIS components by a multitude (or is it magnitude?)

The aggregation transforms in SSIS are blocking components, so they slow things down enormously.

Also, don't forget you are dealing with a subquery. That will be peachy when only using SSIS components.

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Sounds a lot like homework.. And stupid homework at that.

Honestly I wouldn't accept this requirement, there is no rational reason to do it this way when the data is stored in SQL.

CEWII