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?

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:

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

Your first SSIS pakage?

I suggest you start small on a test system and make sure that it executes
all the way through. Start with a single Data Flow task on your Control Flow.
In the Data Flow, have a single Source and single Destination. Test run the
package.
Now add an Execute SQL task on the Control Flow *ahead* of the Data Flow.
Set it to TRUNCATE the destination table first.

Build your package slowly, testing after each component is added. The
package outlined in the prior post is somewhat involved.
=====
Todd C

Hello Todd,
My souce databases are ODBC not OLE DB. I assume I cant use a merge join as
i keep getting errors to set the IsSorted flag it true.


"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote

As you are probably aware, ODBC is ancient technology. It is not reocommended
for SSIS because when you go to deploy the pckage to the server, that server
must have the same DSN setup in order for it to work.

My suggestion: use SQL Native Client protocol if you are accessing a SQL
2005 instance, and bypass ODBC altogether.

OLE DB is the way to go. I have never used ODBC in SSIS for those very
reasons. Plus OLE DB has so much more functionality available as you have
discovered.

Good luck.
=====
Todd C


"Fsb" wrote:

Im trying to connect to a sybase 11 database that sits on a unix box. I dont
think I have a choice.



"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote

Do have a suggestion on how I would connect other then ODBC?

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote

Fsb wrote:
If you use an ORDER BY in your SELECT statement you will have to
set the IsSorted property manually (using the advanced editor).
This property will be only set automatically if you use the Sort
component.

hth,
Gerald

On Jul 23, 9:48*am, Gerald Aichholzer <gerald.aichhol... (AT) gmx (DOT) net>
wrote:
Don't know if this helps, but check to make sure if your old Sybase
drivers are compatible with 64 bit windows OS (what your server is
probably on). You might have to force the package to execute from a
command line (with a switch) to force it to execute in 32 bit mode…
I had yo do that to connect to a Sybase ASE 10.9 on a Solaris Server
once.