Script Task scripting help

Hi all,
I use oledb connection in the script just like as follows:

msConnStr = "Provider=SQLOLEDB;SERVER=" & msServer & ";DATABASE=" & msDb
msConn = New OleDbConnection(msConnStr)
msConn.Open()
ds = New DataSet
msData = New OleDbDataAdapter(msSqlStr, msConn)
rows = msData.Fill(ds, "TableName")

This command is working fine, I just need to ask if there is a method to do retry on the connection if the connection cannot be opened for reasons like server is temporary down? instead of just make the script task a failure. Thanks in advance.
Daren
There is no magic method on any of the opbjects, but you can easily use the Try Catch syntax to catch the rror, and warp tis within a loop to retry n times. Perhaps add a delay in the loop as well to pause between attempts. if successfull, just break out of the loop.

Script Task Logging

I seem to be doing something wrong I have a script task as follows:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim emptyBytes(0) As Byte

Dts.Log("The change in records between this load and the last load is " & CDbl(Dts.Variables("percent").Value) * 100 & "%.", 0, emptyBytes)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I have logging turned on for the package going to a text file with just errors checked. For this step I specifically checked the box for logging on this step (instead of inheriting logging) checked the logging provider, and on details checked ScriptTaskLogEntry.

The upshot is, the step runs fine but I have no entry in the log. HELP

Thanks,

LarryC

Well I guess I was wrong It is logging just fine. I kept looking in the Execution Results for the message and it wouldn't show up. Is there a way to get the message in the Execution Results?

|||

Try using FireInformation (Dts.Events.FireInformation)

|||

Smokin! For what I was doing that worked really great. I had a step I wanted to fail the package and I wanted to see the reason I was failing the package. Using the FireError method accomplished both things I wanted: To be able to see the message in progress when interactively running, and to see the message in the log.

As an unexpected bonus, I can log failure messages now without resorting to special considerations with logging. My script task can now inherit package events which are just logging errors Thanks a million.

The syntax I used for anyone else having a similar problem is:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim emptyBytes(0) As Byte

Dim msg As String = "The change in records between this load and the last load is " + CStr(CDbl(Dts.Variables("percent").Value) * 100) + "%."

Dts.Events.FireError(0, "Fail Package", msg, "", 0)

Dts.TaskResult = Dts.Results.Failure

End Sub

End Class

LarryC

Scope parameter

Hi
I have a report which includes a table as follows
table 1
table1_Group1
table1_Group2
In the footer of Group 1, I have a formula
=Sum(Fields!INVOICED.Value,"table1_Group2")
When I try and run the report, I keep getting the error
"The value expression for the textbox â'textbox21â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set."
As far as I can tell, table1_Group2 is a containing group?
The only thing that seems to work is putting the dataset name (or leaving it
out completely). The table name or group get this error.
Am I being stupid?
MacI think the problem is that table1_Group2 does not contain table1_Group1.
So a group can refer to something outside itself. But why not use this...?
=Sum(Fields!INVOICED.Value,"table1_Group1")
The sums should be the same, right? The multiple instances of table1_Group2
should correspond to the instance of table1_Group1 you're displaying.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> Hi
> I have a report which includes a table as follows
> table 1
> table1_Group1
> table1_Group2
> In the footer of Group 1, I have a formula
> =Sum(Fields!INVOICED.Value,"table1_Group2")
> When I try and run the report, I keep getting the error
> "The value expression for the textbox 'textbox21' has a scope parameter
> that
> is not valid for an aggregate function. The scope parameter must be set
> to a
> string constant that is equal to either the name of a containing group,
> the
> name of a containing data region, or the name of a data set."
> As far as I can tell, table1_Group2 is a containing group?
> The only thing that seems to work is putting the dataset name (or leaving
> it
> out completely). The table name or group get this error.
> Am I being stupid?
> Mac|||Ok that works although what I am trying to is this
I have a dataset which returns the amount invoiced by client
What I want the report to show is the breakdown of the top 20 clients and
then summarise the rest into a single line e.g.
Client 1 100,000
Client 2 90,000
.
.
.
Total top 20 190,000
Other Clients 60,000
Total Income 250,000
As group 2 holds the top 20 clients by using a filter, I wanted (hoping) to
sum the clients in that group only.
Using scope 1, I get the total for the entire dataset
"Jeff A. Stucker" wrote:
> I think the problem is that table1_Group2 does not contain table1_Group1.
> So a group can refer to something outside itself. But why not use this...?
> =Sum(Fields!INVOICED.Value,"table1_Group1")
> The sums should be the same, right? The multiple instances of table1_Group2
> should correspond to the instance of table1_Group1 you're displaying.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Mac" <Mac@.discussions.microsoft.com> wrote in message
> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> > Hi
> >
> > I have a report which includes a table as follows
> >
> > table 1
> > table1_Group1
> > table1_Group2
> >
> > In the footer of Group 1, I have a formula
> >
> > =Sum(Fields!INVOICED.Value,"table1_Group2")
> >
> > When I try and run the report, I keep getting the error
> >
> > "The value expression for the textbox 'textbox21' has a scope parameter
> > that
> > is not valid for an aggregate function. The scope parameter must be set
> > to a
> > string constant that is equal to either the name of a containing group,
> > the
> > name of a containing data region, or the name of a data set."
> >
> > As far as I can tell, table1_Group2 is a containing group?
> >
> > The only thing that seems to work is putting the dataset name (or leaving
> > it
> > out completely). The table name or group get this error.
> >
> > Am I being stupid?
> >
> > Mac
>
>|||Try playing with RunningValue in a formula or maybe a code block. Coupled
with the row count, you may be able to sum them up that way.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:CF9ED55F-C444-4342-B58F-E1863BF28BE3@.microsoft.com...
> Ok that works although what I am trying to is this
> I have a dataset which returns the amount invoiced by client
> What I want the report to show is the breakdown of the top 20 clients and
> then summarise the rest into a single line e.g.
> Client 1 100,000
> Client 2 90,000
> .
> .
> .
> Total top 20 190,000
> Other Clients 60,000
> Total Income 250,000
> As group 2 holds the top 20 clients by using a filter, I wanted (hoping)
> to
> sum the clients in that group only.
> Using scope 1, I get the total for the entire dataset
>
> "Jeff A. Stucker" wrote:
>> I think the problem is that table1_Group2 does not contain table1_Group1.
>> So a group can refer to something outside itself. But why not use
>> this...?
>> =Sum(Fields!INVOICED.Value,"table1_Group1")
>> The sums should be the same, right? The multiple instances of
>> table1_Group2
>> should correspond to the instance of table1_Group1 you're displaying.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Mac" <Mac@.discussions.microsoft.com> wrote in message
>> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
>> > Hi
>> >
>> > I have a report which includes a table as follows
>> >
>> > table 1
>> > table1_Group1
>> > table1_Group2
>> >
>> > In the footer of Group 1, I have a formula
>> >
>> > =Sum(Fields!INVOICED.Value,"table1_Group2")
>> >
>> > When I try and run the report, I keep getting the error
>> >
>> > "The value expression for the textbox 'textbox21' has a scope parameter
>> > that
>> > is not valid for an aggregate function. The scope parameter must be
>> > set
>> > to a
>> > string constant that is equal to either the name of a containing group,
>> > the
>> > name of a containing data region, or the name of a data set."
>> >
>> > As far as I can tell, table1_Group2 is a containing group?
>> >
>> > The only thing that seems to work is putting the dataset name (or
>> > leaving
>> > it
>> > out completely). The table name or group get this error.
>> >
>> > Am I being stupid?
>> >
>> > Mac
>>