SSIS Question.

#11

Hell Tood,

I've got some of this working but am stuck on how to send an email.

Im currenlty at a point where i have a conditional split spliting the data
as follows:
1. no match in table 1
2. no match in table 2
3. matched in both tables.

now i dont know how to send an email indicating missing invalid data in
point 1 and 2. would i have to populate a variable?

"Fsb" <Fountainhead7 (AT) hotmail (DOT) com> wrote

Quote:

Thank you Todd!

This sounds like what im looking for....now i have to wrap my mind around
using all the components you mentioned.

This is my first SSIS package so im learning as i go, nothing like DTS
thats for sure.

I will let you know if i have more questions along the way.

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote in message
news:60D02ABC-10FD-4B13-BA47-88B04F709EE4 (AT) microsoft (DOT) com...
Let me make sure I understand your issue:

You have 2 source Databases, each with a Division table, and they
*should*
match exactly, but may not.

Suppose you have in Db 1:
ID Name
== ====
1 Div 1
2 Div 2
3 Div 3

And in Db 2:
ID Name
== ====
2 Div 2
3 Division three
4 Div 4

And these two tables are the source for a third, destination, table? The
contents of which should be:
ID Name
== ====
1 Div 1
2 Div 2
3 <not sure what you want here to resolve the conflict
4 Div 4

Sounds like a job for a Merge Join transform in SSIS, using the Full
Outer
Join option. That will get you the results above.

But you also want an email sent to the DBA if the records don't match. So
you may need to include all fields from both tables to form an merged set
looking like:

ID1 Name1 ID2 Name2
== ==== == ====
1 Div 1 NULL NULL
2 Div 2 2 Div 2
2 Div 3 3 Division Three
NULL NULL 4 Div 4

You may need to a Multi Cast so that you have two pipelines to work with.
One will go to the destination table, and one will be processed as
described
below:
Send it through a Conditional Split, and look for NULL in ID1 or ID2
columns, indicating that records are missing from one table or the other.
Also check that Name1 equals Name2.

Is this what you are looking for? If not, or you need additional help,
post
back here.

=====
Todd C

"Fsb" wrote:

Hello,

I have 2 databases sources that have the same tables, columns and
layouts.
One database for Canada and one for USA.

in both these databases I have a Division Table.

I want to make sure that records in both tables match eachother. If not
an
email is sent to the DBA.
if the records match I want to update exitsing records and insert new
reocrds into the destination DB.

Can someone send me on the right path?

#12

Hello Fsb:
I kind of lost track of this threadn for a while, sorry.

So you have data rows in a pipeline and you want to sent them someone in an
email. Yes?

I think the easiest way is to send them to a flat file destination (like
..txt), or in your case, two flat files. Then set up a Send Mail task on the
Control Flow to execute after the Data Flow. In the Send Mail, have it attach
the two flat files.

HTH
=====
Todd C

"Fsb" wrote:

Quote:

Hell Tood,

I've got some of this working but am stuck on how to send an email.

Im currenlty at a point where i have a conditional split spliting the data
as follows:
1. no match in table 1
2. no match in table 2
3. matched in both tables.

now i dont know how to send an email indicating missing invalid data in
point 1 and 2. would i have to populate a variable?

"Fsb" <Fountainhead7 (AT) hotmail (DOT) com> wrote in message
news:OMG%23trtCKHA.4432 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
Thank you Todd!

This sounds like what im looking for....now i have to wrap my mind around
using all the components you mentioned.

This is my first SSIS package so im learning as i go, nothing like DTS
thats for sure.

I will let you know if i have more questions along the way.

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote in message
news:60D02ABC-10FD-4B13-BA47-88B04F709EE4 (AT) microsoft (DOT) com...
Let me make sure I understand your issue:

You have 2 source Databases, each with a Division table, and they
*should*
match exactly, but may not.

Suppose you have in Db 1:
ID Name
== ====
1 Div 1
2 Div 2
3 Div 3

And in Db 2:
ID Name
== ====
2 Div 2
3 Division three
4 Div 4

And these two tables are the source for a third, destination, table? The
contents of which should be:
ID Name
== ====
1 Div 1
2 Div 2
3 <not sure what you want here to resolve the conflict
4 Div 4

Sounds like a job for a Merge Join transform in SSIS, using the Full
Outer
Join option. That will get you the results above.

But you also want an email sent to the DBA if the records don't match. So
you may need to include all fields from both tables to form an merged set
looking like:

ID1 Name1 ID2 Name2
== ==== == ====
1 Div 1 NULL NULL
2 Div 2 2 Div 2
2 Div 3 3 Division Three
NULL NULL 4 Div 4

You may need to a Multi Cast so that you have two pipelines to work with.
One will go to the destination table, and one will be processed as
described
below:
Send it through a Conditional Split, and look for NULL in ID1 or ID2
columns, indicating that records are missing from one table or the other.
Also check that Name1 equals Name2.

Is this what you are looking for? If not, or you need additional help,
post
back here.

=====
Todd C

"Fsb" wrote:

Hello,

I have 2 databases sources that have the same tables, columns and
layouts.
One database for Canada and one for USA.

in both these databases I have a Division Table.

I want to make sure that records in both tables match eachother. If not
an
email is sent to the DBA.
if the records match I want to update exitsing records and insert new
reocrds into the destination DB.

Can someone send me on the right path?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS Question.

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: SSIS Question. - 07-24-2009 , 10:21 AM

Re: SSIS Question. - 08-11-2009 , 07:45 AM