Filtering Data Source based on info in destination

[kbverizon]

Ok here's my issue:

I have two connections setup one with my source and one with my destination. My source connection for simplistic sake has Table A and part of an earlier process I brought in Table B which now sits in my destination connection. The values of each are as follows:

Table A (Located on my Source Connection)
Name
A
B
C

Table B (Located on my Destination Connection)
Name
A
B

What I would like to do is part of my ole db source query filter the rows of Table A so that I only bring in those rows that match with Table B.

I simplified this process but the issue I am having is my real table A has millions of rows but I will only need to pull a subset of them, those that relate to my table b. I don't want to have to bring the entire set of data over every time I run the package.

Any help you can provide will be appreciated.

Thanks!

K.B.

 

[MDXer]

A simple join between the 2 tables as your source query would accomplish this.

Select
a.name
From TableA a
join TableB b on a.name = b.name

of course that is the simplified answer not knowing your process.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[kbverizon]

The issue that I have is my ole db source connection for TableA points to Server A and my data for table B is on Server B.

My ole db source connection can't see the data that exists on Server B. If I was using straight sql commands I would setup an odbc connection and use some sort of openquery method however, because I'm using SSIS I don't believe that will work.

 

[MDXer]

approximately how many records?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[kbverizon]

My table A has roughly 1.2 million but I only need to pull out a couple thousand at a time which will relate to the couple thousand rows in my Table B

 

[MDXer]

you will not be able to do this in your OLEDB connection but you do have a couple options.

1) Create an OLE DB source connection to the server that houses table b and construct a query to extract the releant records. You can then merge join these records which result in the matching records from table A passing through the merge. REMEMBER that a merge join requires sorted data sets so you must either order by your source queries and set the advanced properties or use a sort task.

2) Utilize a lookup task that extracts the relevant records from table b configure the columns that should be joined and then output all columns from Table A and just the name column from Table B name this b.name. Configure the error properties to ignore failures. Then add a conditional split to your dataflow and connect the output from the lkp to the conditional split. enter the Condition IsNull(b.Name) == FALSE and call the output Records to Process. You can now use this output to whatever other tasks you have.

Both of these options will work but option 2 will likely perform faster than option 1 option 2 will also probably require less resources.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck