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

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.


--------------------

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


"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

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

"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

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.

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


"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

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.