Good day experts, i have a package in place that generates report from a database (ole db connection) to an excel file, my lead wants to have some kind of an audit log where the number of rows generated by ole db connection should be recorded in an flatfile, column should be date today and number of rows.

Hello, Use Row Count Transformation to get the number of rows from OLE Source to Excel. Have another variable , lets say VarSQL string type, now write expression on this variable , build Select Statement in this variable "SELECT "+@[User::VarRowCount]+" AS RowCount, Getdate() AS DATE" Drag Second Data Flow task, use this variable VarSQL as source , then drag Flat file destination and map. Now every time you will execute your pacakge, it will insert rowcount and date into your flat file at the end. Thankshttp://sqlage.blogspot.com/

Hi AAmir, Thanks for the quick response. I am almost there. Last question, How can i use the variaable VarSQL as souce? what object shoud i use?

Hi AAmir, Thanks for the quick response. I am almost there. Last question, How can i use the variaable VarSQL as souce? what object shoud i use? In your OLE DB Source Editor select SQL Command from variable in the drop down list Data Access Mode. Then you can select the variable that contains your query. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I GOT Thanks AAMir for the Help. I really appreaciate it. Thanks!