That's what I'm wondering, Ruben. I asked that in an earlier post and haven't seen a response yet.

I can't think of a good way to optimize that one. ...

If you want to go with Jeremy's suggestion, you can also throw an index on the Temp table to speed your searching. Though, it might not be worth it.

Regardless,...

Matt,

You actually have a separate DB to do the scrubbing?

Hmmm. I didn't think about that idea. Originally I was going to use staging tables in the DW, but...

The most common function I use in a WHERE clause is a convert. Usually "WHERE Convert(char(10),ModifiedOn,101) = Convert(char(10),GetDate(),101)". Usually because I'm in the middle of a data update...

Hmm. The flaw with the variable idea is if there are no records yet, even if you use an isnull(max(customerkey),0), it will fail the task as unable to set...

The mainframe data for customer names is close. Firstname is 10 characters, LastName is 15 characters and they keep middle initials (1 character) that we don't track in SQL....

You've hit the nail pretty much on the head.

We have two OLTP systems where from our SQL Server we export the data to the mainframe. The mainframe system has...

Have you looked into transactional replication?

There's also database mirroring that might help you.

You're saying you got it to work finally?

Yes, that is correct.

And yes, a customer can have more than one contract.

Lynn,

That's the problem. The OLTP doesn't have a Customers or Address table. All the customer info is stored in the OLTPContract table.

If I had separate tables in the...

I should mention, I don't keep AddressKey in the fact table since people can have multiple addresses. Mailing address, street address, etc.

Lynn,

Here are the create statements (sanitzed). The OLTP table does contain more columns but I'm only concerned with a small few.

Create Table OLTPContracts (ContractID int identity(1,1), FirstName varchar(25), LastName...

The easiest way to find errors is while you're in BIDS. When you execute a package in BIDS, it throws you into Debug Mode. When you're in Debug...

That one is pretty simple. You're trying to create something on the 2000 db that 2000 doesn't support. It could be a data type of a column...