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 assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.sql

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.

How to assign user variable value to the Derived Column, in Data Flow Task

Hi:

In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.

MA2005

@.[User::YourVariableName]

Actually from the top left side you can expand the variables folder and drag-and-dorp it on the expression field; so you avoid the typing.

How to append a new values like uniqueidentifiers, sequence numbers to data flow?

Hi,

I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?

Append what to where? Do you want to add new columns with such vaues to a path within the data flow task?

Derived Column Transform?

Script Transform?

Row Number Transform ? (http://www.sqlis.com/default.aspx?77)

|||

yes..i want to append values to a path with in the data flow task.

If i use Derived Column Transformation, how can i generate uniqueidentifiers and sequence numbers for each row?

Thanks

Gopi

|||

For GUIDs you would need to use the script task, as there is no "New Guid" function in the expression syntax.

For sequence numbers or row numbers I assume, then again the script task is required. For an easier life you can use the cunningly named Row Number Transform I mentioned previously, which does just that, and comes as a simple MSI to install.

|||

Darren,

Thank you very much.

sql

How To add the value of a variable to column in a DataSet

Hi,

i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".

What i need is a way to add a column to my transformed data which contains a value from a User-Variable.

My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.

So anybody know of an existing Data Flow component, which can do this?

Regards, Martin

How about the Derived Column component? Or have I completely misunderstood the point?

Ed

|||

The Derived Column did exactly what i needed - thanks for the help!

How To add the value of a variable to column in a DataSet

Hi,

i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".

What i need is a way to add a column to my transformed data which contains a value from a User-Variable.

My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.

So anybody know of an existing Data Flow component, which can do this?

Regards, Martin

How about the Derived Column component? Or have I completely misunderstood the point?

Ed

|||

The Derived Column did exactly what i needed - thanks for the help!

How to add SQL statements to the package created by Export Data wizard?

How to add SQl statements to the package created by Export Data wizard? I need to add SQL coming from file, the flow would be like this:

1) SQL would drop all objects: (views, tables, stored procedures) etc

2) SQL would create all objects: (views, tables, stored procedures) etc

3) Export Wizard SSIS package would execute

Also is there a dynamic way to add more tables do the package once created in SQL database? I would like to put the whole task on a scheduler too...

Also can I create all steps automaticly by SQL?|||

RADIOGENERIS wrote:

How to add SQl statements to the package created by Export Data wizard? I need to add SQL coming from file, the flow would be like this:

1) SQL would drop all objects: (views, tables, stored procedures) etc

2) SQL would create all objects: (views, tables, stored procedures) etc

3) Export Wizard SSIS package would execute

Also is there a dynamic way to add more tables do the package once created in SQL database? I would like to put the whole task on a scheduler too...

Execute SQL Task allows you to execute SQL statements. Will that do the job for you?

Yes you can add more tables to the package but you will have to build this yourself. I guess you could use the import/export wizard to create a package for your new tables and then copy whatever it creates into your new package.

To schedule execution of your package use SQL Server Agent.

-Jamie

|||OK, I will add two SQL Controls and see how it goes. I will let you know if there are any problems with this.|||

I heard Microsoft is working on the new Data Transfer tool, which could overcome difficulties I have. Would you know when/if it will become available? Thanks so much.

I am taking those free etraing courses, preatty cool. Seems like 2005 has a lot of new cool features. It would be a shame if I need to downgrade.

|||

Radiogeneris, along with the free etraining, it would help if you spent some time reading Books Online. It's vastly improved from SQL 2000, and has many tutorials and examples that can answer many of the questions you've posted throughout these forums. I can understand your frustration with things being different in SQL 2005, but it takes some work on the user's end as well to learn these features.

|||

I was asking simple questions, some of them are not in the online books (I checked). Like why the controls are greyed out...