June 17, 2011 at 3:29 am
HI,
I have my source data this way
TestId TestName Testplace TestLoc
1 Social School1 Null
2 Science Null Null
1 Null Null Hyd
2 Null School2 Null
2 Null Null Mumbai
I need to Insert into the destination and the data should be inserted this way after insertion..
TestId TestName Testplace TestLoc
1 Social School1 Hyd
2 Science School2 Mumbai
Both my source and destination are MS Acess. Any idea which transformation i need to use to this achieve this..
Please let me know in case of any details required
Thanks before hand
🙂
🙂
June 17, 2011 at 4:22 am
Opa! I haven't seen a homework question buzz by in a while.
It must be... 24 hours?
Woot!
Alright, let me ask you the first question, if the origination database and the destination database are in MSAccess, a $200/instance software, why are we discussing using a $5,000/instance (minimum, unless Express, which won't run agent) software?
My next question would be have you used aggregation functions, in particular to this question, MAX() with a group by? If you have, what in SSIS allows you to do aggregations by group?
So, create your dataflow, with a source and destination for your MS Access databases, add them into said dataflow, and then look through the transformations in the toolbox on your left.
Let us know if this doesn't lead you towards your answer and what you found along the way, so we don't feel like we're your personal google.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 1:06 am
June 23, 2011 at 6:21 pm
Jayanth_Kurup (6/20/2011)
Try lookup under the DFT
Wouldn't work for this. Lookup is a per-row operator and he needs to collapse the rows. The only way to do that is with one of the stream interuption objects or a script transformation component using an asynchronous output and sorted input.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2011 at 3:56 pm
v-swgar (6/17/2011)
TestId TestName Testplace TestLoc1 Social School1 Null
2 Science Null Null
1 Null Null Hyd
2 Null School2 Null
2 Null Null Mumbai
I need to Insert into the destination and the data should be inserted this way after insertion..
TestId TestName Testplace TestLoc
1 Social School1 Hyd
2 Science School2 Mumbai
Within a dataflow:
1) Feed the output of your source into a Multicast transform
2) Add an Aggregate transform named "Test names":
a) Connect it to an output from the multicast
b) Configure the Aggregate to group by TestId and max(TestName)
c) Add a Sort transform name it "Sort Test Names by Id"
3) Repeat step 2 for Test Place and TestLoc
4) Add a MergeJoin with inputs from the Sort for TestName and the Sort from TestPlace (Join by TestId)
5) Add a MergeJoin with inputs from the MergeJoin in step 4 and the Sort from TestLoc (Join by TestId)
6) Feed the output of (5) into a destination
June 25, 2011 at 2:41 am
The previous post was using SSIS. It can be solved in the database directly. My MS Access SQL for the join syntax is very rusty but the SQL Server syntax would be:
[font="Courier New"]select
n.TestId,
n.TestName,
l.TestLocation,
p.TestPlace
from
(select TestId, max(TestName) as TestName from TestSource group by TestId) as n
inner join (select TestId, max(TestLocation) as TestLocation from TestSource group by TestId) as l on l.TestId = n.TestId
inner join (select TestId, max(TestPlace) as TestPlace from TestSource group by TestId) as l on p.TestId = n.TestId[/font]
A lot simpler than SSIS
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply