?
Solved

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

Posted on 2011-03-08
16
Medium Priority
?
579 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:motioneye
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35079991
one word: union

sql1 union all sql2 union all sql2...

Bye, Olaf.
0
 

Author Comment

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

Author Comment

by:motioneye
ID: 35080269
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'.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 9

Expert Comment

by:kaminda
ID: 35080346
I also think union is the best shot here. Looks like you are getting a syntax error, please do share your new query
0
 

Author Comment

by:motioneye
ID: 35080372
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
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 35080454
You cannot apply order by to the select part of the union, only to the overall union. Remove the first "order by".
However, in your case where you select from the same table with different criteria only, I would switch to a single statement with some "hidden" group by (hidden because the group columns are not appearing in the select list, as you have already used with domname):
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

Open in new window

This will provide a count for each unique (domname, CatID, trgigerID) combination.
0
 

Author Comment

by:motioneye
ID: 35080650
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
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35080696
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.
0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1800 total points
ID: 35081426
Sorry, didn't think about that aspect.

I don't agree with putting together the where clauses into one, as you implicitly not only group by DomName, but also the side conditions about CatID and triggerID. This is mangled together in a single select, eg you only have one record per DomName then.

You could introduce a computed column to group by additionally:

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

Bye, Olaf.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35081457
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).
0
 

Author Comment

by:motioneye
ID: 35081835
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 :)

0
 
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1800 total points
ID: 35082221
Right, Qlemo, but introducing a groupid gives you more control about what's inside each group of record counts. You could also have where clauses which produce overlapping groups and this would stop working with an overall where clause approach. If this makes sense is depending on the meaning of the data of course.

For example, what would be, if you had these where clauses:
1. CatID in (1,2) and trgigerID in (1, 2, 11)
2. CatID in (2,3) and trgigerID in (2, 5)
3. CatID in (4) and trgigerID in (6)

Putting this together in one wehre and grouping by CatID, triggerID then won't help.

I just want to stay with appending the seperate results together, and this should do. Full query:

select CAST(1 as int) as groupid,  DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 1 AND DomName <> ''
group by DomName
union all
select CAST(2 as int) as groupid, DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 2 AND DomName <> ''
group by DomName
union all
select CAST(3 as int) as groupid, DomName, count(*) as count from TableTest with ( nolock )
where CatID = 1 and trgigerID = 11 AND DomName <> ''
group by DomName
union all
select CAST(4 as int) as groupid, DomName, count(*) as count from TableTest with ( nolock )
where CatID = 2 and trgigerID = 2 AND DomName <> ''
group by DomName
union all
select CAST(5 as int) as groupid, DomName, count(*) as count from TableTest with ( nolock )
where CatID = 2 and trgigerID = 5 AND DomName <> ''
group by DomName
union all
select CAST(6 as int) as groupid, DomName, count(*) as count from TableTest with ( nolock )
where CatID = 4 and trgigerID = 6 AND DomName <> ''
group by DomName
order by groupid asc, count desc

Open in new window


@motioneye, what is not working with this?

Bye, Olaf.
0
 

Author Comment

by:motioneye
ID: 35083493
Hi Olaf,
As per subject and reason why I post this, due to ssis ole db source editor cannot accept multiple sql statement,  
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35086418
This is one statement now.

Bye, Olaf.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35087268
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.
0
 

Author Comment

by:motioneye
ID: 35090008
Hi Olaf,
Yes thanks, your query written one statement, really helpful indeed :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question