July 11, 2016

StreamReading StreamWriting Using SSIS Script Task

How many times you need to stream reading from a file and writing into another well here it is...Here we are using special character char(30) and char(31) as delimited so a quick replace using regex did it. It's Simple,Quick, and Dirty :-)
Enjoy.
/*
   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 System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;

namespace ST_2b1bee4bed9c4b0b8499c0b8215df02d.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    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.
*/

        public void Main()
        {
            String filePath = Dts.Variables["User::strTempFilePath"].Value.ToString();
            //char US = (char) 31;
            //char RS = (char)30;
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001F_",US.ToString()));
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001E_", RS.ToString()));
            ReplaceFile(filePath, filePath.Replace(".cHold", ".txt"));
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        protected void ReplaceFile(String ImportFilePath, String ExportFilePath)
        {
           // H201606301639RowCount:50000
                //User::intCntCustomer,User::strTempFilePath,User::YYYYMMDDHHMMSS
            
            char US = (char)31;
            char RS = (char)30;
            String Header = "H"+Dts.Variables["User::YYYYMMDDHHMMSS"].Value.ToString()+"Rowcount:"+Dts.Variables["User::intCntCustomer"].Value.ToString();
            using (StreamReader vReader = new StreamReader(ImportFilePath))
            {
                using (StreamWriter vWriter = new StreamWriter(ExportFilePath))
                {
                    vWriter.WriteLine(Header);
                    while (!vReader.EndOfStream)
                    {
                        String vLine = Regex.Replace(Regex.Replace(vReader.ReadLine(), "_x001F_", US.ToString()), "_x001E_", RS.ToString());
                        vWriter.WriteLine(vLine);


                    }
                }
            }
        }

    }
}

1 comment:

  1. SSIS is the most useful component for creating dashboards for end users and hence I really feel it should be used as much as possible.

    SSIS Postgresql Read

    ReplyDelete

Contact Form

Name

Email *

Message *