convert access IIF to sql in order by statement

ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?I;m not sure I got the IIF true/False right...I went with IIF(expr, true, false)...if it's backwards just flip it in the code...

ORDER BY Name
-- , IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
, CASE WHEN Risk.Calc LIKE 'H%' THEN 1
WHEN Riks.Calc LIKE 'M%' THEN 2
ELSE 3
END
, Risk.Tile|||i didnt think you could use IIF in sql|||I think brett was just reiterating your iif and submitting a case example as the replacement.|||yea thats what i thought after i looked at it, thanks for the help brett|||The "--" indicate that it's a comment line...good luck..

did it work?

Converfting a 2000 db to 2005.

I have a 2000 db that I've restored to a 2005 machine. When it restored it
restored as a version 80 db (2000 I would assume). To convert it to 2005 do
I just go into options and change the compatibility to 90?
Will it tell me if there are things that can't be converted?
TIA - Jeff.
It actually upgraded it to 2005 when you restored it. But upgraded dbs are
kept in 80 (2000) computability mode by default. Just change it to 9.0 and
you are all set.
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:ObOhzGicHHA.588@.TK2MSFTNGP06.phx.gbl...
>I have a 2000 db that I've restored to a 2005 machine. When it restored it
>restored as a version 80 db (2000 I would assume). To convert it to 2005 do
>I just go into options and change the compatibility to 90?
> Will it tell me if there are things that can't be converted?
> TIA - Jeff.
>

Controling Names of Agents

Hello there
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>

Control Flow design question

hi all of you,

I haven’t idea if the following description is a issue or not, anyway:

I begin from Control Flow layer.

I’ve created a sequence container and inside I’ve got two groups, one own a sql task and another one own a Data Flow task. Both are linked for a completion conector. Up to here everything is fine. But when I collapse my sequence container the arrow remains there for these tasks and you can see the sequence container “closed” and the arrow lonely.

Not very esthetic, not practical.

Any clarification or though will be as usual welcomed

I've seen similar problems in the past. I think this is termed a "ghost" - meaning that the UI is getting lost somehow.

The SSIS team will want to know about this so bug it at Microsoft Connect with a screenshot! And if you post a repro - even better!

-Jamie

|||

hi Jamie,

Well, at first if you collapse both groups and then the sequence container the arrow is not visible at all..

f..stuff

|||

I don't doubt it. These things are hard to reproduce and we shouldn't try and explain why they happen. If you can repro it on demand tho - you should bug it.

-Jamie

|||

Maybe the problem is that we're assuming that BIDS have capabilities such as the ones that shows VISIO or something like that. Error.

see you

|||

Did you raise the bug?

|||No, laziness

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
Jens
Its been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE Column Weighting in FTS 2005

Hi there,
Is it possible with SQL Server 2005 to add weighting to a column in a
CONTAINSTABLE or FULLTEXTTABLE ?
I've got the statment :
SELECT ftt.RANK, [content].id, [content].title, [content].content_data FROM
[content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
What I need to do is ensure that matches in the title column have much
higher ranking than matches in content_data.
I've seen similar posts, but have not seen a definitive yes/no answer for
2005.
Many thanks,
Larry.
Hello Larry,
Do two searches, we also include the job title in the content data. This
is to because the searhc has to match in at least on column, not across columns.
i.e. a search for SQL AND server AND DBA with column title = "SQL DBA" and
content of "SQL Server" would not return as result unless you combined the
data.
SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content]
left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON fttitle.[key]=[content].id
INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
ftt.[key]=[content].id
ORDER BY
fttitle.rank desc, ftt.rank desc

>
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title,
> content_data), 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY
> ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer
> for 2005.
> Many thanks,
> Larry.
|||Hi Simon,
Thanks for the reply.
Does having multiple joins have a big impact on performance ?
Would I need to have another join for every field I wanted to search on in
the table ?
Is there anyway of combining the rankings ?
Many thanks,
Larry.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
> Hello Larry,
> Do two searches, we also include the job title in the content data. This
> is to because the searhc has to match in at least on column, not across
> columns. i.e. a search for SQL AND server AND DBA with column title = "SQL
> DBA" and content of "SQL Server" would not return as result unless you
> combined the data.
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON
> fttitle.[key]=[content].id
> INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
> ftt.[key]=[content].id ORDER BY
> fttitle.rank desc, ftt.rank desc
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Here is an example:
SELECT [KEY], SUM(Rank) AS WeightedRank
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Address, 'Street')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Realtor, 'Street')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Notes, 'Street')
) as x
GROUP BY [KEY]
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
> 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer for
> 2005.
> Many thanks,
> Larry.
>
|||Hello Larry,
It depends on the searches and the data in the index. It can do.
But generally the job title will be small and so should be a quck search.
We combine the rankings by in my example doing fttitle.RANK * 100 + ftt.RANK.
This means that a match in the job title will always rank above a match in
the content. This is because rank goes from 0-100
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Hi Simon,
> Thanks for the reply.
> Does having multiple joins have a big impact on performance ?
> Would I need to have another join for every field I wanted to search
> on in the table ?
> Is there anyway of combining the rankings ?
> Many thanks,
> Larry.
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
|||Thanks for that Hilary,
This appears to be pretty close to what I'm after.
The only question I now have is how best to return the actual data in one
SQL statement
The following would give me what I need, but is this the most efficient way
of achieving this ?
SELECT [KEY], SUM(RANK) AS WeightedRank, content.title, content.abstract
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(content, (title,
keywords), 'Content')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(content,(abstract),
'Content')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(content,(content_data),
'Content')
) AS x INNER JOIN content ON content.id = [KEY]
GROUP BY [KEY], content.title, content.abstract
ORDER BY WeightedRank DESC
Many thanks,
Larry.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23c$xVODYHHA.984@.TK2MSFTNGP04.phx.gbl...
> Here is an example:
> SELECT [KEY], SUM(Rank) AS WeightedRank
> FROM
> (
> SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Address, 'Street')
> UNION
> select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Realtor, 'Street')
> UNION
> select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Notes, 'Street')
> ) as x
> GROUP BY [KEY]
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
> news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
>

CONTAINS on sql2005

I've just discovered that the CONTAINS clause on sql2005 can search
multiple columns - great!
Is there a way to return which column the search word(s) were found in?
Dunc
Regretably not.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <duncan.welch@.gmail.com> wrote in message
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>
|||On what edition did it work - just enterprise edition or even on standard
(and if so what is required to get it going?)
Jan
"Dunc" <duncan.welch@.gmail.com> schrieb im Newsbeitrag
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>

CONTAINS full-text XML problem

I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:

> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:

> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:

> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>

Consuming events in code

Hi,

Ive been taking a look at how to consume events from a package when executing programatically.

Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.

My class that implements IDtsEvents simply output a message to the console for each event type.

Weird thing is, when I execute, this is the only output I get:

Starting...
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Package
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Sequence
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.TaskHost
OnPostValidate:Microsoft.SqlServer.Dts.Runtime.TaskHost
OnQueryCancel
Package ran successfully

What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.

Anyone know why i dont see all of the events?

Thanks

Jamie

Heres the code:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

namespace Package_API

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Starting...");

Package p = new Package();

p.InteractiveMode = true;

p.OfflineMode = true;

// Add a Script Task to the package.

Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence");

TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask");

// Run the package.

DtsEvents events = new DtsEvents();

p.Execute(null,null,events,null,null);

//p.Execute();

if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend)

Console.WriteLine("Package failed or abended");

else

Console.WriteLine("Package ran successfully");

Console.ReadLine();

}

}

}

// Class that implements the IDTSEvents interface:

public sealed class DtsEvents : IDTSEvents

{

void IDTSEvents.OnPreExecute(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPreExecute: " + exec.ToString());

}

void IDTSEvents.OnBreakpointHit(IDTSBreakpointSite breakpointSite, BreakpointTarget breakpointTarget)

{

Console.WriteLine("OnBreakpointHit");

}

void IDTSEvents.OnCustomEvent(TaskHost taskHost,string eventName,string eventText,ref Object[] arguments,string subComponent,ref bool fireAgain)

{

Console.WriteLine("CustomEvent");

}

void IDTSEvents.OnPreValidate(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPreValidate: " + exec.ToString());

}

void IDTSEvents.OnPostValidate(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPostValidate:" + exec.ToString());

}

void IDTSEvents.OnWarning(DtsObject source,int warningCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)

{

Console.WriteLine("OnWarning");

}

void IDTSEvents.OnInformation(DtsObject source,int informationCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError,ref bool fireAgain)

{

Console.WriteLine("OnInformation");

}

void IDTSEvents.OnPostExecute(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPostExecute");

}

bool IDTSEvents.OnError(DtsObject source,int errorCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)

{

Console.WriteLine("OnError");

return true;

}

void IDTSEvents.OnTaskFailed(TaskHost taskHost)

{

Console.WriteLine("OnTaskFailed");

}

void IDTSEvents.OnProgress(TaskHost taskHost,string progressDescription,int percentComplete,int progressCountLow,int progressCountHigh,string subComponent,ref bool fireAgain)

{

Console.WriteLine("OnProgress");

}

bool IDTSEvents.OnQueryCancel()

{

Console.WriteLine("OnQueryCancel");

return true;

}

void IDTSEvents.OnExecutionStatusChanged(Executable exec,DTSExecStatus newStatus,ref bool fireAgain)

{

Console.WriteLine("OnExecutionStatusChanged");

}

void IDTSEvents.OnVariableValueChanged(DtsContainer DtsContainer,Variable variable,ref bool fireAgain)

{

Console.WriteLine("OnVariableValueChanged");

}

}

By returning true from OnQueryCancel, you are cancelling the package

Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled.

Return false from this method, or inherit from DefaultEvents and only override methods that you actually need.

|||

Michael Entin - MSFT wrote:

By returning true from OnQueryCancel, you are cancelling the package

Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled.

Return false from this method, or inherit from DefaultEvents and only override methods that you actually need.

DOH!!!

What a dumbass. I should have realised that!

Thanks Michael!

-Jamie

|||

How odd, Jamie. According my class I can follow each event -including post and pre executing...

Dim EventsSSIS As EventosSSIS
EventsSSIS = New EventosSSIS()
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

Public Class EventosSSIS
Implements IDTSEvents
Public proceso As Int16 = 0

Sub OnPostValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostValidate
End Sub
Sub OnProgress(ByVal taskHost As TaskHost, ByVal progressDescription As String, ByVal percentComplete As Integer, ByVal progressCountLow As Integer, ByVal progressCountHigh As Integer, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnProgress
End Sub
Sub OnPreExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreExecute
End Sub
Sub OnPreValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreValidate
End Sub
Sub OnPostExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostExecute
End Sub
Sub OnWarning(ByVal source As DtsObject, ByVal warningCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) Implements IDTSEvents.OnWarning
End Sub
Sub OnInformation(ByVal [source] As DtsObject, ByVal informationCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnInformation
End Sub
Sub OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed
End Sub
Function OnError(ByVal source As DtsObject, ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) As Boolean Implements IDTSEvents.OnError
End Function
Sub OnExecutionStatusChanged(ByVal exec As Executable, ByVal newStatus As DTSExecStatus, ByRef fireAgain As Boolean) Implements IDTSEvents.OnExecutionStatusChanged
End Sub
Sub OnCustomEvent(ByVal taskHost As TaskHost, ByVal eventName As String, ByVal eventText As String, ByRef arguments() As Object, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnCustomEvent
End Sub
Sub OnBreakpointHit(ByVal breakpointSite As IDTSBreakpointSite, ByVal breakpointTarget As BreakpointTarget) Implements IDTSEvents.OnBreakpointHit
End Sub
Sub OnVariableValueChanged(ByVal dtsContainer As DtsContainer, ByVal variable As Variable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnVariableValueChanged
End Sub
Public Overloads Function OnQueryCancel() As Boolean Implements IDTSEvents.OnQueryCancel
Dim cancelar As Int32 = 0
OnQueryCancel = False
Try
Using cn As New SqlConnection(sCadenadeConexion)
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
cm.CommandType = Data.CommandType.Text
cm.CommandText = "SELECT cancelar FROM sis_controlthread where idproceso= " & proceso
cancelar = cm.ExecuteScalar
If cancelar Then
OnQueryCancel = True
Else
OnQueryCancel = False
End If
cm.Dispose()
End Using
cn.Close()
End Using
Catch ex As Exception
TratamientoErrores(0, 0, 11, ex.Message, "On Query Cancel")
End Try
End Function
End Class