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

Beginner question - working with URL variables

I want to default one of my report parameters to a value passed in via the
URL. How do i reference a URL variable in the report designer?
TIA,
BrianThanks for your help Teros. I think I tried what you are suggesting, but
everytime i run the report, passing in the URL variable never seems to have
any affect on the value.
Here's what i got:
Parameter1: This is the variable that i will want to change with a URL
variable, default value is blank, no prompt.
Parameter2: Changeable parameter on the report, defaults to
Parameter1.value.
Then when I call the report, i try passing in a value for Parameter1:
http://127.0.0.1/ReportServer?ReportName&Parameter1=22
The report comes up with Parameter2 still showing blank.
Brian
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> If you have your url such as:
> http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> Where ReportNameX is the report and categoryY is your parameter, you
should be able to reference it within your report by setting up a
coresponding parameter named categoryY and then using the usual
Parameters!categoryY.Value . So to have it default that value for a second
parameter, I would think you'd set up your URL passed parameter, then call
that with the Parameters!blah.value in your default value spot for the 2nd
parameter.
> Hope that helps (and works! :) )
> - T
> "G" wrote:
> > I want to default one of my report parameters to a value passed in via
the
> > URL. How do i reference a URL variable in the report designer?
> >
> > TIA,
> > Brian
> >
> >
> >|||That seems ridiculously complicated for something that should be common
practice. Passing a URL variable into a report and manipulating that
variable has to be a trivial task.
I must be doing something wrong, but not a clue what.
> Hmm... Might be that parameters can't control parameters in the same
report? Quick workaround might be to have a "shell" report that brings in
the URL parameter, then have your main report as a subreport in the shell
(taking up the entire space, so it's transparent to the end user) and pass
the second parameter based on the outer first?
> Did that make any sense?
> - T
> "G" wrote:
> > Thanks for your help Teros. I think I tried what you are suggesting, but
> > everytime i run the report, passing in the URL variable never seems to
have
> > any affect on the value.
> >
> > Here's what i got:
> >
> > Parameter1: This is the variable that i will want to change with a URL
> > variable, default value is blank, no prompt.
> > Parameter2: Changeable parameter on the report, defaults to
> > Parameter1.value.
> >
> > Then when I call the report, i try passing in a value for Parameter1:
> > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> >
> > The report comes up with Parameter2 still showing blank.
> >
> > Brian
> >
> >
> > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > If you have your url such as:
> > >
> > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > >
> > > Where ReportNameX is the report and categoryY is your parameter, you
> > should be able to reference it within your report by setting up a
> > coresponding parameter named categoryY and then using the usual
> > Parameters!categoryY.Value . So to have it default that value for a
second
> > parameter, I would think you'd set up your URL passed parameter, then
call
> > that with the Parameters!blah.value in your default value spot for the
2nd
> > parameter.
> > >
> > > Hope that helps (and works! :) )
> > > - T
> > >
> > > "G" wrote:
> > >
> > > > I want to default one of my report parameters to a value passed in
via
> > the
> > > > URL. How do i reference a URL variable in the report designer?
> > > >
> > > > TIA,
> > > > Brian
> > > >
> > > >
> > > >
> >
> >
> >|||I think that you have the concept of how to do this not quite right. You are
not wanting to reference the variable passed on the url, you want the url to
set the report parameter you have already defined. Steps to get this
working. First create a report with report parameters and make sure the
report is working.
Easiest is just have a blank report with two text boxes. Set the textboxes
as an expression. Using the expression builder you can set it to you
parameter. Open up the report, you will be prompted for a parameter. Fill it
in and make sure the parameter shows up on your report. Now try it via a
URL.
Bruce L-C
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:O5yEaYTWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> That seems ridiculously complicated for something that should be common
> practice. Passing a URL variable into a report and manipulating that
> variable has to be a trivial task.
> I must be doing something wrong, but not a clue what.
>
> > Hmm... Might be that parameters can't control parameters in the same
> report? Quick workaround might be to have a "shell" report that brings in
> the URL parameter, then have your main report as a subreport in the shell
> (taking up the entire space, so it's transparent to the end user) and pass
> the second parameter based on the outer first?
> >
> > Did that make any sense?
> > - T
> >
> > "G" wrote:
> >
> > > Thanks for your help Teros. I think I tried what you are suggesting,
but
> > > everytime i run the report, passing in the URL variable never seems to
> have
> > > any affect on the value.
> > >
> > > Here's what i got:
> > >
> > > Parameter1: This is the variable that i will want to change with a URL
> > > variable, default value is blank, no prompt.
> > > Parameter2: Changeable parameter on the report, defaults to
> > > Parameter1.value.
> > >
> > > Then when I call the report, i try passing in a value for Parameter1:
> > > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> > >
> > > The report comes up with Parameter2 still showing blank.
> > >
> > > Brian
> > >
> > >
> > > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > > If you have your url such as:
> > > >
> > > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > > >
> > > > Where ReportNameX is the report and categoryY is your parameter, you
> > > should be able to reference it within your report by setting up a
> > > coresponding parameter named categoryY and then using the usual
> > > Parameters!categoryY.Value . So to have it default that value for a
> second
> > > parameter, I would think you'd set up your URL passed parameter, then
> call
> > > that with the Parameters!blah.value in your default value spot for the
> 2nd
> > > parameter.
> > > >
> > > > Hope that helps (and works! :) )
> > > > - T
> > > >
> > > > "G" wrote:
> > > >
> > > > > I want to default one of my report parameters to a value passed in
> via
> > > the
> > > > > URL. How do i reference a URL variable in the report designer?
> > > > >
> > > > > TIA,
> > > > > Brian
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>

Beginner looking for help

Hi All,

I'm not very good at SQL but I want to finish my ASP project and have run into a little problem. I have a variable that I formatted represent a nice comma delimited character list with all "words" in single quotes ('A','B','C'). I was going to pass this variable to a SELECT statement -

SELECT col FROM table WHERE item IN (<my variable>)

Am I doing this right?

Thanks!

BI don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.|||Originally posted by cvandemaele
I don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.

Thanks. I learned the dynamic SQL way last night shortly after posing this.

I appreciate your resonse!