SSIS count of files processed

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS count of files processed

SSIS count of files processed

Rate Topic

Display Mode

Topic Options

Author

Message

Trybbe

Posted Thursday, January 03, 2013 6:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:04 AM
Points: 169, Visits: 433

I have a package that archives old access database files and then send a mail when it completes. I would like to include the number of files processed in the mail message.

i.e. the message should read

"Archiving completed, 10 files archived"

please give direction on how to do this.

Post #1402329

sanandh87

Posted Thursday, January 03, 2013 10:31 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:01 AM
Points: 24, Visits: 51

use foreach loop container, map the folder and inside the container place a script task and write a code for count.

ForEachLoop |
-------------------------------------------------
|
| --------------
| |Script Task |
| ---------------
|
|
|
---------------------------------------------------

define this variable @ package level

User::Count Public Sub Main()
Dts.Variables("User::Count").Value = Dts.Variables("User::Count").Value + 1
MsgBox(Dts.Variables("User::Count").Value.ToString())
Dts.TaskResult = ScriptResults.Success
End Sub

the output of the foreach loop you will get the count of the files. after that you can append the value and send a mail.

Post #1402676

Trybbe

Posted Thursday, January 03, 2013 11:11 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:04 AM
Points: 169, Visits: 433

Hi Thanks for the feedback,

I have two execute process task that I need to get count from. the first one does compact and repair, the second one does archiving. I would like to get a count from both of them.

4 files compacted.
4 files archived.

do I out a script task after each one? isn't the data in the variable going to be overwritten as the container loops

Post #1402691

« Prev Topic | Next Topic »

Permissions