Pre and Post Exec Event Handler For a Package

I have a simple test package to play with Event Handler. I got 2 Exec SQL Task and one File System Task. I have set the OnPreExecute and OnPostExecute at package level along with OnPreExecute on SQL and File System Task. These event handlers were loading the package, task name along with start time, machine and user in my audit table (using Exec SQL Task).

OnPreExecute did work fine for SQL and File System Task. They only had one record in the Audit table.

OnPreExecute and OnPostExecute for the package itseld did NOT work. When I say it doesn't work, it logs it to my audit table so many times. I was expecting one record in my audit table for OnPreExecute and one record for OnPostExecute at Package Level. But I was worng. It has inserted into my audit table 14 times instead of twice.

Any ideas? Or am I doing something wrong?

I know I can fix this by workaround. I can remove OnPreExecute and OnPostExecute Event Handler at Package level and insert Exec SQL Task at the beginning and end of the package. This will fix it, but I was wondering why it doesnt work?

Thanks
SuthaI don't quite know why you're getting it 14 times but this will be something to do with the same event getting caught in multiple places.
Every time the File System Task (for example) executes it will fire the eventhandler scoped to the task AND the eventhandler scoped to the package. So your event actually gets handled twice.

-Jamie|||Yeah i concur from our observations that this seems to be what is happening but surely this is a bit of a design issue ....

I want to know from the post execute that the package is starting (or has started), i don't want the startup events from each component thrown up to the post execute event on the whole package because they don't have any relevance at this level.

What do you think ?

Mark Hill|||Jamie

I tried it on a brand new package as well. I can re-produce it. As Mark states that it looks like an design issue. Anyone from MS can shed any light on this?

Thanks
Sutha|||

You can easily debug it like that:
- place a script in package's OnPreExecute event handler
- set a breakpoint on the task (F9)
- run in debugger
- when debugger brakes on the breakpoint add SourceName variable to the Watch window.

Now, every time you hit the breakpoint you will see the name of the task/container that raised the event.

I believe that all OnPreExecute events in your package get propagated to the package level. That's why you see so many of them. If it is not what you expect - you can change it setting Propagate system variable on event handlers to False

Possible to encrypt database assembly?

Hello.

I've built a simple Visual Basic .NET project containing the following code...

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub WhoAmI()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT SUSER_SNAME()", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class

From Visual Studio, I want to encrypt the contents of this assembly, as a proof-of-concept.
Even though assembly contents are stored as varbinary(MAX) in the database, converting to varchar(MAX) will expose the code.

However, the Dotfuscator Community Edition reports the following error: "Dotfuscator Community Edition cannot operate on SQL Server applications.... please try Standard or Professional Edition."

Has anyone tried encrypting a database assembly and deploying to the database?

A good test would be to issue the following TSQL script against the database holding the assembly...

-- Does the sample code above run?
EXEC dbo.WhoAmI
GO
-- Is the code readable?
SELECT * , Convert(varchar(MAX), content) FROM sys.assembly_files

Hi,

There is no explicit support for encrypted assemblies in the database. You can use a code obfuscation utility to make reverse-engineering difficult, as you suggest.

Perhaps someone else has a favorite obfuscator they can recommendI don't know much about them.

Cheers,

-Isaac

|||

Note that the code you're reading from sys.assembly_files isn't related to encrypting the assembly - VS deploys your full source code and PDB files to the database to assist with debugging. If you don't want VS to do this, then you can turn it off under the project properties / Deploy / Deploy Code option. This still won't protect your assembly though as anyone can use a reflection tool such as http://www.denisbauer.com/NETTools/SQL2005Browser.aspx. Obfuscation can help here, but the real solution is to control who has access to the system views containing the assembly using metadata permissions.

Steven

|||A great answer... You've given lots of new leads. Thanks.

Possible to assign for each CPU a different process within one SQL Server 2005 on a multicore se

Hi all,

is it possible to assign for each CPU a different process within one SQL Server 2005?

I got 2 servers (each 4 CPUs; 64 bit system) which should be able to failover, but on the other hand there are reporting/analysing applications and totally different sharepoint/other applications that should be hosted seperately, each on one physical server.

Thanks a lot for any help!

MarcBey wrote:

Hi all,

is it possible to assign for each CPU a different process within one SQL Server 2005?

I got 2 servers (each 4 CPUs; 64 bit system) which should be able to failover, but on the other hand there are reporting/analysing applications and totally different sharepoint/other applications that should be hosted seperately, each on one physical server.

Thanks a lot for any help!

Within one SQL Server? No not that I am aware of. You can use the affinity mask option to exclude certain CPUs from an entire instance of SQL Serfver, but you can't do it by individual databases within an instance.

|||

Hi,

It is not possible to restrict CPU to work for certain task.

Regards

Sufian

Possible bug with SSIS File System Task

I'm having an issue with a file system task & I'm not sure whether it is user error on my part or a bug. I'm using a SQL Task to create a transaction log backup & I'm saving the name of the file in a result set which I then am mapping to a package level user variable. After that runs I'm trying to copy this .BAK file to another folder using the file system task. I'm setting the following properties on the file system task.

Isdestinationpathvariable: False

Then I have entered the static directory for the file move.

Operation: Tried it with both copy file & move file.

Issourcepathvariable: True

Sourceconnection: User::File_name

After setting this I immediately get a validation warning telling me the source directory cannot be empty. If I try to run it, it fails. The weird thing is that if I set up a connection manager to a flat file & pass my user variable in as the connection string to this connection. Then set the Issourcepathvariable to false & the Sourceconnection to this connection manager it works.

I also have gotten it to work by substituting an ftp task in place of the file system task. The ftp task has no problem when I set the Islocalpathvariable to true & then pass my variable to Localvariable property. This is why I believe there is some sort of issue with the file system task. Has anyone seen this before? Is there some sort of problem with the way I'm setting it up?

Thank you

You might be missing the path of your source connection. Make sure you are including the path of the source file as a part of the File_name variable.

Rafael Salas

|||I tried it with both a mapped drive letter in the path & a unc path. No luck with either. Also the path I put into the variable works for the ftp task. Thank you though.

Ports

Hi
I have a SQL Server 2000 system with two instances. The
non-default instance needs to use port 1433, but the
default instance is using that one. Anyone know how to
change the ports the instances are using?
JoeUse the Server Network Utility, select the instance then
select the TCP/IP protocol and select properties. You can
set the ports from here.
-Sue
On Tue, 18 Nov 2003 07:43:56 -0800, "Joe"
<anonymous@.discussions.microsoft.com> wrote:
>Hi
>I have a SQL Server 2000 system with two instances. The
>non-default instance needs to use port 1433, but the
>default instance is using that one. Anyone know how to
>change the ports the instances are using?
>Joe|||In case your instances are running in a Microsoft server
cluster, both instances can listen on the same port since
they'll be using two different IP addresses.
Linchi
>--Original Message--
>Hi
>I have a SQL Server 2000 system with two instances. The
>non-default instance needs to use port 1433, but the
>default instance is using that one. Anyone know how to
>change the ports the instances are using?
>Joe
>.
>