Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
COURSE of the MONTH
13 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to write multiple T-sql in Oledb/ or any other dts component in SSIS
Posted on 2011-03-08
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
4
4- +1
16 Comments
Active 1 day ago
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'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Union'.
I also think union is the best shot here. Looks like you are getting a syntax error, please do share your new query
Active 1 day ago
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
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
Active today
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):
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
Active 1 day ago
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
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
Active today
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.
Best to check with a single DomName. Just add a fixed DomName to the where clause for debugging.
Active today
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.
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.
Active today
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).
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).
Active 1 day ago
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 :)
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 :)
Active today
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:
@motioneye, what is not working with this?
Bye, Olaf.
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
@motioneye, what is not working with this?
Bye, Olaf.
Active 1 day ago
Hi Olaf,
As per subject and reason why I post this, due to ssis ole db source editor cannot accept multiple sql statement,
As per subject and reason why I post this, due to ssis ole db source editor cannot accept multiple sql statement,
Active today
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.
"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.
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month13 days, left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.