|
|
You're in the
sql server dts
group:SSIS - new challenge ...how build table from six input tables
sql server dts:
The good news, I've created 6 tables from a single input table source. Each record of the input source had a primary key that was included in the 6 destination tables as a "foreign key". Now I need to take the 6 tables and re-assemble them into the original table format to be used as an interim data warehouse. The bad news, 4 of the original destination tables have a "one to one" relationship with the original input table. i.e. One record was created in the 4 destination tables for each record in the original "input" table record. Unfortunately, the other 2 original destination tables had a "one to many" relationship with the original "input" table. i.e. one original input record could create "1 to n" destination table records (i.e. the bad news part). Being relatively new to SSIS I'm a little uncertain of what SSIS tools I could use to tackle this problem. (01) 1st and easier problem . I need a control that can handle 6 inputs and will allow me to select out all records from the six tables that have the same primary key. (02) 2nd not so easy problem . I need to assemble the data selected from the 6 input tables into a single output record. (02a) the wl_well_analysis input table (one of the 2 many to one input tables) could very well give me 6 records with "potable too little", "potable salty", "potable muddy", "potable odor", "potable colored" or "potable other" descriptions. These I need to translate into CHAR(1) value columns in the output table . i.e. potable_too_little char(1) Y/N potable_salty char(1) Y/N potable_muddy char(1) Y/N potable_odor char(1) Y/N potable_colored char(1) Y/N potable_other varchar(30) Y/N Interesting challenge . I expect that this may just be a little beyond SSIS's limits and that I might just have to limber up Visual Studio and C# to get this done in a timely manner. However, if I can do this in SSIS I'd really like to know. Thanks in advance! Barry in Oregon
Hello Frost,
You could consider to use SQL command in to directly join the tables you want. Or you could try "lookup" items. Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- [quoted text, click to view] >Reply-To: "frostbb" <barry.b.frost@remove.this.wrd.state.or.us>
>From: "frostbb" <frostbb@newsgroups.nospam> >Subject: SSIS - new challenge ...how build table from six input tables >Date: Thu, 5 Jan 2006 16:45:34 -0800 >Lines: 86 >Organization: Oregon Water Resources Dept >X-Priority: 3 >X-MSMail-Priority: Normal >X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 >X-RFC2646: Format=Flowed; Original >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 >Message-ID: <uZJx#plEGHA.1384@TK2MSFTNGP11.phx.gbl> >Newsgroups: microsoft.public.sqlserver.dts >NNTP-Posting-Host: 159.121.113.234 >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.dts:62903 >X-Tomcat-NG: microsoft.public.sqlserver.dts > >Ok, > > > >The good news, I've created 6 tables from a single input table source. >Each record of the input source had a primary key that was included in the 6 >destination tables as a "foreign key". > > > >Now I need to take the 6 tables and re-assemble them into the original table >format to be used as an interim data warehouse. > > > >The bad news, 4 of the original destination tables have a "one to one" >relationship with the original input table. i.e. One record was created in >the 4 destination tables for each record in the original "input" table >record. Unfortunately, the other 2 original destination tables had a "one >to many" relationship with the original "input" table. i.e. one original >input record could create "1 to n" destination table records (i.e. the bad >news part). > > > >Being relatively new to SSIS I'm a little uncertain of what SSIS tools I >could use to tackle this problem. > > > >(01) 1st and easier problem . I need a control that can handle 6 inputs and >will allow me to select out all records from the six tables that have the >same primary key. > > > >(02) 2nd not so easy problem . I need to assemble the data selected from the >6 input tables into a single output record. > > > >(02a) the wl_well_analysis input table (one of the 2 many to one input >tables) could very well give me 6 records with "potable too little", >"potable salty", "potable muddy", "potable odor", "potable colored" or >"potable other" descriptions. These I need to translate into CHAR(1) value >columns in the output table . i.e. > > > >potable_too_little char(1) Y/N > >potable_salty char(1) Y/N > >potable_muddy char(1) Y/N > >potable_odor char(1) Y/N > >potable_colored char(1) Y/N > >potable_other varchar(30) Y/N > > > >Interesting challenge . I expect that this may just be a little beyond SSIS's >limits > >and that I might just have to limber up Visual Studio and C# to get this >done in a > >timely manner. > > > >However, if I can do this in SSIS I'd really like to know. > > > >Thanks in advance! > > > >Barry > >in Oregon > > >
Peter,
Thanks for your response. :) I'll continue my research. If I come up with a viable SSIS solution I'll let the group know. Best wishes, Barry in Oregon [quoted text, click to view] "Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message news:3EUlk%23oEGHA.3764@TK2MSFTNGXA02.phx.gbl... > Hello Frost, > > You could consider to use SQL command in to directly join the tables you > want. Or you could try "lookup" items. > > Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ===================================================== > > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > -------------------- >>Reply-To: "frostbb" <barry.b.frost@remove.this.wrd.state.or.us> >>From: "frostbb" <frostbb@newsgroups.nospam> >>Subject: SSIS - new challenge ...how build table from six input tables >>Date: Thu, 5 Jan 2006 16:45:34 -0800 >>Lines: 86 >>Organization: Oregon Water Resources Dept >>X-Priority: 3 >>X-MSMail-Priority: Normal >>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 >>X-RFC2646: Format=Flowed; Original >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 >>Message-ID: <uZJx#plEGHA.1384@TK2MSFTNGP11.phx.gbl> >>Newsgroups: microsoft.public.sqlserver.dts >>NNTP-Posting-Host: 159.121.113.234 >>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl >>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.dts:62903 >>X-Tomcat-NG: microsoft.public.sqlserver.dts >> >>Ok, >> >> >> >>The good news, I've created 6 tables from a single input table source. >>Each record of the input source had a primary key that was included in the > 6 >>destination tables as a "foreign key". >> >> >> >>Now I need to take the 6 tables and re-assemble them into the original > table >>format to be used as an interim data warehouse. >> >> >> >>The bad news, 4 of the original destination tables have a "one to one" >>relationship with the original input table. i.e. One record was created in >>the 4 destination tables for each record in the original "input" table >>record. Unfortunately, the other 2 original destination tables had a "one >>to many" relationship with the original "input" table. i.e. one original >>input record could create "1 to n" destination table records (i.e. the bad >>news part). >> >> >> >>Being relatively new to SSIS I'm a little uncertain of what SSIS tools I >>could use to tackle this problem. >> >> >> >>(01) 1st and easier problem . I need a control that can handle 6 inputs > and >>will allow me to select out all records from the six tables that have the >>same primary key. >> >> >> >>(02) 2nd not so easy problem . I need to assemble the data selected from > the >>6 input tables into a single output record. >> >> >> >>(02a) the wl_well_analysis input table (one of the 2 many to one input >>tables) could very well give me 6 records with "potable too little", >>"potable salty", "potable muddy", "potable odor", "potable colored" or >>"potable other" descriptions. These I need to translate into CHAR(1) > value >>columns in the output table . i.e. >> >> >> >>potable_too_little char(1) Y/N >> >>potable_salty char(1) Y/N >> >>potable_muddy char(1) Y/N >> >>potable_odor char(1) Y/N >> >>potable_colored char(1) Y/N >> >>potable_other varchar(30) Y/N >> >> >> >>Interesting challenge . I expect that this may just be a little beyond > SSIS's >>limits >> >>and that I might just have to limber up Visual Studio and C# to get this >>done in a >> >>timely manner. >> >> >> >>However, if I can do this in SSIS I'd really like to know. >> >> >> >>Thanks in advance! >> >> >> >>Barry >> >>in Oregon >> >> >> >
Peter,
Do you mean an SSIS "Execute Sql Task" control or a data flow "Data Reader Source" control when you refer to "SQL command" ?? I've broken up the original problem into steps The 1st step of the process is to execute a big gnarly sql query that joins 4 tables (SELECT bunches of stuff with a bunch of LEFT OUTER JOINS) I need to take the 300,000 + rows that result from that sql query and then pass them through a few column conversions and then out to a "Flat File Destination". I've managed to get the SSIS "Execute Sql Task" control to accept the big gnarly sql query but I can't figure out how to pass the results from the Control Flow "Execute Sql Task" into the data flow as an input ... Any help would be greatly appreciated. Thanks in advance. Barry in Oregon [quoted text, click to view] "Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message news:3EUlk%23oEGHA.3764@TK2MSFTNGXA02.phx.gbl... > Hello Frost, > > You could consider to use SQL command in to directly join the tables you > want. Or you could try "lookup" items. > > Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ===================================================== > > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > -------------------- >>Reply-To: "frostbb" <barry.b.frost@remove.this.wrd.state.or.us> >>From: "frostbb" <frostbb@newsgroups.nospam> >>Subject: SSIS - new challenge ...how build table from six input tables >>Date: Thu, 5 Jan 2006 16:45:34 -0800 >>Lines: 86 >>Organization: Oregon Water Resources Dept >>X-Priority: 3 >>X-MSMail-Priority: Normal >>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 >>X-RFC2646: Format=Flowed; Original >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 >>Message-ID: <uZJx#plEGHA.1384@TK2MSFTNGP11.phx.gbl> >>Newsgroups: microsoft.public.sqlserver.dts >>NNTP-Posting-Host: 159.121.113.234 >>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl >>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.dts:62903 >>X-Tomcat-NG: microsoft.public.sqlserver.dts >> >>Ok, >> >> >> >>The good news, I've created 6 tables from a single input table source. >>Each record of the input source had a primary key that was included in the > 6 >>destination tables as a "foreign key". >> >> >> >>Now I need to take the 6 tables and re-assemble them into the original > table >>format to be used as an interim data warehouse. >> >> >> >>The bad news, 4 of the original destination tables have a "one to one" >>relationship with the original input table. i.e. One record was created in >>the 4 destination tables for each record in the original "input" table >>record. Unfortunately, the other 2 original destination tables had a "one >>to many" relationship with the original "input" table. i.e. one original >>input record could create "1 to n" destination table records (i.e. the bad >>news part). >> >> >> >>Being relatively new to SSIS I'm a little uncertain of what SSIS tools I >>could use to tackle this problem. >> >> >> >>(01) 1st and easier problem . I need a control that can handle 6 inputs > and >>will allow me to select out all records from the six tables that have the >>same primary key. >> >> >> >>(02) 2nd not so easy problem . I need to assemble the data selected from > the >>6 input tables into a single output record. >> >> >> >>(02a) the wl_well_analysis input table (one of the 2 many to one input >>tables) could very well give me 6 records with "potable too little", >>"potable salty", "potable muddy", "potable odor", "potable colored" or >>"potable other" descriptions. These I need to translate into CHAR(1) > value >>columns in the output table . i.e. >> >> >> >>potable_too_little char(1) Y/N >> >>potable_salty char(1) Y/N >> >>potable_muddy char(1) Y/N >> >>potable_odor char(1) Y/N >> >>potable_colored char(1) Y/N >> >>potable_other varchar(30) Y/N >> >> >> >>Interesting challenge . I expect that this may just be a little beyond > SSIS's >>limits >> >>and that I might just have to limber up Visual Studio and C# to get this >>done in a >> >>timely manner. >> >> >> >>However, if I can do this in SSIS I'd really like to know. >> >> >> >>Thanks in advance! >> >> >> >>Barry >> >>in Oregon >> >> >> >
Michael,
I just learned while working thru the problems in another thread (see the "SSIS -OleDB Source as table view Hideously Slow ??") that the Data Flow - OleDb Source object provides an "Sql Command" data access option in addition to "Table/View" and a couple of other options. This is the downside to being new to SSIS and Sql Server and being under tremendous pressure to "get stuff done". I haven't tried the huge gnarley Sql Query with the 4 left outer joins into the "Sql Command" data access option expression but I expect it will work. Also the "Sql Command - Data Access Option" is essentially the answer to the 1st part of this thread's question. This realization gets me some 'traction' with the problem and I can get going again! Thanks to both you and and Peter Yang for you time and help. Its very much appreciated. Barry in Oregon [quoted text, click to view] "Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in message news:1y0qFgeFGHA.3944@TK2MSFTNGXA02.phx.gbl... > Hi Barry, > > Peter Yang is OOF and I am his backup. > > I am looking into this issue and will update you as soon as possible. > > Thank you for your patience and cooperation. > > > Sincerely yours, > > Michael Cheng > Microsoft Online Partner Support > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > ===================================================== > This posting is provided "AS IS" with no warranties, and confers no > rights. >
Hi Barry,
Peter Yang is OOF and I am his backup. I am looking into this issue and will update you as soon as possible. Thank you for your patience and cooperation. Sincerely yours, Michael Cheng Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Barry,
You are welcome! It's my pleasure to work with you on this thread and I believe other communities will also benefit from your invaluable inputs. If you have any questions or concerns next time, don't hesitate to let me know. We are always here to be of assistance! Sincerely yours, Michael Cheng Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |