Pivoting data in SQL and SSIS Dynamically

There is a pviot function in SSIS and a pivot query in SQL - both of which I find laborious, ESPECIALLY the one in SSIS!
Here is a dynamic SQL query that will fix the problem:

DECLARE @columns NVARCHAR(2000)
SELECT  @columns = COALESCE(@columns + ',[' + [Column2] + ']', '[' + [Column2] + ']')
FROM    TableName
ORDER BY [Column2]

--print @columns
DECLARE @SqlQuery nvarchar(4000)
set @SqlQuery = 'select [Column1],' + @cols + ' into dbo.NewTable from (select [Column1],[Column2], [Column3] from TableName) p 
   PIVOT (MAX([Column3]) FOR [Column2] in (' + @columns + ') ) AS pvt order by [Column1]'

--print @SqlQuery
execute (@SqlQuery)

I recommend adding this as an 'Execute SQL Task' in your Control Flow - even if it means using a Staging Table. Will Allow for a change in columns and everything!

One more thing! Notice the 'into dbo.NewTable'. You'll need to drop that table before performing this task. This will allow for a change to the dynamic columns. Dynamic column queries such as this are great even if the columns are fixed.
I've just done one where there were 50 columns after the pivot and I don't have time to be defining and writing all that extra code! It is espcially irritating in SQL (before SQL Server 2012 - which I understand takes care of tasks such as this much better!).
Good luck.

Create and export Excel in C# Winform application

So I used to do this with something along the lines of:

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsApplication1
{
 public partial class Form1 : Form
 { 
    public Form1()
  {
   InitializeComponent();
  }
 private void button1_Click(object sender, EventArgs e)
 {
  Excel.Application excel = new Excel.Application();
  Excel.Workbook wb = new Excel.Workbook();
  excel.Workbook.Add(wb);

etc...etc...etc..

Then I would loop through, adding data to a worksheet. This was very memory intensive and very SLOW....
This is how I do it now.

I create an Excel file using Excel - I add and name worksheets, I format columns etc and get it to 'look' how I want it to.

I then add this to the application in a folder called 'Excel Templates'. You can then change the deployment properties to export and create this file on installation.

The idea is that the application will copy, rename and save this file. It then creates an OLEDB connection and adds rows using an update command (I use update because it keeps the cell formatting and doesn't append after 'blank rows').

This is a basic class for copying and populating the Excel template. The 'FileName' parameter is what and where you want to save the file as.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace SpaceApp_v0._1.Classes
{
    class ExcelClassExample
    {
        static DataSet ds_Excel;

        public void CreateViewer(string FileName)
        {
            string myPath = System.Windows.Forms.Application.StartupPath + @"\Excel Templates\ExcelTemplate.xls";

            File.Copy(myPath, FileName, true);

            Populate_Excel(FileName);
        }

        private void Populate_Excel(string FileName)
        {
            SqlConnection conn = new SqlConnection(ConnectionsClass.Server);

            string sqlQuery = "select FieldNames from TableName Where parameters = 'Value'";

            SqlCommand cmd = new SqlCommand(sqlQuery, conn);

            ds_Excel = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            conn.Open();

            da.Fill(ds_Excel, "TableName");

            conn.Close();

            string DSN = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No;IMEX=2\"", FileName);
            using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
            {

                Con.Open();

                using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
                {
                    int Row = 1;
                    foreach (DataRow row in ds_Excel.Tables["TableName"].Rows)
                    {
                        Com.Connection = Con;
                        Com.CommandText = string.Format("UPDATE [Sheet1$A" + Row + ":Z" + Row + "] SET F1 = '{0}'", row["Column1"]);

                        Com.ExecuteNonQuery();
                        Row++;
                    }
                }
                Con.Close();
            }
        }

    }
}

You can call this class from your main app using a button click event. I have used this is a background worker, creating 2,500 Excel 'templates' in a short period of time, with progress bar etc.