Dynamic Generation of FlatFile FixedWidth Part I

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

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using RunTimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Pipeline;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;


namespace ST_394d7e0c32ff40af81e953483ef3c2cf.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
        Package _pgk = new Package();
        Application _app = new Application();
        public void Main()
        {
            Variables vars = null;
            Dts.VariableDispenser.LockForRead("User::strTabListCmd");
            Dts.VariableDispenser.LockForRead("User::strSqlCmdFilter");
            Dts.VariableDispenser.LockForRead("User::strColumnCmd");
            Dts.VariableDispenser.LockForRead("User::strSavePath");
            Dts.VariableDispenser.LockForRead("User::strFileDirectory");
            Dts.VariableDispenser.GetVariables(ref vars);

            try
            {

                String CmdTabList = vars["User::strTabListCmd"].Value.ToString();
                String CmdFilter = vars["User::strSqlCmdFilter"].Value.ToString();
                String CmdColumns = vars["User::strColumnCmd"].Value.ToString();
                String FileDirectory = vars["User::strFileDirectory"].Value.ToString();

                String ServerName = @"";
                String DatabaseName = @"";
                String ConnectionString = "Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True;";
                
                DataTable _DataTable = ExecuteCommand(ConnectionString, CmdTabList);
                int CntRows = _DataTable.Rows.Count;
                foreach (DataRow row in _DataTable.Rows)
                {
                    String FileName = (String)row["FileName"];
                    String TabName = (String)row["TabName"];
                    String Filter = CmdFilter;
                    String Fiter2 = Filter.Replace("<<Filter>>", TabName);
                    StringBuilder NewCmd = new StringBuilder();
                    NewCmd.Append(CmdColumns);
                    NewCmd.Append(Fiter2);
                   
                    DataTable connColumns = ExecuteCommand(ConnectionString, NewCmd.ToString());
                    AddFixedWith(connColumns, TabName, FileDirectory + "\\" + FileName + ".DAT");
                    connColumns = null;
                    NewCmd = null;
                    Filter = null;
                    TabName = null;
                    Fiter2 = null;
                }
              
                _app.SaveToXml("C:\\temp\\test02.dtsx", _pgk, null);
                //Dispose of table 
                // TODO: Add your code here
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch 
            {
                
                Dts.TaskResult = (int)ScriptResults.Failure;

            }
        }
        private DataTable ExecuteCommand(String ConnectionString, String qrty)
        {
            SqlConnection BDEConnection = new SqlConnection(ConnectionString);
            DataTable rtData = new DataTable();
            BDEConnection.Open();
            SqlCommand  SqlCmd = new SqlCommand(qrty,BDEConnection);
            SqlDataReader DtReader = SqlCmd.ExecuteReader();
            rtData.Load(DtReader);
            BDEConnection.Close();
            return rtData;
        }
        private void AddFixedWith(DataTable dtColumns,String ConnectionName,String ConnectionString)
        {
            ConnectionManager flatConn = _pgk.Connections.Add("FLATFILE");
            flatConn.Name = ConnectionName;
            flatConn.ConnectionString = ConnectionString;
            flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
            flatConn.Properties["CodePage"].SetValue(flatConn, "37");
            flatConn.Properties["DataRowsToSkip"].SetValue(flatConn, 1);
            flatConn.Properties["RowDelimiter"].SetValue(flatConn, "\r\n");
            flatConn.Properties["LocaleID"].SetValue(flatConn, "1033");
            //Inner object
            RunTimeWrapper.IDTSConnectionManagerFlatFile100 ConnectionFlatFile = (RunTimeWrapper.IDTSConnectionManagerFlatFile100)flatConn.InnerObject;


            foreach (DataRow row in dtColumns.Rows)
            {
                RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
                FlatFileColumn100.ColumnType = "FixedWidth";
                FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
                FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
                int Scale = (int)row["Scale"];
                switch (Scale)
                {
                    case -1:
                    FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_STR;
                    break;
                    default:
                      FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_BYTES;
                    break;
                    


                }
                
                RunTimeWrapper.IDTSName100 ColName = (RunTimeWrapper.IDTSName100)FlatFileColumn100;
                ColName.Name = (String) row["FieldLevelName"];
               
            }

            

        }


    }
}