Showing posts with label provide. Show all posts
Showing posts with label provide. Show all posts

Tuesday, March 27, 2012

Configuration files and variables

Hi ..

I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////

Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need

sam_idea wrote:

Hi ..

I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////

Have you looked in BOL? There's plenty in there regarding SSIS configurations.

Example here: http://www.sqlis.com/?26

sam_idea wrote:

Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need

The 2 most common methods for setting the value of a variable are using an expression (when the variable has EvaluateAsExpression=TRUE, example here: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx) or a script task. There are various tasks (e.g. Execute SQL Task, XML Task) and components (e.g. Rowcount transform, Recordset destination) that can set variable values also.

There is no concept of "passing variables between tasks". A variable is scoped to a container within the package and is accessible by that container and all its ancestral containers. You can pass values between tasks by storing them in variables (note the subtle difference here)

Here's some examples of how you can use variables:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/04/09/1265.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx

Here's a tip for you. Google is your friend and alot quicker than this forum: http://www.google.co.uk/search?hl=en&q=ssis+configurations&meta=

-Jamie

Tuesday, March 20, 2012

conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )
|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...

Im just hoping I've explained myself clearly now :)

Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!

Sunday, March 11, 2012

conditional query

Just wondering if someone could provide a brief example of how to do this. I
have a stored procedure and I need a condition where statement, for example
inputs are
@.name varchar(25)
@.color varchar(25)
if color is not 'none ' I want
select * from table1
where table1.name = @.name
and table1.color = @.color
if color is 'none' I want
select * from table1 where table1.name=@.name.
Thanks.
--
Paul G
Software engineer.One way
If @.color <> 'none'
select * from table1
where table1.name = @.name
Else
select * from table1
where table1.name = @.name
and table1.color = @.color
But there are lots of ways to to this type of processing. See
http://www.sommarskog.se/dyn-search.html
for a good discussion of ways to do this.
Tom
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:34367D41-4D61-4C01-ABC5-C5DD9DB969BD@.microsoft.com...
> Just wondering if someone could provide a brief example of how to do this.
> I
> have a stored procedure and I need a condition where statement, for
> example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||create proc myProc
@.name varchar(25),
@.color varchar(25)
as
if @.color != 'none'
select * from table1
where table1.name = @.name and table1.color = @.color
if @.color = 'none'
select * from table1
where table1.name=@.name
Linchi
"Paul" wrote:
> Just wondering if someone could provide a brief example of how to do this. I
> have a stored procedure and I need a condition where statement, for example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||This does it all in one query.
SELECT *
FROM table1
WHERE table1.name = @.name
AND (@.color = 'none '
OR table1.color = @.color)
Note that it MIGHT not perform as well as the alternatives using two
individual queries.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Jan 2008 11:17:02 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>Just wondering if someone could provide a brief example of how to do this. I
>have a stored procedure and I need a condition where statement, for example
>inputs are
>@.name varchar(25)
>@.color varchar(25)
>if color is not 'none ' I want
>select * from table1
>where table1.name = @.name
> and table1.color = @.color
>if color is 'none' I want
>select * from table1 where table1.name=@.name.
>Thanks.|||thanks for the responses. I simplified the example as I actually have
several items in the select statement as well as several in the where clause
as well as joins. I may have to use two seperate queries but may try to do it
with a sing query using OR if possible.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> This does it all in one query.
> SELECT *
> FROM table1
> WHERE table1.name = @.name
> AND (@.color = 'none '
> OR table1.color = @.color)
> Note that it MIGHT not perform as well as the alternatives using two
> individual queries.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 29 Jan 2008 11:17:02 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Just wondering if someone could provide a brief example of how to do this. I
> >have a stored procedure and I need a condition where statement, for example
> >
> >inputs are
> >@.name varchar(25)
> >@.color varchar(25)
> >
> >if color is not 'none ' I want
> >select * from table1
> >where table1.name = @.name
> > and table1.color = @.color
> >
> >if color is 'none' I want
> >select * from table1 where table1.name=@.name.
> >Thanks.
>|||On Tue, 29 Jan 2008 12:34:23 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>thanks for the responses. I simplified the example as I actually have
>several items in the select statement as well as several in the where clause
>as well as joins. I may have to use two seperate queries but may try to do it
>with a sing query using OR if possible.
Be aware that the warning about possible performance problems of the
all-in-one version becomes more apt as the query becomes more complex.
You may not have any problem, only trying it will determine that, but
be aware of the possibility.
Roy Harvey
Beacon Falls, CT|||ok thanks for the additional information. I have it running in the live
database, using 3 separate queries based on the condition of two input
parameters and the longest query is about 2 seconds. Fortunately the
database is relatively small and some indexes have been put into place to
enhance performance.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> On Tue, 29 Jan 2008 12:34:23 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >thanks for the responses. I simplified the example as I actually have
> >several items in the select statement as well as several in the where clause
> >as well as joins. I may have to use two seperate queries but may try to do it
> >with a sing query using OR if possible.
> Be aware that the warning about possible performance problems of the
> all-in-one version becomes more apt as the query becomes more complex.
> You may not have any problem, only trying it will determine that, but
> be aware of the possibility.
> Roy Harvey
> Beacon Falls, CT
>