Tuesday 13 April 2010

Excel Destination - using Script

Recently faced a problem with the Excel Destination Data Flow component. It does not write formulas properly.
So developed this script that will use a File Connection and write directly to Excel.



using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string filename;
Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel.Workbook wbook;
Microsoft.Office.Interop.Excel.Worksheet wsht;

int row;

public override void PreExecute()
{
row = 1;
filename = (string)this.Connections.OutputFileConnection.AcquireConnection(null);
excelApp = new Microsoft.Office.Interop.Excel.Application();
wbook = excelApp.Workbooks.Add(Missing.Value);
wsht = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets.get_Item(1);

base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{

//wbook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wbook.Close(true, filename, false);
excelApp.Quit();
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
wsht.Cells[row, "A"] = Row.ID;
wsht.Cells[row, "B"] = Row.CountryName;
//wsht.Cells[row, "C"] = "=INDIRECT(ADDRESS(ROW(),1, 4)) * 2";
row++;
}
}

No comments:

Post a Comment