Lookup transform with multiple matches

Please indulge my ignorance, as I have only been using SSIS for a couple of weeks.
I'm trying to create a data warehouse using two input tables.
A column needs to be added to one table by using a lookup into the second table.
SSIS seems to handle the "no matches" and "single match" cases perfectly.
I can't for the life of me figure out how to properly handle multiple matches.
SSIS defaults to the first match, but I need to compute the "best" match.

Many thanks in advance
Scott!

What's the criteria for the "best" match? Knowing the criteria would make it easier to help |||Here are some specifics:
Table A contains repair records.
Table B contains inspection records.
Each piece of equipment can have multiple repairs and many more inspections.
A lookup can be performed based on the equipment number.

I need to determine which repair immediately proceeds each inspection.
This can be found by computing the smallest positive date difference.

Programmactically, I would normally do this with nested loops.
Using SSIS, I suppose it could also be done this way, using a foreach loop
and a condition...
|||

Interesting problem

I haven't tested this, but could you join the rows in SQL on the equipment, calc the date difference and order by it, and use a rank function to get the first row?

Looking for suggestions Cluster or Mirror

Hello,

We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.

John

Fail-over clustering and database mirroring are both high availability solutions that don't have any direct effect on performance. Fail-over clustering relies on shared external storage between the nodes (which is a potential single point of failure), and requires higher-end hardware in most cases. It works at the instance level. Failing over a node in the cluster typically takes 1-2 minutes on a large, active database instance.

Database mirroring works at the database level(not the instance level). There are two copies of the data (one on the Principle and one on the Mirror), so you need twice the storage space. Only the Principal database is available to service clients. If you want automatic fail-over with DB Mirroring, you need a Witness Server. You have to be running in Synchronous mode with Saftey turned on to get automatic fail-over with DB Mirroring. Database fail-over with mirroring is more like 10-15 seconds.

If you are doing Replication, it will might be easier to do fail-over clustering.

|||

here are some resources that may be helpful in your decision-making:

Failover Clustering white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

SQL Server 2005 High Availability Resources:

http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx

Looking for suggestions Cluster or Mirror

Hello,

We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.

John

Fail-over clustering and database mirroring are both high availability solutions that don't have any direct effect on performance. Fail-over clustering relies on shared external storage between the nodes (which is a potential single point of failure), and requires higher-end hardware in most cases. It works at the instance level. Failing over a node in the cluster typically takes 1-2 minutes on a large, active database instance.

Database mirroring works at the database level(not the instance level). There are two copies of the data (one on the Principle and one on the Mirror), so you need twice the storage space. Only the Principal database is available to service clients. If you want automatic fail-over with DB Mirroring, you need a Witness Server. You have to be running in Synchronous mode with Saftey turned on to get automatic fail-over with DB Mirroring. Database fail-over with mirroring is more like 10-15 seconds.

If you are doing Replication, it will might be easier to do fail-over clustering.

|||

here are some resources that may be helpful in your decision-making:

Failover Clustering white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

SQL Server 2005 High Availability Resources:

http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx

Looking for some advice.....

I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.You could use a data flow to read the flat file, and use an OLEDB Destination to save it to a "temp" table. Use an Execute SQL to update the temp table, then use a second data flow to retrieve the data and send it to an OLE DB Command to call your stored procedure.