SSIS Iterate Folder and Delete Files

andy

I needed a way to clean up old folders and files in the folders using SSIS. The foreach loops just were not working to do what I thought was a simple task. So I went to using a script object and works exactly how I need it to. My process will rebuild the folders each day, but I wanted to make sure if some data is not needed it is removed, as a final step just look through all the folders and creates a self-extracting zip file of the contents and I do not want to waste resources with old data.

public void Main()
string[] folders = System.IO.Directory.GetDirectories(Dts.Variables["BaseOutputFolder"].Value.ToString());
foreach(var folder in folders)
{
System.IO.Directory.Delete(folder, true);
}
 Dts.TaskResult = (int)ScriptResults.Success;
}

SQL Pivot

andy

In reviewing SQL Pivot for a project I did discover a new way I was not familiar with to concatenate rows to a single column.

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO
--INSERT INTO CourseSales VALUES('Sql Server',2013,15000)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
 + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
select @ColumnName

So the magic comes in setting the set to a single variable and as it adds it can tell there is data already there and simply concatenates each item in the row. I have done with with making something an XML variable and then remove out XML tags to replace with a comma. This is much simpler.

Up and going

andy

Well I finally got my HAM Technician license after over 20 years of being introduced to the idea. They make is pretty easy now, no morse code needed to pass the exam. They publish the entire list of questions and right answers, so it is really just memorization to initially get started. However, to really know what you are doing, you have to be able to understand over time and to move up with the license level.

It is more of a hobby, but has impact on our community if you are able to serve being an operator during a situation where the communications are down. It can be costly, depending on what you go with, but the idea is to ease into it. You can start with a simple Baofeng hand held radio, UV-5RV2+ is what I have, and most likely some sort of external antenna to get a better reach of signal. I got an Ed Fong DBJ-1 and DBJ-2, so in all for this setup about$110 including some 50′ coax for the antenna (Ed sells for $25).

So I plan to try to log things I learn about HAM and any other stuff I might want to keep in a place that is easier for me to find that may relate to my computer consulting I do as a programmer.