Karunakaran


Hi,

I created a SSIS Package programatically based on the few threads here in this forum. This package just has a data flow task, during data transfer for every 1000 rows or so I want to update the status in a table in the database.

How do I achieve this


As of now I just have a source and a destination, no transformations in between the flow. I'm not sure if rowcount will help, when I tried it using a onprogress even handler it always showed up as zero.


Thanks




Re: How to get rowcount in a programatically created SSIS package

Phil Brammer


See if this helps any:

http://blogs.conchango.com/jamiethomson/archive/2007/03/08/SSIS_3A00_-OnPipelineRowsSent.aspx








Re: How to get rowcount in a programatically created SSIS package

Karunakaran

Looks like this event is only available in the log providers and not an in DFT. I'm not sure how I can use this in a programatic manner.

Can I put a script component in between the data flow and use it to track the number of records

Thanks






Re: How to get rowcount in a programatically created SSIS package

DarrenSQLIS

How would a script component inform the host or calling application, which is I assume what should be monitoring the progress

You are programatically running the package, so I would make a point of passing in of events and log (IDTSEvents and IDTSLogging) otherwise you have no idea what is going on. If you want real progress info, then this is the only effective way of doing it. Building a packge in a specific way to support progress info is wrong, there is enough stuff in the box already, so use it.






Re: How to get rowcount in a programatically created SSIS package

Karunakaran

Hi Darren,

I guess we have a misunderstanding here or I'm not following your reply.

I built a console application based on this thread. This thread discusses about creating a custom task, while created a console app to create and execute the package. Let me know If I have to post the code.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1371094&SiteID=1

Once I call, package.execute(), I have no idea how many rows have been transfered till the package execution completes. Basically after I call package.execute(), I need to log in a db table for every 1000 rows or so. This has to be implemented using the code.

I'm hoping I have explained what I'm after, if not please let me know.

Thanks





Re: How to get rowcount in a programatically created SSIS package

DarrenSQLIS

There are two overloads for the execute method. Use the one that allows you to pass in event and log objects, so you can get that infromation out. The logging interface will get called with nice row progress info messages, which is exactly what you want.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.execute.aspx

Obviously look at the IDTSEvents and ITDSLogging interafces. There are wrapper classes that implement them, or just build your own.