How to audit data flow runs

What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

How to audit data flow runs

What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

How to add speed when run sql execute command?

Hi,
the problem i'm facing when i run the sql is when i run
the command- executenonquery or executescalar(), the
system needs a long time to process before come out a
result.
i'm using Intel P4 1.5GHz, 256 RAM.
Can anyone tell me that how to add speed when execute the
sql command'?
thanks.There can be many reasons why the command executes slowly. DDL statements
(Cretae Table etc.), demo data (Insert statements) and the command you re
sending would definitely help solving the problem. You can use Query
Analyzer to anayze the T-SQL query you are sending - you can see the
execution plan and find worst operations, like table scans, sorts...
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.|||What sort of command are you executing? It will be a good idea to have a
look at the actual query.
--
With Regards,
Deepak
[I code, therefore I am]
http://deepakictim.blogspot.com
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:10a6401c3f435$2e416eb0$a001280a@.phx.gbl...
> Hi,
> the problem i'm facing when i run the sql is when i run
> the command- executenonquery or executescalar(), the
> system needs a long time to process before come out a
> result.
> i'm using Intel P4 1.5GHz, 256 RAM.
> Can anyone tell me that how to add speed when execute the
> sql command'?
> thanks.

how to add ODBC DSN to multiple computers

Hello. I need to set add an SQL ODBC System DSN to each computer on
our domain.
The suggestions I've seen so far involve setting up a GPO startup
script using VBScript to call an API, or a batch file using odbcconf.
I don't know VB, and I could find very little information about how to
use odbcconf.
I would appreciate it if someone could give me a standard solution, if
there is one. I'm a little new to this, so I wouldn't be surprised if
I were completely off the mark.
thanks,
joseph
If the use of a DSN is not really mandatory for your application, then using
a DSN-Less ODBC connection might be an easier solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Joseph O'Brien" <obrien1984@.hotmail.com> wrote in message
news:1139438690.109709.324280@.g47g2000cwa.googlegr oups.com...
> Hello. I need to set add an SQL ODBC System DSN to each computer on
> our domain.
> The suggestions I've seen so far involve setting up a GPO startup
> script using VBScript to call an API, or a batch file using odbcconf.
> I don't know VB, and I could find very little information about how to
> use odbcconf.
> I would appreciate it if someone could give me a standard solution, if
> there is one. I'm a little new to this, so I wouldn't be surprised if
> I were completely off the mark.
> thanks,
> joseph
>

how to add ODBC DSN to multiple computers

Hello. I need to set add an SQL ODBC System DSN to each computer on
our domain.
The suggestions I've seen so far involve setting up a GPO startup
script using VBScript to call an API, or a batch file using odbcconf.
I don't know VB, and I could find very little information about how to
use odbcconf.
I would appreciate it if someone could give me a standard solution, if
there is one. I'm a little new to this, so I wouldn't be surprised if
I were completely off the mark.
thanks,
josephIf the use of a DSN is not really mandatory for your application, then using
a DSN-Less ODBC connection might be an easier solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Joseph O'Brien" <obrien1984@.hotmail.com> wrote in message
news:1139438690.109709.324280@.g47g2000cwa.googlegroups.com...
> Hello. I need to set add an SQL ODBC System DSN to each computer on
> our domain.
> The suggestions I've seen so far involve setting up a GPO startup
> script using VBScript to call an API, or a batch file using odbcconf.
> I don't know VB, and I could find very little information about how to
> use odbcconf.
> I would appreciate it if someone could give me a standard solution, if
> there is one. I'm a little new to this, so I wouldn't be surprised if
> I were completely off the mark.
> thanks,
> joseph
>

How to add a Windows User Login to a database using T-SQL

Hi All,

I'm trying to add a new Windows User to a database using T-SQL. I tried the sp_addlogin system proc, but this doesn't allow backslashes (presumably because it only allows creation of SQL Server logins).

I couldn't find a sproc for this, what's the best way to achieve it?

Thanks,

Simon.

Sorry, being stupid - again!!

sp_grantlogin is all you need...

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
Regards,
JamieActually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
--
Regards,
Jamie
"thejamie" wrote:

> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the networ
k
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of lea
st
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
Regards,
Jamie
Actually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
Regards,
Jamie
"thejamie" wrote:

> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the network
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of least
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
--
Regards,
JamieActually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
--
Regards,
Jamie
"thejamie" wrote:
> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the network
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of least
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to access the SQL mobile database(.sdf) using ASP.Net?

How to access the SQL mobile database(.sdf) using ASP.Net?

When i try to add the System.Data.SqlServerCe.dll as a Reference it shows the following error.

Error 1 Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

You must browse to this folder: C:\Programmer\Microsoft SQL Server Compact Edition\v3.1 and add the reference from there. In addition, you must follow the instructions in this article http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.aspx if you want to use the SqlCeConnection. Also note the advice on performance problems with SQL CE and asp.net. Please not there is a documentation error, see here for the correct syntax:

http://msdn2.microsoft.com/en-us/library/ms174612.aspx