Runtime Error after some pause

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report]

Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229

Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25

Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40

Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25

System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105

System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115

System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andysql

Runtime Error after some pause

Hello,

My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.

I think that this is some session time or something like that.
Am I right? How can fix it?

Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||

Back call without drillthrough report

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Back call without drillthrough report

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

|||

I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.

It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."

This only happens when I drill-through.

|||

I'm having this issue also. Did anyone ever find a solution? thanks

|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)

check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx

-nonoandy|||

i am also facing this problem...

any solutions for this?

|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this link
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)

But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.

Read this
http://support.microsoft.com/default.aspx/kb/933137
and download

Thanks!
Andy

Runtime error 713

Dear Gurus,

I have developed a package with VB6 & crystal report10. While installing it in Client machines i get error as "Run time Error 713".
I searched for it but only found that it is " Appication - Defined or Object define error 713"..Help me to solve this asap..

Thanx in Advance,
RevolutionWhen creating the Package did you add all the required dlls?
Also search here for your solution
http://support.businessobjects.com/

RunningValue help!!

the running value works on the preview of reporting services but when
i run the report on the server it displays zero all the time. plz tell
me how to solve it
thanx in advancei figured out the problem !!!

RunningTotal (for the birds)

How do I solve this RDL chart problem?

A bird watcher records the date each time he spots a new type of bird ('NewBird'). Likewise, he records the date of each time he spots a previously known type of bird ('OldBird'). Like this:

SpotDate Event
01/01/07 NewBird
01/01/07 NewBird
01/01/07 NewBird
01/02/07 OldBird
01/02/07 NewBird
01/02/07 OldBird
01/06/07 NewBird
01/06/07 OldBird
01/06/07 OldBird

I want to make an RDL chart that shows a running total of 'NewBird' to 'OldBird' along the date timeline. I am using SQL suite 2K5. I figure my "Category" field should be 'SpotDate', my series field should be 'Event,' and my "Data" field should be some kind of "=RunningTotal(Event, ?)". Is this correct?

And for that matter, how do I convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)?

Ok, it seems the answer (mostly) to my own question is this:

1) Create a select statement that provides the daily total (e.g. called "Tally") grouped by SpotDate and Event.

2) In the chart, add the SpotDate to the category, the Event to the series, and the "Tally" as the value

3) Edit the "Tally" properties so that its 'value' field shows '=RunningValue(Fields!Tally.Value, Sum, "chart1_SeriesGroup1")

Thats it! The name "chart1_SeriesGroup1" was chosen simply to match the auto-generated name Visual Studio gave to the "Event" series (you can find or edit this name by selecting the 'data' tab on chart properties. One final piece of advice: do not "add" a new dataset field that contains a "RunningValue" calculated value...it causes Visual Studio to crash every time you view a chart (whether or not you used it on the chart).

So now my only remaining issue, is the need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot) that I showed as example data earlier.

|||

> need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)

Set the x-axis to use scalar mode and make sure the category grouping expression returns DateTime objects (not data values as strings).

For more information, you may want to read the following section of a technical article on SSRS charts: http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic3

-- Robert

Running update syntax at set time

Hi

Is it possible for me to run an update syntax at a certain time say midnight for example?

I'm trying to update a bit field in my table (which acts as a checkbox in my Access front end), but only if three date fields are before todays date. The dates in question are in two other tables.

If so how would I go about doing this?

ThanksHave you looked at Books OnLine and read about the SQL Server Agent and scheduled jobs?|||What's Books OnLine, and where can I find it?|||What's Books OnLine, and where can I find it?Books Online = BOL = SQL Server Help - easily accessed via Query Analyser and Enterprise Manager.

BTW - are you sure you want to derive this overnight rather than at runtime or (possibly) at point of data capture?

Running totals in matrix?

Is it possible to create a row in a matrix that represents the running total
over time? Can I do this in the matrix itself or does this need to be
calculated in the dataset? If it's the latter, any ideas for how to do this
simply?
Example:
Jan Feb Mar
Monthly Sales 150 59 124
Cumulative Sales 150 209 333
Thanks!there is a running value in the aggregation expression...
On Jan 5, 1:36=A0am, jmann <jm...@.discussions.microsoft.com> wrote:
> Is it possible to create a row in a matrix that represents the running tot=al
> over time? =A0Can I do this in the matrix itself or does this need to be
> calculated in the dataset? =A0If it's the latter, any ideas for how to do =this
> simply?
> Example:
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Jan =A0= =A0 =A0 Feb =A0 =A0 =A0 Mar
> Monthly Sales =A0 =A0 =A0 =A0 =A0 150 =A0 =A0 =A0 =A059 =A0 =A0 =A0 =A0 ==A0124
> Cumulative Sales =A0 =A0 =A0150 =A0 =A0 =A0 =A0209 =A0 =A0 =A0 =A0333
> Thanks!|||On Jan 5, 4:53 am, Sridar K <srid...@.gmail.com> wrote:
> there is a running value in the aggregation expression...
> On Jan 5, 1:36 am, jmann <jm...@.discussions.microsoft.com> wrote:
> > Is it possible to create a row in a matrix that represents the running total
> > over time? Can I do this in the matrix itself or does this need to be
> > calculated in the dataset? If it's the latter, any ideas for how to do this
> > simply?
> > Example:
> > Jan Feb Mar
> > Monthly Sales 150 59 124
> > Cumulative Sales 150 209 333
> > Thanks!
The Running Total/Value expression is explained here:
http://msdn2.microsoft.com/en-us/library/aa255824.aspx
Just as an FYI, you would normally use a cursor or while loop to
provide this type of functionality in the stored procedure/query that
is sourcing the report. Of course, this option adds complexity and
poor performance, so I would suggest using the Running Value
expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'm trying the RunningTotal expression (Fields!Sales.Value, Sum, Nothing) and
getting the following error:
"The value expression for the textbox â'textbox6â' has a scope parameter that
is not valid for RunningValue, RowNumber or Previous. The scope parameter
must be set to a string constant that is equal to the name of a containing
group within the matrix â'matrix1â'." Not sure what this means. Any ideas?
Thanks!
"EMartinez" wrote:
> On Jan 5, 4:53 am, Sridar K <srid...@.gmail.com> wrote:
> > there is a running value in the aggregation expression...
> >
> > On Jan 5, 1:36 am, jmann <jm...@.discussions.microsoft.com> wrote:
> >
> > > Is it possible to create a row in a matrix that represents the running total
> > > over time? Can I do this in the matrix itself or does this need to be
> > > calculated in the dataset? If it's the latter, any ideas for how to do this
> > > simply?
> >
> > > Example:
> >
> > > Jan Feb Mar
> > > Monthly Sales 150 59 124
> > > Cumulative Sales 150 209 333
> >
> > > Thanks!
>
> The Running Total/Value expression is explained here:
> http://msdn2.microsoft.com/en-us/library/aa255824.aspx
> Just as an FYI, you would normally use a cursor or while loop to
> provide this type of functionality in the stored procedure/query that
> is sourcing the report. Of course, this option adds complexity and
> poor performance, so I would suggest using the Running Value
> expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System:tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System:tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

Running the same query on multiple servers

Hi, I periodically run the same query on multiple servers. Currently I
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...
|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan

Running sum

I'm not quite sure if i'm aiming for the impossible, but:
In a matrix, i have time (weeks) as columns, items as rows and qty as detail
and i would like to have a running sum for the columns.
This problems arise because i need to columns to be dynamic.
Any suggestions would be more than welcome
Example:
Records:
Item 1, week 1, qty 5
Item 1,week 2, qty 3
Item 1, week 3, qty 4
Matrix
week 1 week 2 week 3
Item 1 5 8 12I seem to have found the solution (RunningValue(Expression, Function,
Scope)).
Sorry for the inconvenience
"Claus Aaberg" <caa@.phaseone.com> wrote in message
news:%23TPAKwKcEHA.368@.TK2MSFTNGP10.phx.gbl...
> I'm not quite sure if i'm aiming for the impossible, but:
> In a matrix, i have time (weeks) as columns, items as rows and qty as
detail
> and i would like to have a running sum for the columns.
> This problems arise because i need to columns to be dynamic.
> Any suggestions would be more than welcome
> Example:
> Records:
> Item 1, week 1, qty 5
> Item 1,week 2, qty 3
> Item 1, week 3, qty 4
> Matrix
> week 1 week 2 week 3
> Item 1 5 8 12
>sql

running ssis package with ssis run time compoenents and sql server 2000...

Hi Jamie,

thanks.

I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs.

Thanks,

$wapnil

spattewar wrote:

I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs.

About what exactly?

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with a remote sql agent?

hi all,

thanks for the valuable information all the time!!! saved me a lot of time...

our team developes a system for text data improvement using ssis .

we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .

my question is:

what is the best way to do this?

if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?

or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.

do we need to install sqlserver on these "ssis servers"?

thanks for your replies!

To use SQL Server Agent in this scenario, all you need to do is ensure that SQL Server Agent is installed and accessible somewhere on the network. It need not be installed on your main SQL Server or the SSIS servers.|||

thanks for your reply! could you be more specific?

how do i use sql agent if it is installed on a different machine, to run the package on my computer?

i just found in kirk haselden's book a "caution" on this subject, it says:

"SQL Server Ajent service relies on the sql server service. if you intend to use sql ajent to schedule package execution, you need to have SqlServer installed on the machine as well, if you have some policies that don't allow SqlServer on the same box with integration services or otherwise govern the installation of SqlServer that prohibits you from running both servers on the same machine, you need to use the windows task scheduler or some other tool..."

can you explain this to me?

|||

That means that both services: SQL Server agent and SSIS needs to be running in the same machine where you are trying to execute he package via SS Agent. So you have 2 options, install the SS agent & SSIS in your computer or install the SSIS only and use other job scheduler tool.

May I ask why you want to schedule a job from your PC instead from a server?

|||

hi rafael, thanks, that as what i thought.

the reason to run the packages on a different machine is the load balancing- we are talking about a very heavy process, that is supposed to be working repeatedly, all the time. we don't want the SQL server performence to slow down.

what do i loose if i don't use the Agent to run these proccesses?

|||

rebecca M wrote:

what do i loose if i don't use the Agent to run these proccesses?

I would say the ability to put it under a schedule.

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

|||

Rafael Salas wrote:

I would say the ability to put it under a schedule.

and what about monitoring, logging, and such?

Rafael Salas wrote:

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

|||

rebecca M wrote:

and what about monitoring, logging, and such?

The looging information provided by SQL server agent would be about if the job was executed succesfully or not. Do not confuse with the SSIS logging that is define inside of the packages.

rebecca M wrote:

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

I *think* SQL Agent requires DB componnets and I think you would require a license on that box as well.

|||SQL Server Agent relies on the SQL Server Service. The SQL Server Service is part of the SQL Server Database Engine. Therefore, SQL Server Agent cannot operate without SQL Server Database Engine.sql

Running SSIS for the first time

Hello,

If my SSIS runs every day, how can I "know" that I'm running it for the first time?

I need an SQL Select1 statement to execute the first day that my flow starts (to get all the old data) and after that SQL Select2 statement should always execute (to get the latest data).

How is this possable?

Thank you.

You could :

1. Run a SQL Task with SQL Statement = conditional expression on a flag value,

2. Set the true Output=Select2 and the false output=Select1

3. Get your SQL select1 to also update a flag to true in the database

HTH

Kar

|||Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed.|||

Thanks for your replies.

Is it possable to have a bool flag (fetchall = true/false) that is set in the beginning when SSIS is first started?

Is it possable to do this without having to write to a database?

Thank you.

|||

Sure,

Taht would be easy to implement. You can create a boolean variable in the package and then assign its value via command line when executing the package (/SET option of DTEXEC command). Then you can build/change the where clause of the source components based on the value of that variable.

Don't forget to provide an intial value to the variable as the designer requires it for validation prupose.

|||

"Run a SQL Task with SQL Statement = conditional expression on a flag value"

How is this done? Can you please give me an example?

Thank you.

|||

Even if I use a bool value that is stored in a database, how do I set it back to 0 when the SSIS service is stopped?

Would you recommend keeping the bool value in a database or storing it as a variable in SSIS?

Thank you.

|||

You could put the whole query in a variable, let's call it MyQuery, then use an expression in MyQuery variable to actually build the query at run time based on the other boolean variable.

You would need to figure out how to control and maintain the boolean varaible. I sgugested to build a table to track the load history; but that is up to you.

|||Rafael Salas:

"I suggested to build a table to track the load history"

I'm sorry, but can you please explain this for me how it would solve my problem?

My SSIS is supposed to run every day as a scheduled task.

==Start SSIS process==

1day: Send all the old data (1000 rows)

2day: Send 1 row

3day: Send 1 row

..

.

==Stop SSIS process==

Since I'm new to SSIS, I'm still very confused how to implement this.

What would be the most simple solution?

Thanks a lot!!

|||

Rafael Salas:

"Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed. "

Lets say I do this and log everything I do in a table.

==Start SSIS process==

1. 1000 rows

2. 1 row

3. 1 row

4. 1 row

..

.

==Stop SSIS process==

==Start SSIS process==

How can I know now what to do (get 1000 or 1 row) by looking at the table above that I've logged?

Maby Im misunderstanding

Thank you.

|||

I'm guessing you have some sort of WHERE clause on your select to determine what rows to retrieve, perhaps a modified date. Something like "WHERE modified_date >= @.last_date".

You'd store the maximum retrieved value for modified_date in a control table. Each time the package runs, you'd retrieve the last modified_date value from the control table, and use that for the @.last_date parameter.

|||

That is right John.

What I am suggestion is to log the latest date or ID you loaded in the previous execution; then the subsequent execution will use that value to filter the rows (in a where clause) and pick only 'new' rows.

Perhaps, I am not understanding your requirement. What would be the difference between the 'first run' and the following ones?

|||

Thank you for your patience

As I understand it, SSIS can be configured as a scheduled task.

That means that, f.ex. if I want my SSIS flow to be run at 18:00 every day, my flow will be executed at that time day after day.

When the database guys setup my SSIS flow to be run as a schedules task, Im guessing they pick my flow and say "start". When that happens, the first day at 18:00 I want to send all my old data (1000rows).

However, the next day at 18:00 (and the next, and the next...) I only want to send an update (1row).

If the database guy "stops" my scheduled task (SQL went down for some reasons) and then "starts" my flow again, I need to send all my old data the first day at 18:00 (1000rows), then the next day at 18:00 (and the day after that...) only an update (1row) etc...

Im still very puzzled how to do this.

If you have any suggestions, please let me know.

Thanks very much!!

|||

I think that I still really don't understand what you are attempting to accomplish, because this scenario doesn't make much sense to me. If you've already moved your 1000 old rows, why does it matter if the scheduled task is restarted? The rows were already moved.

However, assuming that you really do want to reset everything because of the scheduled task being stopped, I don't see an easy way to handle it in SSIS. SSIS has no way of knowing if the the scheduled task was stopped or not. You'd have to do something on the SQL Server database engine side.

Again, you might want to better explain the reason why you are trying to do this, as that might help us to come up with a better solution.

|||

I agree...Please provide a better explanation of your scenario.

Anyway, you could have am Execute SQL Task at the begining of the dataflow to check whatever you want and then based on that result decide what kind of process it will be run (Incremental, Vs initial).

Running SSIS for the first time

Hello,

If my SSIS runs every day, how can I "know" that I'm running it for the first time?

I need an SQL Select1 statement to execute the first day that my flow starts (to get all the old data) and after that SQL Select2 statement should always execute (to get the latest data).

How is this possable?

Thank you.

You could :

1. Run a SQL Task with SQL Statement = conditional expression on a flag value,

2. Set the true Output=Select2 and the false output=Select1

3. Get your SQL select1 to also update a flag to true in the database

HTH

Kar

|||Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed.|||

Thanks for your replies.

Is it possable to have a bool flag (fetchall = true/false) that is set in the beginning when SSIS is first started?

Is it possable to do this without having to write to a database?

Thank you.

|||

Sure,

Taht would be easy to implement. You can create a boolean variable in the package and then assign its value via command line when executing the package (/SET option of DTEXEC command). Then you can build/change the where clause of the source components based on the value of that variable.

Don't forget to provide an intial value to the variable as the designer requires it for validation prupose.

|||

"Run a SQL Task with SQL Statement = conditional expression on a flag value"

How is this done? Can you please give me an example?

Thank you.

|||

Even if I use a bool value that is stored in a database, how do I set it back to 0 when the SSIS service is stopped?

Would you recommend keeping the bool value in a database or storing it as a variable in SSIS?

Thank you.

|||

You could put the whole query in a variable, let's call it MyQuery, then use an expression in MyQuery variable to actually build the query at run time based on the other boolean variable.

You would need to figure out how to control and maintain the boolean varaible. I sgugested to build a table to track the load history; but that is up to you.

|||Rafael Salas:

"I suggested to build a table to track the load history"

I'm sorry, but can you please explain this for me how it would solve my problem?

My SSIS is supposed to run every day as a scheduled task.

==Start SSIS process==

1day: Send all the old data (1000 rows)

2day: Send 1 row

3day: Send 1 row

..

.

==Stop SSIS process==

Since I'm new to SSIS, I'm still very confused how to implement this.

What would be the most simple solution?

Thanks a lot!!

|||

Rafael Salas:

"Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed. "

Lets say I do this and log everything I do in a table.

==Start SSIS process==

1. 1000 rows

2. 1 row

3. 1 row

4. 1 row

..

.

==Stop SSIS process==

==Start SSIS process==

How can I know now what to do (get 1000 or 1 row) by looking at the table above that I've logged?

Maby Im misunderstanding

Thank you.

|||

I'm guessing you have some sort of WHERE clause on your select to determine what rows to retrieve, perhaps a modified date. Something like "WHERE modified_date >= @.last_date".

You'd store the maximum retrieved value for modified_date in a control table. Each time the package runs, you'd retrieve the last modified_date value from the control table, and use that for the @.last_date parameter.

|||

That is right John.

What I am suggestion is to log the latest date or ID you loaded in the previous execution; then the subsequent execution will use that value to filter the rows (in a where clause) and pick only 'new' rows.

Perhaps, I am not understanding your requirement. What would be the difference between the 'first run' and the following ones?

|||

Thank you for your patience

As I understand it, SSIS can be configured as a scheduled task.

That means that, f.ex. if I want my SSIS flow to be run at 18:00 every day, my flow will be executed at that time day after day.

When the database guys setup my SSIS flow to be run as a schedules task, Im guessing they pick my flow and say "start". When that happens, the first day at 18:00 I want to send all my old data (1000rows).

However, the next day at 18:00 (and the next, and the next...) I only want to send an update (1row).

If the database guy "stops" my scheduled task (SQL went down for some reasons) and then "starts" my flow again, I need to send all my old data the first day at 18:00 (1000rows), then the next day at 18:00 (and the day after that...) only an update (1row) etc...

Im still very puzzled how to do this.

If you have any suggestions, please let me know.

Thanks very much!!

|||

I think that I still really don't understand what you are attempting to accomplish, because this scenario doesn't make much sense to me. If you've already moved your 1000 old rows, why does it matter if the scheduled task is restarted? The rows were already moved.

However, assuming that you really do want to reset everything because of the scheduled task being stopped, I don't see an easy way to handle it in SSIS. SSIS has no way of knowing if the the scheduled task was stopped or not. You'd have to do something on the SQL Server database engine side.

Again, you might want to better explain the reason why you are trying to do this, as that might help us to come up with a better solution.

|||

I agree...Please provide a better explanation of your scenario.

Anyway, you could have am Execute SQL Task at the begining of the dataflow to check whatever you want and then based on that result decide what kind of process it will be run (Incremental, Vs initial).

sql

Running SQL 2000 and SQL 2005 simultaneously

I have seen information on the web about running two versions of SQL at the same time on the same server but was wondering any tricks of the trade regarding it. The main recommendation that I have seen is to have SQL 2000 SP4 running first as the default instance (which is my current situation on a development server) and then install SQL 2005 as a named instance on it. Should the SQL 2005 installation place its files in a dedicated directory (ie, C:\SQL2005) as opposed to the default directory that the installation would normally write to, thus not overwriting any SQL 2000 system files? Any other recommendations?

Well directories of the core files (files needed to run SQL Server should be separated in different directories), for data and log files it depends. If you already established a physical structur of distributed data files and log files (to enhance performance due to split IO access) you should reuse this structure, placing the files on the disk using the same "logic" as you did for the other instance. If you did not do that yet, you can place the files wherever you want, I would just ot mix them up with the other (data-)files as it will ease the migration lateron if you keep them separately.

Jens K. Suessmeyer

http://www.sqlserver2005.de