April 14, 2012 at 5:21 am
hello everyone.
i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
April 15, 2012 at 3:55 pm
you have a very vague question. can you post more detail and maby table deffinitions and sample data.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 15, 2012 at 9:39 pm
CELKO (4/15/2012)
The terms parent and child are not RDBMS; they are from the old network DBs. We have referenced and referencing tables which is very different.
Only a couple of thousand people disagree with you. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2012 at 9:47 pm
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
I don't know much about SSIS. However, I suspect that with or without knowledge of what your tables look like, I don't believe that this is going to be an easy thing to do in SSIS. Part of the purpose of me posting here is to help "bump" this particular post because I'm also interested in how this can be done in SSIS.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2012 at 5:57 am
Considering the urgency of the requirement and the vagueness of the question, the following link seems to be an apt solution:
April 17, 2012 at 11:03 am
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
This type of request goes WAY beyond a forum thread. These forums are "staffed" by unpaid people who volunteer their time and thus are for short, targeted, straight-forward needs. This isn't any of those. Get yourself a consultant to help with implementation and mentoring you on how to manage/maintain this project going forward.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2012 at 12:27 pm
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
First, with or without SSIS, you need to map source tables to destination tables. Do they have the same columns in the destination? Do the five "parent tables" map to five tables in the destination? Are both databases normalized to the same extent in the same manner?
Once you've worked out what data goes from where to where, SSIS data flows will probably be very intuitive. But you need to map it first.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 12:28 pm
Jeff Moden (4/15/2012)
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
I don't know much about SSIS. However, I suspect that with or without knowledge of what your tables look like, I don't believe that this is going to be an easy thing to do in SSIS. Part of the purpose of me posting here is to help "bump" this particular post because I'm also interested in how this can be done in SSIS.
Not sure why you think it would be difficult. Unless the data transformations are extremely complex, SSIS does this kind of thing very, very easily. It's pretty much what it's built for, after all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 12:35 pm
CELKO (4/15/2012)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.This is minimal polite behavior on a SQL forum.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
The terms parent and child are not RDBMS; they are from the old network DBs. We have referenced and referencing tables which is very different.
Yes, they are completely different concepts. Except for the minor fact that they're two different words for the same thing. Same meaning, different letters, different pronunciation. Like "cat" and "felix domesticus". Same kind of "very different". (Or is my sarcasm getting too thick?)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 6:14 pm
GSquared (4/17/2012)
Jeff Moden (4/15/2012)
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
I don't know much about SSIS. However, I suspect that with or without knowledge of what your tables look like, I don't believe that this is going to be an easy thing to do in SSIS. Part of the purpose of me posting here is to help "bump" this particular post because I'm also interested in how this can be done in SSIS.
Not sure why you think it would be difficult. Unless the data transformations are extremely complex, SSIS does this kind of thing very, very easily. It's pretty much what it's built for, after all.
Granted, if the tables are different than their intended targets, then you have to map them no matter which method you use. My comment about it being difficult comes from the idea that if the source and destination tables are identical and the "child" tables on the source all have DRI properly setup, then it's a lot easier to do (MHO because I don't know how to do the same thing automatically in SSIS) in T-SQL. Feed it the 5 "main" table names, query the DRI to find the related tables, and write some dynamic SQL to do all the mapping "auto-magically". Of course, if it's more than a one-off, someone should possibly spend a little time on replication for all of this.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2012 at 6:29 am
Jeff Moden (4/17/2012)
GSquared (4/17/2012)
Jeff Moden (4/15/2012)
vm.chowdary85 (4/14/2012)
hello everyone.i got a scenario like this i got 5 parent tables and 30 child tables in the source using SSIS control flow and data flow i need to transform them to the destination database as fast as possible. can any one explain in detail to me how to solve this as i am very new to this SSIS ...
Many thanks.
I don't know much about SSIS. However, I suspect that with or without knowledge of what your tables look like, I don't believe that this is going to be an easy thing to do in SSIS. Part of the purpose of me posting here is to help "bump" this particular post because I'm also interested in how this can be done in SSIS.
Not sure why you think it would be difficult. Unless the data transformations are extremely complex, SSIS does this kind of thing very, very easily. It's pretty much what it's built for, after all.
Granted, if the tables are different than their intended targets, then you have to map them no matter which method you use. My comment about it being difficult comes from the idea that if the source and destination tables are identical and the "child" tables on the source all have DRI properly setup, then it's a lot easier to do (MHO because I don't know how to do the same thing automatically in SSIS) in T-SQL. Feed it the 5 "main" table names, query the DRI to find the related tables, and write some dynamic SQL to do all the mapping "auto-magically". Of course, if it's more than a one-off, someone should possibly spend a little time on replication for all of this.
If the tables are all the same, you can actually set up the SSIS package by using the Import/Export Wizard in SSMS. Tell it which tables map to which other tables, select Allow Identity Insert in the column mapping, and you're done. Should take less than five minutes from start to finish on setting it up. (Running will take however long it takes, depending on the number of rows per table.) SSIS will even optimize the process to avoid overloading tempdb and your tran logs, which also speeds it up considerably.
I had a situation where a source database had several tables (couple dozen) that I needed to copy to another datacenter about 1,000 miles away. Needs to copy data every 15 minutes. Snapshot replication was taking about 8 minutes per copy. T-SQL versions were worse (because of DTC - local tests were plenty fast). SSIS took about 2 minutes to set up, and does the whole copy job, reliably, in about 15 seconds.
Once the data is on the remote servers, I have various T-SQL jobs in SQL Agent that do all the more complex transformations and distribute the data to three different databases for different websites. Complex business rules in each one, and so on. T-SQL procedures (LOTS of Merge statements, mainly) do that part more efficiently. But the initial long-distance copy of specific tables is most efficiently done by SSIS, at least in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2012 at 8:11 am
GSquared (4/19/2012)
Once the data is on the remote servers, I have various T-SQL jobs in SQL Agent that do all the more complex transformations and distribute the data to three different databases for different websites. Complex business rules in each one, and so on. T-SQL procedures (LOTS of Merge statements, mainly) do that part more efficiently. But the initial long-distance copy of specific tables is most efficiently done by SSIS, at least in this case.
Weirdly enough for me with SSIS, I've started replacing the MERGE statements for our DW with a series of lookups that stream new records in nice and quick and do updates for the changed already existant ones... Took merge statements that typically take 5 minutes and cut them down to 30-40 seconds...
I was shocked as hell when I tested that out because I didn't expect to outperform the MERGE statement, but I did.
April 19, 2012 at 8:38 am
Been 5 days and nothing from the OP. Guess it isn't as urgent as he said.
April 20, 2012 at 6:26 am
mtassin (4/19/2012)
GSquared (4/19/2012)
Once the data is on the remote servers, I have various T-SQL jobs in SQL Agent that do all the more complex transformations and distribute the data to three different databases for different websites. Complex business rules in each one, and so on. T-SQL procedures (LOTS of Merge statements, mainly) do that part more efficiently. But the initial long-distance copy of specific tables is most efficiently done by SSIS, at least in this case.Weirdly enough for me with SSIS, I've started replacing the MERGE statements for our DW with a series of lookups that stream new records in nice and quick and do updates for the changed already existant ones... Took merge statements that typically take 5 minutes and cut them down to 30-40 seconds...
I was shocked as hell when I tested that out because I didn't expect to outperform the MERGE statement, but I did.
I tested two-step upserts vs Merge, and it was faster in most cases in my code. But it isn't always faster, and possibly not even faster the majority of the time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2012 at 6:57 am
GSquared (4/20/2012)
I tested two-step upserts vs Merge, and it was faster in most cases in my code. But it isn't always faster, and possibly not even faster the majority of the time.
Well presently I've done it to about 30 different execute SQL tasks in SSIS all showing remarkable improvement....
But it could be I'm lucky. As with everything, it depends 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply