How to capture OnPipelineRowsSent

Question

Hi all

As you all know in SSIS you can make you own "SSIS management Framework" which is basically your customize SSIS logging, to capture an events like "OnError" you have to have a Stored procedure set in the "OnError" event of the package to be able to capture the errors for your "SSIS management Framework".

My question is how can I capture the "OnPipelineRowsSent" within the events?

I DO NOT want to set the SSIS logging, I want to be able to capture it in an event or somehow?

I am using SQL2008R2

Thanks

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 1

If you look down in the comments of this post:

http://consultingblogs.emc.com/jamiethomson/archive/2007/03/08/ssis_3a00_-onpipelinerowssent.aspx

There is some code to build your own listener that can grab the event.  Otherwise there doesn't appear to be any event which would allow you to get at the data inside a package.

---

Chuck

Answer 2

Hello Nik,

Please see this post: http://www.bidn.com/blogs/BrianKnight/ssis/34/parsing-the-onpipelinerowssent

There it is shown how one can build a stored proc incorporating dbo.sysdtslog90 so I guess dbo.sysdtslog100 must fit right in.

The where clause is event LIKE 'onpipelinerowssent%'.

Or you wanted a programmatic access (e.g. subscribing to the OnPipelineRowsSent events in say C# code)?

---

Arthur My Blog

Answer 3

Hi Artur, in the link he is parsing the string from SSIS logs, I dont want to set/Active SSIS loggs, i want to be able to capture the OnPipleLineRoesSent myself.

And i have mu own function that does the pars.

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 4

AFAIK OnPipleLineRoesSent  is not exposed in any API, it is internal to the SSIS engine. Thus it will require you to write some slick code to wire to its events up.

Is this the route you want to proceed with?

---

Arthur My Blog

Answer 5

I have a function that can read the SSIS loggins for OnPipleLineRoesSent 

but i want to capture the OnPipleLineRoesSent not form setting SSIS to a table then reading it , i'd like to capture the OnPipleLineRoesSent   on the fly, or may as an object

I am close to the answer, but i know it will take time

i am calling the package by using pkg.Execute() in .NET, i am sure that .NET can pull out  OnPipleLineRoesSent somehow.

http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.dts/2007-05/msg00190.html

http://books.google.ca/books?id=JhwR9HVEVooC&pg=PT986&lpg=PT986&dq=How+to+use+IDTSLogging&source=bl&ots=n4AxQuoXgx&sig=nYMB7QOYWhTaiLI3cZ1qiE7jGkI&hl=en&sa=X&ei=IwK0T53aF8aF6QGow8XeDw&ved=0CF8Q6AEwBA#v=onepage&q=How%20to%20use%20IDTSLogging&f=false

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.idtslogging.log.aspx

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 6

That's what I thought,

as per http://msdn.microsoft.com/en-us/library/ms366768%28v=vs.100%29.aspx

You subscribe to an event that is published by another class when you want to write custom code that is called when that event is raised

So something like this should work:

this.MyEventHandler += new System.EventHandler(IntegrationServices.OnPipleLineRowsSent);

---

Arthur My Blog

Answer 7

hi

you can go to in packages ssis menu-->logevents option is there.

you can check how many rows are sent.

---

indu

Answer 8

That's what I thought,

as per http://msdn.microsoft.com/en-us/library/ms366768%28v=vs.100%29.aspx

You subscribe to an event that is published by another class when you want to write custom code that is called when that event is raised

So something like this should work:

this.MyEventHandler += new System.EventHandler(IntegrationServices.OnPipleLineRowsSent);

Thanks Arthur i think that you have the answer but I am trying to put it in my code

as you know I run my packages buy using .NET code

Dim ChildPkgPathAndFileName As String = Dts.Variables("uVar_CPSChildPkgPathAndFileName").Value
        Dim app As New Application
        'Dim events As IDTSEvents
        Dim pkg As Package = app.LoadPackage(ChildPkgPathAndFileName, Nothing)

And to call/run the package i use

''''''''''''''''''''''''''''''''''''
'' EXECUTE PACKAGE
Dim pkgExecResult As DTSExecResult
pkgExecResult = pkg.Execute()

my question is how can i set an event object to the code and how to capture OnPipleLineRoesSent so that i can insert the MESSAGE from OnPipleLineRoesSent  into a table?

heads-up I am not looking for the answer .....

package.LoggingOptions.EventFilter = new string[] { "OnPipelineRowsSent" };

.... this is not what i am looking for.

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 9

hi

you can go to in packages ssis menu-->logevents option is there.

you can check how many rows are sent.

---

indu

Sorry this is not my answer , I did mention that "I DO NOT want to set the SSIS logging" what i ment was i dont want to set this at design time and i want to capture OnPipleLineRoesSent  NOT Through SSIS logging, i want to capture the event and be able to write into the event or capture the OnPipleLineRoesSent  only so that i can insert the MESSAGES into a table 

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 10

Sorry for the later reply Nik,

you know, the

.LoadPackage

method in SSIS 2012 becomes less useful because you will not be able to launch packages off the SSIS 2012 SSISDB (catalog).

So sorry to divert you.

But I think you can actually take advantage of the new logging tables, why not to get the execution ID returned and query the log tables that have all the info already? You may want to spawn a separate thread if you want the checks done concurrently (in parallel).

---

Arthur My Blog

Answer 11

Thanks OldJeep.

Thanks Artur, Yes I know and you are right (I am voting for that), but sometimes it gets into your head to do something extra.

I got it working but some small bits are missing, story short the child package has about 20 log record related to the OnPipleLineRowsSent, when i run the code it does not fail but it shows me only one record of the OnPipeLineRowsSent

the code looke like

Public Sub Log(ByVal logEntryName As String, ByVal computerName As String, ByVal operatorName As String, _
 ByVal sourceName As String, ByVal sourceID As String, ByVal executionID As String, ByVal messageText As String, _
 ByVal startTime As Date, ByVal endTime As Date, ByVal dataCode As Integer, ByRef dataBytes() As Byte) Implements IDTSLogging.Log

In the code i used "Public Sub Log" insted of "Public OverRides Sub Log".

The code works fine but the issues are....

1- i see the wiggle line under the worg "Log" in the defenition of the SUB like ---> "Public Sub Log"

2- in that sub i have set a message box so that i can see the message of the log I only get 3 messages  ( i have event traced it as well) 

3- the "Public Sub Log" gets called 3 times,

          1-Start package

          2-OnPipleLineRowsSent   (this should be arround 20 rows)

          3-EndPackage

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 12

I do get a warning for the, "Public Sub Log" saying

Warning 2 sub 'Log' shadows an overridable method in the base class 'LogProviderBase'. To override the base method, this method must be declared 'Overrides'

but when i add "Public OverRides Sub Log". it wont work.

---

Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).