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)
Dts.TaskResult = (int)ScriptResults.Success;
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.
--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
--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)
--INSERT INTO CourseSales VALUES('Sql Server',2013,15000)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
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.
I will visit this example in the future, logging it here to keep it in one place. http://blog.robseder.com/2013/10/18/executing-a-long-running-process-from-a-web-page/