locked
Best Practice - Lookup or SQL from Variable? RRS feed

  • Question

  • 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

     

    Thursday, February 2, 2006 12:29 PM

Answers

  • 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.

    Thursday, February 2, 2006 1:31 PM

All replies

  • 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.

    Thursday, February 2, 2006 1:31 PM
  •  

    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

     

    Thursday, February 2, 2006 1:58 PM