I need a VB.net script to capture if the multiple folders are empty and send those folder names in email

Question

I know how to do but, i am not good at .net code....

There are multiple paths stored in  a table, i am capturing them to Object variable & then For each Loop container to assign it to variable... Now i need the script to put into for each loop if the folders are empty I want to capture to Mailstring variable and then send only one email which include all folders which are empty.

SQL Server: 2005 

Thanks in advance.

---

s

Answer 1

Take a look at: http://www.sqlis.com/post/Shredding-a-Recordset.aspx

---

Arthur My Blog

Answer 2

Arthur, i know how to do..the link which you have given will send multiple emails....i want to get only one email...i have like 10 folders in my table. i just need vb code to handle the variables and use that in send mail task.

---

s

Answer 3

Hello Santhos,

I would have approached the problem without using a script task. I will define a vaiable (EmptyFolders) that will hold all the empty folders. This variable will be populated just outside the ForEachLoop container after reading each folder.

A simple execute SQL task can be used to find if a folder is empty or not. This can be done by creating a int variable (ReturnValue) with value 0, in the execute SQL statement inside the ForEach Loop Select ? + 1 As ReturnValue and return the single row to the same variable (ReturnValue). You can then test if ReturnValue is equal to 0, that means you can not find the file/s that you are looking for in the folder.

See image of logic. Hope this helps

Answer 4

Mayorlag,

My approach is  exactly same except Execute Sql Tasks 

• what is - select ? +1
• Can you clearly explain how the other exceute Sql task works...what i am doing with that...what sql i will be using in it...

Final ouput is email should mention all the list of folders which are empty...I dont know whether u r following me....

Thanks in advance

---

s

Answer 5

Do you want the thread moved to .net developers?

I suspect you may not necessarily take this route implementing a lot of code in a Script Task because we have all the bells and whistles to send a single email from SSIS and not to code anything. Perhaps if you could share more insight on what is going to be built would help.

At the moment it seems your design may benefit from using the Precedence Constraints. 

Take a look at:

http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

---

Arthur My Blog

Answer 6

Here is how you could count the number of files in a folder:
http://msdn.microsoft.com/en-us/library/07wt70x2(v=vs.80).aspx

Directory.GetFiles(YourPath)

And then .Count or .Length (not sure) to get the number of files.

And here is how you use SSIS variables in a Script Task:
http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html

The SSIS Foreach Loop container could be replaced by a loop within the Script Task. You could read the contents of the object variable in a Script Task:
http://beyondrelational.com/modules/2/blogs/106/posts/11133/ssis-reading-object-variable-in-script-task.aspx

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter