best practice for lookup

say i have a customer.aspx that allows a user to enter in customer data.

on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customer

but some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"

Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?

I think this is more of a business decision. When you have to do reporting later on to track the sales, would you want to see "none" under sales rep?

|||

well, probably not, so assuming you don't - then is there even another way dealing with it assuming that in the underlying db, a customer must have an associated sales rep? in that reporting scenario, you'd have to write sql to filter out the "dummies" i guess.

|||

NuJoizey:

...assuming that in the underlying db, a customer must have an associated sales rep?

If you have to code according to that assumption, then you really have no choice than to use some sort of default value in your application with an Id of "999" or whichever you are comfortable with. Jjust make sure it doesnt get repeated. You can also use a negative value like -1 so there is no conflict with any number generated by SQL Server if your table grows.

|||

Another way to handle this is to store NULL in the sales rep ID. This is, in a sense, the more elegant solution since NULL means not known and you can do this and still maintain the foreign key relationship with your sales rep master table

However, many people don't like using NULL values because of their unintuitive behavior in select statements (eg, "select * from customer where SalesRepId <> 3" will NOT return rows where SalesRepId is NULL because NULL= unknown and if something is unknown it justmight be 3).

My advice is to follow ndinakar's suggestion and use a special value like -1

|||

great - thank you for the discussions

Best Practice - Lookup or SQL from Variable?

Hi,

I am pulling data from FoxPro tables into SQL 2005, and want to only pull new or changed rows. Accordingly each table in Fox has a column LastChangedDateTime, indicating the last time the row was updated, and I have a table in SQL which has one row per Fox table, listing the table name and the most recent data pulled into SQL.

In 2000 DTS I would have pulled the SQL datetime value into a package variable, then used a parameterized SQL statement with ".. WHERE LastChangedDateTime > ? " to select the rows I require.

In SSIS this approach does not seem to be possible, and the options are that I either use a variable for the entire SQL statement or, as the first SSIS tutorial suggests, use a lookup against the SQL table.

Gut feel is that the lookup will perform slower than creating the variable SQL and executing that (given that the source table is 13 million rows and rising, and I only want the last 100,000 or so from today).

What is considered best practice under these circumstances?

Also is it possible to write SSIS scripts in C# rather than VB.NET, as the syntax differences are driving me mad? ;-)

Thanks in advance,

Richard R

I would go with the DTS style method, it should work just fine. An Exec SQL Task can get the date value and store it in a variable. The variable can then be used in a parameterised query, in the same way as you did with DTS, but obviously using a Data Flow task, and the correct source. Saying that I have not tried it with FoxPro, but you will be using the same OLE-DB driver I assume so it should work fine. Parameter support is available in the OLE-DB Source, and the driver should support it if it did in DTS.

Using a lookup would not make sense as you will be doing far more work.
Using a variable for the command (with EvaluateAsExpression = True) is also perfectly valid, and sometimes the better choice, but for a simple query like this and since you have parameter support, I'd go with the former method, but there is nothing in it really.

The Script Task and Script Component both use Visual Studio for Applications (VSA), which means you get the power of .Net rather than a interpreted script language. Unfortunately VSA has only been implemented for VB.Net, there is no C# support. No idea if or when there will be either, but you are certainly not the first to raise the issue.

|||

Thanks Darren,

Sometimes it's good to check out a gut feeling - just in case the whole underlying system architecture has changed.

I'm not sure the FoxPro v9 driver OLEDB actually has parameter support, it didn't seem to work when I tried it, hence the original post. This is the first time I've had to interface to FoxPro, and there are definitely a few oddities about the process...

Regards,

Richard

Behavior of SSIS lookup transform on full cache setting with low computer memory

I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low.

Does SSIS

a) give an out of memory error of some sort

b) resort to a no caching or partial caching mode

c) maintain the full caching mode but will switch to using the paging file(virtual memory).

I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?

It won't do B unless you explicitly set it. I think it does C as part of the standard Windows memory management, but you might see A too if you are dealing with large data sets.

Your approach would make sense. I'd start by making sure that you are only caching the bare minimum reference set needed, though.