Avatar of motioneye
motioneye
Flag for Singapore asked on

How to write multiple T-sql in Oledb/ or any other dts component in SSIS

I'm writing ssis package ( using oledb source editor ) to query the db then save the result in disk drive as *.csv, the problem is oledb only can be use to run single T-sql command, so if I have a query like below, how do I run this query in ssis then save a results in single file destination ?

select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 1 AND DomName <> ''
group by DomName order by count(*) desc
GO


select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 2 AND DomName <> ''
group by DomName order by count(*) desc
GO

select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 11 AND DomName <> ''
group by DomName order by count(*) desc
GO

select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 2 and trgigerID = 2 AND DomName <> ''
group by DomName order by count(*) desc
GO

select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 2 and trgigerID = 5 AND DomName <> ''
group by DomName order by count(*) desc
GO

select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 4 and trgigerID = 6 AND DomName <> ''
group by DomName order by count(*) desc
GO

ssis-oledb.jpg
Microsoft SQL Server

Avatar of undefined
Last Comment
motioneye

8/22/2022 - Mon
Olaf Doschke

one word: union

sql1 union all sql2 union all sql2...

Bye, Olaf.
motioneye

ASKER
Hi Olaf,
I'm cracking my head, well then u one word helping much indeed :)
motioneye

ASKER
Tested with the query, union / union all doesn't work with error below

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Union'.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
kaminda

I also think union is the best shot here. Looks like you are getting a syntax error, please do share your new query
motioneye

ASKER
I tested with this two first


select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 1 AND DomName <> ''
group by DomName order by count(*) desc
Union all
select DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 2 AND DomName <> ''
group by DomName order by count(*) desc
GO
SOLUTION
Qlemo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
motioneye

ASKER
I tested and it return me with different result set, the results is no loner as what has been produced by the 6 T-sql command


select DomName, count(*) as count from TableTest with ( nolock )
where DomName <> '' and
     (CatID = 1 and trgigerID in (1, 2, 11)
      or
      CatID = 2 and trgigerID in (2, 5)
      or
      CatID = 4 and trgigerID in (6)
     )
group by DomName, CatID, trgigerID
order by count(*) desc
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Qlemo

I can't see why it should be different - with one exception, which is the ordering. But that would apply the same when using union.
Best to check with a single DomName. Just add a fixed DomName to the where clause for debugging.
ASKER CERTIFIED SOLUTION
Olaf Doschke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Qlemo

Olaf,
Look at the group by: since CatID and trgigerID are contained in it, you get one record for each tuple, as I have already described.

motioneye,
Without displaying the grouping columns, or using some constructed value like above you do not have control over the different "branches" of the union (or my version of SQL).
motioneye

ASKER
Hi Olaf,
Thanks for your help, The query was return me the same values as my earlier post.

Hi Qlemo,
Thanks for your scripts too, actually I can see a reasons why both of you writing the code in different style,m indeed this is what I want :) since I can compare the data and convince the management how should they look into report :)

Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Olaf Doschke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
motioneye

ASKER
Hi Olaf,
As per subject and reason why I post this, due to ssis ole db source editor cannot accept multiple sql statement,  
Olaf Doschke

This is one statement now.

Bye, Olaf.
Qlemo

Olaf,
"introducing a groupid gives you more control" is what I said in http:#a35081457, but we still do not know if that is necessary at all, as it adds to the complexity. No doubt it is needed if the selection and grouping criteria are non-unique (overlapping). But if there is no overlap, always prefer the most simple approach.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
motioneye

ASKER
Hi Olaf,
Yes thanks, your query written one statement, really helpful indeed :)