March 19, 2014

Transfer Table to File Using Dynamic SSIS


/*

   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 System.Windows.Forms;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Pipeline = Microsoft.SqlServer.Dts.Pipeline;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;
using System.IO;
using System.Xml;




namespace ST_0b887b30a0984bc088553f39a5f7d1c7.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.
*/
        Runtime.Package myPackage = new Runtime.Package();


        char SOH = (char)1; // SOH
        char RS = (char)30;// RS 
        String Delimited = "";
        

        public void Main()
        {
            Runtime.Variables vars = null;
            Delimited = SOH.ToString();
           

            //READ
            Dts.VariableDispenser.LockForRead("User::strTableName");
            Dts.VariableDispenser.LockForRead("User::strFileName");
            Dts.VariableDispenser.LockForRead("User::strDirectoryPath");
            Dts.VariableDispenser.LockForRead("User::bolOverrideFile");
            Dts.VariableDispenser.LockForRead("User::strSqlCmd");
            Dts.VariableDispenser.LockForRead("User::strSourceFileName");
            Dts.VariableDispenser.LockForRead("User::strFullFilePath");
            Dts.VariableDispenser.LockForRead("User::bolSavePackage");
            Dts.VariableDispenser.LockForRead("User::strSavePackageLocation");
            Dts.VariableDispenser.LockForRead("User::intColumnDelimitedAscii");
            Dts.VariableDispenser.LockForRead("User::intRowDelimiterAscii");
            Dts.VariableDispenser.LockForRead("User::bolIncludeColumnHeaders");


            //WRITE
            Dts.VariableDispenser.LockForWrite("User::intExtractCount");

            // Reference Variables 
            Dts.VariableDispenser.GetVariables(ref vars);

            String TableName = vars["User::strSourceFileName"].Value.ToString();
            String FileName = vars["User::strFileName"].Value.ToString();
            String DirectoryPath = vars["User::strDirectoryPath"].Value.ToString();
            String FullFilePath = vars["User::strFullFilePath"].Value.ToString();
            String SourceSqlCmd = vars["User::strSqlCmd"].Value.ToString();
            String SavePackageDirectory = vars["User::strSavePackageLocation"].Value.ToString();
            SOH = (char)(int)vars["User::intColumnDelimitedAscii"].Value;
            RS = (char)(int)vars["User::intRowDelimiterAscii"].Value;

            Boolean FileOverride = (Boolean)vars["User::bolOverrideFile"].Value;
            Boolean SaveSsisPackage = (Boolean)vars["User::bolSavePackage"].Value;
            Boolean IncludeHeaderFile = (Boolean)vars["User::bolIncludeColumnHeaders"].Value;
            myPackage.Name = vars["User::strTableName"].Value.ToString() ;
            myPackage.Description = "Table2FileTransfer";
            try
            {

                // Add RowCount Variable 
                // Local variable for File Path
                Runtime.Variable intRawDataCount = myPackage.Variables.Add("intRawCount", false, "User", 0);

                //Create OLEDB Connection
                Runtime.ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
                Runtime.ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
                Runtime.ConnectionManager Source = AddOLEDBConnection(TableName, Dts.Connections["source"].ConnectionString);
                //Add FlatFileDestination
                Runtime.ConnectionManager FlatFile = AddFlatFileConnection(FileName, FullFilePath, Delimited,IncludeHeaderFile);

                // Add Logging
                AddLogging("msdb", true);

                //Add Sequence Container 
                Runtime.Executable execSequence = AddSQLExecutable("STOCK:Sequence");
                Runtime.Sequence rtSequence = (Runtime.Sequence)execSequence;

                //Sequence Properties
                rtSequence.Name = "DataHub_ODS_EXTRACT";
                rtSequence.LoggingMode = Runtime.DTSLoggingMode.UseParentSetting;
                rtSequence.Description = "DATAHUB_ODS Export Prototype";

                // Add WorkFlow Component
                Runtime.Executable execDataFlow = rtSequence.Executables.Add("STOCK:PipelineTask");
                Microsoft.SqlServer.Dts.Runtime.TaskHost thDataFlow = (Runtime.TaskHost)execDataFlow;
                thDataFlow.Name = "Fill2RawLoading";
                PipelineWrapper.MainPipe DataFlow = (PipelineWrapper.MainPipe)thDataFlow.InnerObject;

                //Adding Source Component.
                PipelineWrapper.IDTSComponentMetaData100 icmdSource = AddComponentMetadata(DataFlow, "SourceComponet");
                icmdSource.ComponentClassID = "DTSAdapter.OleDbSource.2";//sql2012
                icmdSource.Name = "SourceTbl";
                PipelineWrapper.CManagedComponentWrapper mcwSource = icmdSource.Instantiate();
                mcwSource.ProvideComponentProperties();

                icmdSource.RuntimeConnectionCollection[0].ConnectionManagerID = Source.ID;
                icmdSource.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface(Source);

                mcwSource.SetComponentProperty("AccessMode", 2);//SQLCmd
                mcwSource.SetComponentProperty("SqlCommand", SourceSqlCmd);
                //mcwSource.SetComponentProperty("AccessMode", 0); //TableOrView
                //mcwSource.SetComponentProperty("OpenRowset", RawTableName);
                RefreshMetadata(mcwSource);

                //Add RowCount Component
                PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(DataFlow, "RowCountComponent");
                icmdRowCount.ComponentClassID = "DTSTransform.RowCount.2";
                icmdRowCount.Name = "RawFileRowCount";
                PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
                mcwRowCount.ProvideComponentProperties();
                mcwRowCount.SetComponentProperty("VariableName", "User::intRawCount");

                // Join Source and RowCount 

                PipelineWrapper.IDTSPath100 tblSource2Count;
                tblSource2Count = DataFlow.PathCollection.New();
                tblSource2Count.Description = "Fill Table 2 Raw Table";
                tblSource2Count.AttachPathAndPropagateNotifications(icmdSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
                RefreshMetadata(mcwRowCount);

                // Add FlatFile Destination Component
                PipelineWrapper.IDTSComponentMetaData100 icmdFileDestination = AddComponentMetadata(DataFlow, "FlatFileDestination");
                icmdFileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination.2";
                icmdFileDestination.Name = "FlatFileDestination";

                //Get Instance
                PipelineWrapper.CManagedComponentWrapper mcwFlatFileDestination = icmdFileDestination.Instantiate();
                mcwFlatFileDestination.ProvideComponentProperties();

                mcwFlatFileDestination.SetComponentProperty("Overwrite", FileOverride);
                //Set Destination connection
                icmdFileDestination.RuntimeConnectionCollection[0].ConnectionManagerID = FlatFile.ID;
                icmdFileDestination.RuntimeConnectionCollection[0].ConnectionManager = Runtime.DtsConvert.GetExtendedInterface(FlatFile);

                // Add Path between rowCount and Flatfile
                IDTSPath100 path = DataFlow.PathCollection.New();
                path.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0],
                icmdFileDestination.InputCollection[0]);

                //Set Input and Virtuals for FlatFile Inputs 

                SetColumnsNamesForFlatFile(ref icmdFileDestination, ref FlatFile, Delimited);
                RefreshMetadata(mcwFlatFileDestination);


                MappColumns(icmdFileDestination, mcwFlatFileDestination);

                // Exectue Package in Memory 
                 myPackage.Execute();
                 if ((myPackage.ExecutionResult == Runtime.DTSExecResult.Failure) || (myPackage.ExecutionStatus == Runtime.DTSExecStatus.Abend))
                 {

                     if (SaveSsisPackage)
                     {
                         SavePackage(SavePackageDirectory);
                     }

                     throw new System.InvalidCastException("Generated Package Failure");

                     //Dts.TaskResult = (int)ScriptResults.Failure;
                 }
                 else
                 {
                     // Get internal Count 
                     vars["User::intExtractCount"].Value = (int)intRawDataCount.Value;
                     // release variables
                     vars.Unlock();
                     vars = null;
                     if (SaveSsisPackage)
                     {
                         SavePackage(SavePackageDirectory);
                     }
                     myPackage.Dispose();
                    Dts.TaskResult = (int)ScriptResults.Success;
                 }
               
            }
            catch
            {
                myPackage.Dispose();
                //SavePackage(vars["User::strDirectoryPath"].Value.ToString());
                Dts.TaskResult = (int)ScriptResults.Failure;
            }






        }
        /*************************Sub Programs*********************************************************/
        public void SetColumnsNamesForFlatFile(ref PipelineWrapper.IDTSComponentMetaData100 FlatFile, ref Runtime.ConnectionManager ConnFlatFile, String Delimited)
        {
            IDTSInput100 FlatFileOutput = FlatFile.InputCollection[0];
            IDTSVirtualInput100 vFlatFileInput = FlatFileOutput.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 FlatFileVirtualInputColumns = vFlatFileInput.VirtualInputColumnCollection;

            RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = (RuntimeWrapper.IDTSConnectionManagerFlatFile100)ConnFlatFile.InnerObject;

            // Create flat file connection columns to match pipeline
            int indexMax = FlatFileVirtualInputColumns.Count - 1;
            for (int index = 0; index <= indexMax; index++)
            {
                // Get input column to replicate in flat file
                IDTSVirtualInputColumn100 virtualInputColumn = FlatFileVirtualInputColumns[index];

                // Add column to Flat File connection manager
                RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileColumn =
                connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
                flatFileColumn.ColumnType = "Delimited";
                flatFileColumn.ColumnWidth = virtualInputColumn.Length;
                flatFileColumn.DataPrecision = virtualInputColumn.Precision;
                flatFileColumn.DataScale = virtualInputColumn.Scale;
                flatFileColumn.DataType = virtualInputColumn.DataType;
                RuntimeWrapper.IDTSName100 columnName = flatFileColumn as RuntimeWrapper.IDTSName100;
                columnName.Name = virtualInputColumn.Name;

                if (index < indexMax)
                    flatFileColumn.ColumnDelimiter = Delimited;
                else
                    flatFileColumn.ColumnDelimiter = RS.ToString();
            }




        }
        public void AddLogging(String ConnectionName, Boolean Enable)
        {
            Runtime.LogProvider pkLogging;
            pkLogging = myPackage.LogProviders.Add("DTS.LogProviderSQLServer.2");
            pkLogging.ConfigString = ConnectionName;
            pkLogging.Description = "SQL Server Logging ";
            myPackage.LoggingOptions.SelectedLogProviders.Add(pkLogging);
            myPackage.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
            myPackage.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
            switch (Enable)
            {
                case true:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
                    break;
                case false:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;
                default:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;

            }
        }
        public Runtime.ConnectionManager AddFlatFileConnection(String ConnectionName, String FullFilePath, String Delimited,Boolean IncludeHeaders)
        {
            char RS = (char)30;
            Runtime.ConnectionManager connectionManagerFlatFile = myPackage.Connections.Add("FLATFILE");
            connectionManagerFlatFile.ConnectionString = FullFilePath;
            connectionManagerFlatFile.Name = ConnectionName;
            connectionManagerFlatFile.Properties["Format"].SetValue(connectionManagerFlatFile, "Delimited");
            connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, IncludeHeaders);
            // connectionManagerFlatFile.Properties["TextQualified"].SetValue(connectionManagerFlatFile, false);
            connectionManagerFlatFile.Properties["RowDelimiter"].SetValue(connectionManagerFlatFile, RS.ToString());

            return connectionManagerFlatFile;


        }
        /*
           * Add OLEDB Connection 
           */
        public Runtime.ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
        {
            Runtime.ConnectionManager ConMgr = myPackage.Connections.Add("OLEDB");

            ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
            ConMgr.Name = ConnectionName;
            ConMgr.Description = "SQL OLEDB using " + ConnectionName;

            return ConMgr;

        }
        /*
       
        /*
      * Add WorkFlow
      *
      */
        private PipelineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
        {
            myPackage.Executables.Add("STOCK:PipelineTask");
            Runtime.TaskHost _TaskHost = (Runtime.TaskHost)myPackage.Executables[0];
            PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
            _TaskHost.Name = ComponentName;
            _TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");

            return dataFlowTask;

        }
        private IDTSComponentMetaData100 AddComponentMetadata(MainPipe wf, String ComponentName)
        {
            IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
            return icmd;
        }
        // Create SSIS executable
        private Runtime.Executable AddSQLExecutable(String MONIKER)
        {
            Runtime.Executable exec = myPackage.Executables.Add(MONIKER);

            return exec;
        }


        private void SavePackage(String DirectoryPath)
        {
            String FullFilePath = DirectoryPath + "\\" + myPackage.Name.ToString() + ".dtsx";
            if (File.Exists(FullFilePath))
            {

                File.Delete(FullFilePath);
                Runtime.Application app = new Runtime.Application();
                app.SaveToXml(FullFilePath, myPackage, null);

            }
            else
            {
                Runtime.Application app = new Runtime.Application();
                app.SaveToXml(FullFilePath, myPackage, null);

            }
        }

        private void RefreshMetadata(CManagedComponentWrapper mcw)
        {
            //Reinitialize the metadata, Refresh Columns
            mcw.AcquireConnections(null);
            mcw.ReinitializeMetaData();
            mcw.ReleaseConnections();

        }
        private void MappColumns(IDTSComponentMetaData100 icmDest, CManagedComponentWrapper mcwDest)
        {
            //mappings

            IDTSInput100 inpDestination = icmDest.InputCollection[0];
            IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 vinpcDestination = (IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;


            foreach (IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
            {
                try
                {
                    IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_READONLY);
                    // map external column
                    IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
                    mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
                }
                catch
                {
                    IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);
                    // map external column

                }

            }


        }

        protected void Dispose(bool disposing)
        {
            if (disposing)
            {
                // dispose managed resources
                myPackage.Dispose();
            }
            // free native resources
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }



    }
}

Contact Form

Name

Email *

Message *