Discussion:
SSIS Question.
(too old to reply)
Fsb
2009-07-21 17:20:02 UTC
Permalink
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?
Todd C
2009-07-22 11:46:01 UTC
Permalink
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
Post by Fsb
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?
Fsb
2009-07-22 14:44:25 UTC
Permalink
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.
Post by Todd C
You have 2 source Databases, each with a Division table, and they *should*
match exactly, but may not.
ID Name
== ====
1 Div 1
2 Div 2
3 Div 3
ID Name
== ====
2 Div 2
3 Division three
4 Div 4
And these two tables are the source for a third, destination, table? The
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
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
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
Post by Fsb
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?
Todd C
2009-07-22 14:54:01 UTC
Permalink
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
Fsb
2009-07-22 17:59:07 UTC
Permalink
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.
Post by Todd C
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
Todd C
2009-07-22 18:24:01 UTC
Permalink
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
Post by Fsb
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.
Post by Todd C
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
Fsb
2009-07-22 18:34:56 UTC
Permalink
Im trying to connect to a sybase 11 database that sits on a unix box. I dont
think I have a choice.
Post by Todd C
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
Post by Fsb
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.
Post by Todd C
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
Fsb
2009-07-23 14:19:37 UTC
Permalink
Do have a suggestion on how I would connect other then ODBC?
Post by Todd C
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
Post by Fsb
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.
Post by Todd C
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
Gerald Aichholzer
2009-07-23 14:48:22 UTC
Permalink
Post by Fsb
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.
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
Willow Anne Grant
2009-07-23 23:18:59 UTC
Permalink
Post by Gerald Aichholzer
Post by Fsb
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.
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
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.
Fsb
2009-07-24 15:21:43 UTC
Permalink
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?
Post by Fsb
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.
Post by Todd C
You have 2 source Databases, each with a Division table, and they *should*
match exactly, but may not.
ID Name
== ====
1 Div 1
2 Div 2
3 Div 3
ID Name
== ====
2 Div 2
3 Division three
4 Div 4
And these two tables are the source for a third, destination, table? The
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
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
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
Post by Fsb
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?
Todd C
2009-08-11 12:45:01 UTC
Permalink
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
Post by Fsb
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
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?
Post by Fsb
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.
Post by Todd C
You have 2 source Databases, each with a Division table, and they *should*
match exactly, but may not.
ID Name
== ====
1 Div 1
2 Div 2
3 Div 3
ID Name
== ====
2 Div 2
3 Division three
4 Div 4
And these two tables are the source for a third, destination, table? The
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
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
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
Post by Fsb
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?
Loading...