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 append a header row in SELECT

Hi All,

When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do?
I would like to make a SP that would select all data from any table(with a header row) with just:

_sp_getData ' table_name'

I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.

Something like this:

select 0 AS SortCol ,
 cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1,
cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2,
cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3,
cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4,
cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5,
cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6,
cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7,
cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8,
cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9
 from information_schema.columns where table_name = 'authors'
union all
select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip,
cast(contract as varchar)
 from authors

Is not an option.

Thank you very much in advance,
Vla OrlovskyJust an advice: do not do it. THis is not SQL. You add an additional non-data row int data. VERY bad style.

Especially as you then go on and say the one way to do it (union) is not an option.|||Hi Tom,

Thanks for reply.

In my case, I just want to export data into a VB RecordSet and into a CSV text file.
My co-workers want to have the data to be imported into other places.
I just want to create a simple GUI that allows them to get the data without involving me.

Sincerely,
Vlad Orlovsky|||So what?

Before exposrint to a CSV file, write the first line with the names of the columns.

Interesting enough, the information is in the VB recordset.|||If I understand correctly, before you output the actual data to CSV, you can just loop through the fields collection on the first row of your recordset and output the Name and Type property like below:

For intCounter = 0 To oRS.Fields.Count - 1
strName = oRS.Fields(intCounter).Name
strType = oRS.Fields(intCounter).Type
Next|||Hi,

I wanted to get the data the most fastest way posible.(with very little IIS processing)

Sincerely,
Vlad Orlovsky

how to add record with same Primarykey that user deleted

I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
Many thanks.An autonumber? Look at IDENTITY_INSERT in BOL.

How to add record thru command button in adp.

Hi,

In access database project where my tables are linked from MSSQL server, how can I add record from the FORM itself using command button. Bcz it is not like mdb files in access that you can just drag a command button onto the form and take an action like add, delete, print or find rec. I just came to know it from immediate window using following SQL line;

Docmd.RunSql "Insert......

This is ok but how it could be done thru a command button on FORM.

Thanks in advance.

With kind regards,
AshfaqueYou can use RunSQL with an ADP file. Or you can call an INSERT procedure on your SQL Server database.|||Hi blindman,

Bcz I am new to adp, can you please extend your help in more detail. I never used adp and its prodedure. How can I do it. Do I need to create a function / procedure? If so. How..Need to know badly. . Help in this regards will be higly appriciated.

Thanks a lot.
Ashfaque|||In an ADP application, no data is stored in Microsoft Access. It is all stored on the back-end server.

You can put code in the modules or forms in the ADP file, but it should be limited to affecting the behavior of the user interface. Your business logic should be implemented in stored procedures, views, and user-defined functions on your database server.

For an explanation of how to write TSQL code for SQL Server, look up VIEWS and STORED PROCEDURES in Books Online.|||Thanks again blindman,

I will check it.

With regards,
Ashfaque

How to add record into SQL Server 2000?

How can I add the record into my company SQL Server 2000 database from the website register page?

This is my codes:
<%
Dim sql,rs

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\InetPub\wwwroot\daly\fpdb\market.mdb"

sql="INSERT INTO testing (fname,lname)"
sql=sql & " VALUES "
sql=sql & "('Shawn', 'mike')"

objConn.Execute sql
objConn.close
%>

This is my error:
Microsoft JET Database Engine error '80004005'

Operation must use an updateable query.

/VITA_SHOW/test_VITAFormhandler.asp, line 137

Thank you very much!!!

Shawn
scn@.daly.comNot sure why you are getting that error, but a much better design strategy is to create a stored procedure that inserts the record based upon supplied parameters. You should avoid giving applications direct access to data tables.|||your problem might have been either...

1. you dont have the NT permissions for a file-based database or for the folder that contains the file
2. the file might have been exclusive
3. theres another thats still connected that needs to be closed

for more info on issuing this problem, goto MS white papers

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306269|||What type of object is "testing"? I suspect it's not a table, but rather a query.|||You say that you are updating SQL Server 2000, but your code seems to be looking at a Microsoft Access database - the Jet engine and the .mdb extension both refer to Access.

Andy|||objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\InetPub\wwwroot\daly\fpdb\market.mdb"

First off, the database is Access, not SQL, as can be seen from the connection string above.

If you are trying to use access, then the reason for the error is that the IUSR_SERVERNAME (or the authenticated web user, if applicable) does not have write access to the folder and file at c:\InetPub\wwwroot\daly\fpdb\market.mdb. Make sure you set read/write privileges for IUSR_SERVERNAME or Everyone for both the MDB file and the folder in which it resides.

If you are in fact trying to connect to an MSSQL server, you will need to do one of the following:

a) If you have access to the ODBC configuration, set up a DSN, and use the following syntax:

objConn.Open "DSNNAME","USERNAME","PASSWORD"

b) If you don't have access to create a DSN, use the following syntax:

objConn.Open "Driver={SQL Server};UID=USERNAME;password=PASSWORD;database=DA TABASE_NAME;server=SERVER_NAME_OR_IP"