Thursday, March 29, 2012
Configuration question
limit the number of report requests that execute at a given time? For
example, if I schedule 100 jobs to execute via a shared schedule at the same
time. How can I tell RS to only run 5 at a time until all 100 have
completed?
ThanksBy default RS will only take on work when it has the resources to do so.
Currently we rely on CPU usage to determine this. We will start working on
at most 2 * number of CPU. If the cpu usage stays low we will continue to
try and do more work. You can limit the total number RS will work on in the
RSReportServer.config file. The MaxQueueThreads tells RS at most how many
threads to use for processing reports. 0 means we will do as described
above, any other number will limit the total work that we can work on at one
time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"troark" <t.roark@.excite.com.n0sp@.m> wrote in message
news:#R4snWxfEHA.1188@.TK2MSFTNGP11.phx.gbl...
> I've looked for the answer to this question, but can't find it. How do
you
> limit the number of report requests that execute at a given time? For
> example, if I schedule 100 jobs to execute via a shared schedule at the
same
> time. How can I tell RS to only run 5 at a time until all 100 have
> completed?
> Thanks
>|||Great explaination. Thanks!
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OTHD4byfEHA.1428@.TK2MSFTNGP10.phx.gbl...
> By default RS will only take on work when it has the resources to do so.
> Currently we rely on CPU usage to determine this. We will start working
on
> at most 2 * number of CPU. If the cpu usage stays low we will continue to
> try and do more work. You can limit the total number RS will work on in
the
> RSReportServer.config file. The MaxQueueThreads tells RS at most how many
> threads to use for processing reports. 0 means we will do as described
> above, any other number will limit the total work that we can work on at
one
> time.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "troark" <t.roark@.excite.com.n0sp@.m> wrote in message
> news:#R4snWxfEHA.1188@.TK2MSFTNGP11.phx.gbl...
> > I've looked for the answer to this question, but can't find it. How do
> you
> > limit the number of report requests that execute at a given time? For
> > example, if I schedule 100 jobs to execute via a shared schedule at the
> same
> > time. How can I tell RS to only run 5 at a time until all 100 have
> > completed?
> >
> > Thanks
> >
> >
>sqlsql
Tuesday, March 20, 2012
Conditionally execute a task
Hallo
Is there a way to conditionally execute a task?
I got a task “Mail Send“ and I would like to execute it, just if the variable X (my message source) has a value.
I try in expressions “Disable” = len(@.[Benutzer::Msg] ) > 5 ? false : true, but it does not work.
Link with an an arrow the task before send mail task double click the green line and (you have Precedence Constraint Editor window) choose at Evaluation Operation the value Expression and type
@.x is not null or other expression
so if the expression is true SSIS will go and run sendmail task
|||Following ggciubuc post...
You can add an additional row to a scrit task in order to fail the package if the expression is the oposite of the first expression...
helped?
Regards
conditionally execute a subreport
displayed only when the subject of the report has a particular job title. The
data source for this subreport is a stored procedure, and in that procedure I
examine the job title and execute the code only for the specified job title,
but SQL Server goes ahead and opens all of the tables anyway, increasing
overhead on a report that already takes several hours to run.
A better solution would be to conditionally execute the subreport. Is there
a way to do that?
thanks, bethMy understanding of Reporting Services is that it is not meant to work that
way. This is not a client program; it is a server platform. There is
therefore no event-driven programming paradigm like you get in Access. It is
by design.
If you want to do the kind of thing you are talking about, you will need to
change your design. You can design two separate reports, or you can write
your own client program to conditionally load reports.
There is a sample demo of how to integrate reports in your application on
http://www.microsoft.com/sql/reporting. It shows how to use a browser control
in a Windows application.
sorry if this is not the answer you wanted.
Charles Kangai, MCT, MCDBA
"beth" wrote:
> I have a report that contains several subreports, one of which is meant to be
> displayed only when the subject of the report has a particular job title. The
> data source for this subreport is a stored procedure, and in that procedure I
> examine the job title and execute the code only for the specified job title,
> but SQL Server goes ahead and opens all of the tables anyway, increasing
> overhead on a report that already takes several hours to run.
> A better solution would be to conditionally execute the subreport. Is there
> a way to do that?
> thanks, beth|||Charles,
I will persue the alternatives you suggested - thank you.
I don't understand your point about client versus server platforms.
If I can conditionally toggle visibility of an object, why could I not toggle
say, an enable/disable property, of a subreport? If this is by design though,
I guess that's that.
I could try placing another stored procedure up front that examines the job
title, that will then conditionally execute the stored procedure that opens
all the
tables and collects the data; using this method, I could at least stop SQL
Server from opening tables it is not going to use. Not sure that will work
though.
thank you for your reply Charles...beth
"Charles Kangai" wrote:
> My understanding of Reporting Services is that it is not meant to work that
> way. This is not a client program; it is a server platform. There is
> therefore no event-driven programming paradigm like you get in Access. It is
> by design.
> If you want to do the kind of thing you are talking about, you will need to
> change your design. You can design two separate reports, or you can write
> your own client program to conditionally load reports.
> There is a sample demo of how to integrate reports in your application on
> http://www.microsoft.com/sql/reporting. It shows how to use a browser control
> in a Windows application.
> sorry if this is not the answer you wanted.
> Charles Kangai, MCT, MCDBA
Conditional Where Clause w/ Case Statement Possible?
Greetings,
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate
FROM mytable
WHERE
CASE WHEN @.myparam = 'ALL'
THEN
xdate >= '2007-09-01'
ELSE
xdate >= '2007-09-01' or
lob = @.myparm
END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
Any ideas?
Rob
Your query can be written this way, I think:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901')
AND (
@.myparm = 'ALL'
OR
lob = @.myparm
)
In general, you can probably write:
WHERE
(@.p = 'Option1' AND (<option 1 condition>))
OR
(@.p = 'Option 2' AND (<option 2 condition>))
OR
...
Be careful where parentheses go, so AND and OR don't associate differently than you want.
In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.
Steve Kass
Drew University
http://www.stevekass.com
|||This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,
If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..
Code Snippet
If @.myparam = 'ALL'
SELECT
uniqueID, lob, xdate
FROM
mytable
WHERE
xdate >= '2007-09-01'
ELSE
SELECT
uniqueID, lob, xdate
FROM
mytable
WHERE
xdate >= '2007-09-01' or lob = @.myparm
|||
Steve Kass wrote:
Your query can be written this way, I think:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901')
AND (
@.myparm = 'ALL'
OR
lob = @.myparm
)
In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.
Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.
I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901') and
lob LIKE @.myparm
then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.
Rob
|||
Manivannan.D.Sekaran wrote:
This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,
If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..
The problem with using the IF statement is the number of permutation when I have four variables.
IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'
(query 1)
IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'
(query 2)
IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'
(query 3)
...
I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.
Rob
|||
In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..
There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..
example,
Code Snippet
Declare @.SQL as Varchar(8000);
Declare @.Name as Varchar(8000);
Declare @.Type as Varchar(8000);
Declare @.Where as varchar(8000);
Set @.Name = null;
Set @.Type ='U'
Set @.SQL = 'Select * from Sysobjects Where 1=1'
Set @.Where = ''
IF @.Name is not NULL
Set @.Where = ' And Name=''' + @.Name + ''''
IF @.Type is NOT NULL
Set @.Where = @.Where + ' And Type=''' + @.Type + ''''
Exec (@.SQL + @.Where)
Code Snippet
Declare @.SQL as NVarchar(4000);
Declare @.Name as Varchar(8000);
Declare @.Type as Varchar(8000);
Declare @.Where as varchar(8000);
Set @.Name = NULL;
Set @.Type ='P'
Set @.SQL = 'Select * from Sysobjects Where 1=1'
Set @.Where = ''
IF @.Name is not NULL
Set @.Where = ' And Name=@.Name'
IF @.Type is NOT NULL
Set @.Where = @.Where + ' And Type=@.Type'
Set @.SQL = @.SQL + @.Where
Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type
|||Sorry, Rob. I misread your original intent, so let me try again. I think you want
1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.
First, a solution that assumes @.myparm is never NULL:
select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)
Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)
So this is like what a CASE statement would be. The structure is
where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...
The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.
Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).
SK
|||
Steve Kass wrote:
select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)SK
Steve,
Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.
I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.
Rob
Conditional Where Clause w/ Case Statement Possible?
Greetings,
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate
FROM mytable
WHERE
CASE WHEN @.myparam = 'ALL'
THEN
xdate >= '2007-09-01'
ELSE
xdate >= '2007-09-01' or
lob = @.myparm
END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
Any ideas?
Rob
Your query can be written this way, I think:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901')
AND (
@.myparm = 'ALL'
OR
lob = @.myparm
)
In general, you can probably write:
WHERE
(@.p = 'Option1' AND (<option 1 condition>))
OR
(@.p = 'Option 2' AND (<option 2 condition>))
OR
...
Be careful where parentheses go, so AND and OR don't associate differently than you want.
In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.
Steve Kass
Drew University
http://www.stevekass.com
|||This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,
If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..
Code Snippet
If @.myparam = 'ALL'
SELECT
uniqueID, lob, xdate
FROM
mytable
WHERE
xdate >= '2007-09-01'
ELSE
SELECT
uniqueID, lob, xdate
FROM
mytable
WHERE
xdate >= '2007-09-01' or lob = @.myparm
|||
Steve Kass wrote:
Your query can be written this way, I think:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901')
AND (
@.myparm = 'ALL'
OR
lob = @.myparm
)
In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.
Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.
I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:
SELECT uniqueID, lob, xdate
FROM mytable
WHERE (xdate >= '20070901') and
lob LIKE @.myparm
then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.
Rob
|||
Manivannan.D.Sekaran wrote:
This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,
If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..
The problem with using the IF statement is the number of permutation when I have four variables.
IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'
(query 1)
IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'
(query 2)
IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'
(query 3)
...
I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.
Rob
|||
In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..
There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..
example,
Code Snippet
Declare @.SQL as Varchar(8000);
Declare @.Name as Varchar(8000);
Declare @.Type as Varchar(8000);
Declare @.Where as varchar(8000);
Set @.Name = null;
Set @.Type ='U'
Set @.SQL = 'Select * from Sysobjects Where 1=1'
Set @.Where = ''
IF @.Name is not NULL
Set @.Where = ' And Name=''' + @.Name + ''''
IF @.Type is NOT NULL
Set @.Where = @.Where + ' And Type=''' + @.Type + ''''
Exec (@.SQL + @.Where)
Code Snippet
Declare @.SQL as NVarchar(4000);
Declare @.Name as Varchar(8000);
Declare @.Type as Varchar(8000);
Declare @.Where as varchar(8000);
Set @.Name = NULL;
Set @.Type ='P'
Set @.SQL = 'Select * from Sysobjects Where 1=1'
Set @.Where = ''
IF @.Name is not NULL
Set @.Where = ' And Name=@.Name'
IF @.Type is NOT NULL
Set @.Where = @.Where + ' And Type=@.Type'
Set @.SQL = @.SQL + @.Where
Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type
|||Sorry, Rob. I misread your original intent, so let me try again. I think you want
1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.
First, a solution that assumes @.myparm is never NULL:
select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)
Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)
So this is like what a CASE statement would be. The structure is
where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...
The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.
Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).
SK
|||
Steve Kass wrote:
select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)SK
Steve,
Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.
I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.
Rob
|||in t-sql you can have conditional where clausessample:
select * from mytable
where
mycol = case when @.i = 1 then 1 else 2 end
Monday, March 19, 2012
Conditional SQL Ajent Job execution
job should be running at any given time. If execution time exceeds 3
minutes - job should not start. Currently I have a flag in a
user-defined table that is set to 1 by the SP that is called by the
job when it begins, and reset back to 0 when it ends. The SP checks
the value of the flag, if it is currently = 1 then the SP exits.
The problem is that if the SP fails half-way through the flag is never
reset back to 0, thus preventing subsequent executions.
Is there a way to set the job to execute conditionally based on
whether or not another instance of same job is already running?I will probably add check the sysjobhistory to verify that
the job didn't fail:
select top 2 *
from sysjobhistory
where job_id = (select job_id from sysjobs
where name = <'Job Name'>)
order by run_date, convert(smalldatetime, (case len
(run_time)
when 1 then '00'+':'+'00'+':'+'0'+ right
(run_time,2)
when 2 then '00'+':'+'00'+':'+right
(run_time,2)
when 3 then '00'+':'+'0'+left(convert(char
(3), run_time), 1)+':'+right(run_time,2)
when 4 then '00'+':'+left(convert(char(4),
run_time), 2)+':'+right(run_time,2)
when 5 then '0'+left(convert(char(5),
run_time), 1)+':'+substring(convert(char(5), run_time), 2,
2)+':'+right(run_time,2)
when 6 then left(convert(char(6),
run_time), 2)+':'+substring(convert(char(6), run_time), 3,
2)+':'+right(run_time,2)
END)) desc
If the job failed, it should continue with the schedule:
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I have a job set to execute every 3 minutes. Only one
instance of the
>job should be running at any given time. If execution
time exceeds 3
>minutes - job should not start. Currently I have a flag
in a
>user-defined table that is set to 1 by the SP that is
called by the
>job when it begins, and reset back to 0 when it ends.
The SP checks
>the value of the flag, if it is currently = 1 then the SP
exits.
>The problem is that if the SP fails half-way through the
flag is never
>reset back to 0, thus preventing subsequent executions.
>Is there a way to set the job to execute conditionally
based on
>whether or not another instance of same job is already
running?
>.
>|||You shouldn't need such flags etc. Agent will not start a job if it is still executing (from last
time).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexey Aksyonenko" <Alexey.Aksyonenko@.coanetwork.com> wrote in message
news:1449e414.0309300914.2af4241@.posting.google.com...
> I have a job set to execute every 3 minutes. Only one instance of the
> job should be running at any given time. If execution time exceeds 3
> minutes - job should not start. Currently I have a flag in a
> user-defined table that is set to 1 by the SP that is called by the
> job when it begins, and reset back to 0 when it ends. The SP checks
> the value of the flag, if it is currently = 1 then the SP exits.
> The problem is that if the SP fails half-way through the flag is never
> reset back to 0, thus preventing subsequent executions.
> Is there a way to set the job to execute conditionally based on
> whether or not another instance of same job is already running?
Saturday, February 25, 2012
Conditional execution of first task
I have a situation where I'd like to conditionally execute the first task in a package based on the contents of a user variable.
If user variable "Var1" is false I want to begin execution with the first task.
If "Var1" is true I want to begin execution at the second task.
My first thought of course was SequenceContainer, but the same issue would exist for the first task in a SequenceContainer.
Is there a way to do this?
Thanks!
To conditionally execute a task you would use a constraint with an expression on it, but you need two tasks (containers) to do this. The ideal answer is to use something that does nothing, but offers a start point for the constraint. A sequence container works rather well, just drop it on and collpase it (the arrow on the right-hand side of the header). You can then link that to the real task, nothing should go inside that sequence container.|||I have created a sample solution inline with Darren's suggeston to use 'Sequence Container' for placing the precedence condition @. http://mystutter.blogspot.com/2006/03/sql-server-integration-services.html
Please let me know your comments.
Thanks,
Loonysan
Thank you. Your example was perfect.
I found another solution as well. I created a Sequence Container, inside that I created another Sequence Container and my first task. I put an expression constraint between those. Then I linked the outer Sequence Container to the second task.
I appreciate the help.
Conditional Execution in the Control Flow via Script Task
I'm trying to conditionally execute a dataflow based on the presence of a data file. If the data file isn't present, I'd like to execute gracefully without error.
Logic is as follows:
If FileExists Then
execute dataflow
Else
exit w/o error
End If
I've got the code ready to go, but I'm not sure how to do this conditional branch logic. Right now, the code calls the Dts.Results.Success / Failure. The problem, however, is Failure is exactly that... which doesn't result in the graceful exit I'm looking for.
Anyone have any ideas?
Thanks in advance.
Here is how I would do that:
Create a script task in your control flow to check if the file exists and write that result into a SSIS variable, let's say FileExists=1 -->exists; FileExtis=0 -->Does not exist. Then create a precedence constraint from the script task to the data flow. Then Edit the precedence constraint to use evaluation operation 'Expression and constraint'; Value 'success' and write the expression like @.[User::FileExists]==1.
This way the dataflow will be executed only if the sript task succed and the value of the variable FileExists is equal to one.
Rafael Salas
|||This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.|||Phil Brammer wrote:
This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.
You mean to have a 'Check if file exists' operation in the file system task?
Rafael Salas
|||Rafael Salas wrote:
Phil Brammer wrote: This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.
You mean to have a 'Check if file exists' operation in the file system task?
Rafael Salas
Indeed.|||
Good Idea. I followed your suggestion; for those interested in voting on that suggestion:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=231838
Rafael Salas
|||For those curious.. I documented the steps for future peoples that landed on this thread.dichotic.wordpress.com
Conditional drillthrough report?
different report based on the values contained in the main report?On Apr 10, 2:26 am, georges <geor...@.discussions.microsoft.com> wrote:
> Is it possible to execute conditional drillthrough reports i.e. to execute a
> different report based on the values contained in the main report?
This link might help.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/bba9dfc46640ab3/100ad792fec5beb6?lnk=st&q=reporting+services+conditional+drill&rnum=2#100ad792fec5beb6
Regards,
Enrique Martinez
Sr. Software Consultant
Conditional Container
May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.
1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task
2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)
3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.
IMHO, these features would be very important for developer productivity.
Marco Russo
http://sqljunkies.com/weblog/sqlbi
All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.
For #2, Copy/Paste might be a short term solution.
regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.
(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.
Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!
-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?
regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.
Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.
Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.
Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:
- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||
Ok here's my wish list,
1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)
2. Read only access to the whole package from componentmetadata, not just that related to the component.
3. Parallel For each loops. Performance.
4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop
5. Debug support for script component (not just the task)
6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.
7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.
8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance
9. IIS Log file connection both source and destination would be good. But would settle for source.
10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.
11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers
That'll do for now.
|||Thanks Simon. Excellent input. Thanks!Anyone else?
K|||
I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.
1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.
2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .
3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.
These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.
What everyone else think about these features.
Thanks
Sutha
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx
-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!
It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.
-Jamie
Friday, February 17, 2012
Concurrency problem
time.Both concurrent requests on SQL server 2000 fail with an error message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC
SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access
denied.
The SQL requests are from ASP page on a shared SQL server. How do I enable
several users execute my sql statements at the same time (only reading
data). Is locking
causing this problem or something else? Should I use WITH(UNLOCK)?
Jared Burford
Computer IT/Support
support@.megatech-pc.comThe error has nothing to do with locking. Check the connection and if the
users have rights to access the databse and the table.
AMB
"Jared Burford" wrote:
> Users A and B execute a simple "SELECT FROM ..." statement at the same
> time.Both concurrent requests on SQL server 2000 fail with an error messag
e:
> Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC
> SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access
> denied.
> The SQL requests are from ASP page on a shared SQL server. How do I enable
> several users execute my sql statements at the same time (only reading
> data). Is locking
> causing this problem or something else? Should I use WITH(UNLOCK)?
>
> Jared Burford
> Computer IT/Support
> support@.megatech-pc.com
>
>|||It works ok with one, but fails with more than one user
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
> The error has nothing to do with locking. Check the connection and if the
> users have rights to access the databse and the table.
>
> AMB
> "Jared Burford" wrote:
>|||just a quick note: both are trying to access the same table which takes in
exxcess of 30 secs.
"Jared Burford" <support@.megatech-pc.com> wrote in message
news:eqGN$IkVFHA.548@.tk2msftngp13.phx.gbl...
> It works ok with one, but fails with more than one user
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
>|||Do u think SELECT ... WITH(NOLOCK) ... can help?
If u only want to do dirty read, i think this is fine for u.
"Jared Burford" wrote:
> just a quick note: both are trying to access the same table which takes in
> exxcess of 30 secs.
> "Jared Burford" <support@.megatech-pc.com> wrote in message
> news:eqGN$IkVFHA.548@.tk2msftngp13.phx.gbl...
>
>|||Problem solved... I just open tabe, read it, and close, it, set the flag on
that one so it won't check it next time, and move to the next, and repeat.
This way other uses can read the same table without getting stuck.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
> The error has nothing to do with locking. Check the connection and if the
> users have rights to access the databse and the table.
>
> AMB
> "Jared Burford" wrote:
>
Tuesday, February 14, 2012
Concurrency
Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.
For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.
Refer to Books Online, Topic: Transactions
|||Other good topics in the BOL are "Isolation mode" and locking hints.
Jens K. Suessmeyer.
http://www.sqlserver2005.de