/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

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

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    SqlConnection conn;
    SqlCommand cmd;

    public override void PreExecute()
    {
        base.PreExecute();
        bool pbCancel = true;

        try
        {
            conn = new SqlConnection(@"Data Source=<server_name>;Initial Catalog=<database_name>;Integrated Security=SSPI;Packet Size=32767");
            conn.Open();

            cmd = conn.CreateCommand();
            
			/*
			** listed below are a few examples of how you can get data.  They all assume a single result set
			*/
			
			//a simple procedure call
			cmd.CommandText = "execute <owner>.my_procedure";
            
			//a procedure with parameters 
			cmd.CommandText = "execute <owner>.my_procedure @first_name=@param1, @last_name=@param2";
            cmd.Parameters.Add(@param1,SqlDbType.Char,3).Value='Joe'
			cmd.Parameters.Add(@param2,SqlDbType.Char,8).Value='Richberg'
			
			//select statement
			cmd.CommandText="select col1,col2,col3 from <table_name> where x..........."
			
			cmd.CommandTimeout = 0;
        }
        catch (Exception e)
        {
            ComponentMetaData.FireError(1, "Running  -- preExecute", e.Message, "", 0, out pbCancel);
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();

        bool pbCancel = false;

        try
        {
            if (conn != null && conn.State == ConnectionState.Open)
                conn.Close();
        }
        catch (Exception e)
        {
            ComponentMetaData.FireError(1, "Running -- postExecute", e.Message, "", 0, out pbCancel);
        }
    }

    public override void CreateNewOutputRows()
    {
        bool pbCancel = true;

        SqlDataReader data;

        try
        {
            //again, single result set..
			data = cmd.ExecuteReader(CommandBehavior.SingleResult);

            while (data.Read())
            {
                Output0Buffer.AddRow();

                Output0Buffer.ExternalColumn1 = data.GetString(0); --assumes a string is the first column
                Output0Buffer.ExternalColumn2 = data.GetInt32(1); -- second column is a 4 byte int
            }
        }
        catch (Exception e)
        {
            ComponentMetaData.FireError(1, "Running usp_CoreFeed -- CreateNewOutputRows()", e.Message, "", 0, out pbCancel);
        }
    }
}