January 09, 2015

Convert Unicode File into ASCII

There is always improvements on code, I wrote this awhile back, but I have to fixed a few things
here is the new code, the ideal would be to create an ssis custom component but that would be next, in the meantime I'm enjoying working at Fanatics. Ah... before I forget I would be a speaker for SqlSaturday 2015 in Tampa....Thank You ....
/*
   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.IO;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Text;

namespace ST_78e778bf841c4fed8969b1315fe198d0.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Global Variables
        String Archive = "";
        String Import = "";
        String Load = "";
        String Hold = "";
        String Scrubber = "";
        String Expression = @"[^\u0000-\u007F]";
        Encoding ascii = Encoding.ASCII;
        int Rows2Process = 100000;
        Variables vars = null;
        #endregion
        public void Main()
        {
            // TODO: Add your code here
            initialized();
            MoveFiles(Import, Scrubber);

            FileScrubber(Scrubber);
            MoveFiles(Hold, Load);

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        // Initialize Variables 
        private void initialized()
        {

            Dts.VariableDispenser.LockForRead("User::strArchive");
            Dts.VariableDispenser.LockForRead("User::strImport");
            Dts.VariableDispenser.LockForRead("User::strLoad");
            Dts.VariableDispenser.LockForRead("User::strHold");
            Dts.VariableDispenser.LockForRead("User::strScrubber");
            Dts.VariableDispenser.GetVariables(ref vars);
            Archive = vars["User::strArchive"].Value.ToString();
            Import = vars["User::strImport"].Value.ToString();
            Load = vars["User::strLoad"].Value.ToString();
            Hold = vars["User::strHold"].Value.ToString();
            Scrubber = vars["User::strScrubber"].Value.ToString();
        }

        // Start Scrubbing Mode 
        private void FileScrubber(String DirectoryPath)
        {
            List<String> _FileList = GetDirectoryFiles(DirectoryPath);
            // Create the new Empty File on the Hold Directory
            List<String> _Rows2Clean = new List<string>();
            foreach (String FullFilePath in _FileList)
            {
                int RecordCnt = 0;
                String FileName = GetFileName(FullFilePath);
                String NewPath = Hold + "\\" + FileName;
                // CreateEmptyFile(NewPath);
                using (FileStream sf = File.Open(FullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    StreamReader sr = new StreamReader(sf);
                    String row = string.Empty;

                    while ((row = sr.ReadLine()) != null)
                    {
                        
                        _Rows2Clean.Add(row);
                        RecordCnt++;
                        if (_Rows2Clean.Count >= Rows2Process)
                        {
                            WriteNewFile(ref _Rows2Clean, NewPath);
                            _Rows2Clean.Clear();
                        }
                    }
                    sf.Close();
                    sr.Dispose();

                }
                if (_Rows2Clean.Count >= 1)
                {
                    WriteNewFile(ref _Rows2Clean, NewPath);
                    _Rows2Clean.Clear();
                }
               
               
                //MessageBox.Show(@"Records Count:" + RecordCnt.ToString() + " FileName " + FileName);
                File.Move(FullFilePath, Archive+"\\scrub_"+FileName);
                NewPath = null;
                FileName = null;
            }
            _FileList.Clear();
            _FileList = null;
            _Rows2Clean.Clear();
            _Rows2Clean = null;

        }
        private String GetFileName(String FullFilePath)
        {
            FileInfo _fileInfo = new FileInfo(FullFilePath);

            String FileName = String.Empty;
            if (_fileInfo.Exists)
            {
                FileName = _fileInfo.Name.ToString().Trim();

            }

            _fileInfo = null;
            return FileName;
        }
        private String RemoveUnicode(String inputString)
        {
            string asAscii = Encoding.ASCII.GetString(
                                                        Encoding.Convert(
                                                        Encoding.UTF8,
                                                        Encoding.GetEncoding(
                                                        Encoding.ASCII.EncodingName,
                                                        new EncoderReplacementFallback(string.Empty),
                                                        new DecoderExceptionFallback()
                                                        ),
                                                        Encoding.UTF8.GetBytes(inputString)
                                                        )
                                                        );
            return asAscii;
        }
        private void WriteNewFile(ref List<String> RowsToWrite, String FullFilePath)
        {
            //s = Regex.Replace(s, @"[^A-Za-z0-9]", "");
            List<String> NewRows2Write = new List<String>();
            foreach (String Row in RowsToWrite)
            {
                String regRow = Regex.Replace(Row, Expression, "");

                NewRows2Write.Add(RemoveUnicode(regRow));
                regRow = String.Empty;

            }
            RowsToWrite.Clear();

            using (FileStream sf = File.Open(FullFilePath, FileMode.Append, FileAccess.Write, FileShare.None))
            {
                using (StreamWriter sw = new StreamWriter(sf))
                {
                    foreach (String newRow in NewRows2Write)
                    {

                        sw.WriteLine(newRow);

                    }
                    sw.Dispose();
                    NewRows2Write.Clear();
                    NewRows2Write = null;
                }
                sf.Close();


            }





        }
        private void CreateEmptyFile(String FullFilePath)
        {
            FileInfo _file = new FileInfo(FullFilePath);

            if (!_file.Exists)
            {
                _file.Create();
                _file = null;

            }
            else
            {
                _file = null;
            }

        }
        // Create a list of files in the Import Folder for scrubbing

        private List<String> GetDirectoryFiles(String FullFilePath)
        {

            String[] fdir = Directory.GetFiles(FullFilePath);
            List<String> files = new List<string>(fdir);

            fdir = null;

            return files;
        }


        // Move Files to Load Directory
        private void MoveFiles(String FromPath, String ToPath)
        {
            List<String> FromFiles = GetDirectoryFiles(FromPath);
            foreach (String FullFilePath in FromFiles)
            {

                Boolean IsLock = IsFileLocked(FullFilePath);
                if (!IsLock)
                {
                    FileInfo _finfo = new FileInfo(FullFilePath);
                    FileInfo _scF = new FileInfo(ToPath + "\\" + _finfo.Name.ToString());
                    if (!_scF.Exists)
                    {

                        _finfo.MoveTo(_scF.FullName);

                    }
                    else
                    {
                        File.Delete(_scF.FullName);
                        _finfo.MoveTo(_scF.FullName);
                    }

                }


            }
            FromFiles = null;


        }
        //Check if Files are lock If lock file won't be move to scrubber
        protected virtual bool IsFileLocked(String FullFilePath)
        {
            FileStream stream = null;

            try
            {
                stream = File.Open(FullFilePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
            }
            catch (IOException)
            {
                //the file is unavailable because it is:
                //still being written to
                //or being processed by another thread
                //or does not exist (has already been processed)
                return true;
            }
            finally
            {
                if (stream != null)
                    stream.Close();
            }

            //file is not locked
            return false;
        }
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

January 02, 2015

Happy 2015

Happy New Year 2015 to everyone...
/*
   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.IO;

namespace ST_5dc7efcc7bc2413ebd429a77fa9f1964.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.
      */

        public void Main()
        {
        String path  = Dts.Variables["source_file_unc"].Value.ToString();
        FileInfo fi  = new FileInfo(path);
        String file_name = "";
        String result = "success";

        String  archive_folder_path = Dts.Variables["archive_folder_path"].Value.ToString();
        String  dups_folder_path  = Dts.Variables["dups_folder_path"].Value.ToString();
        String error_folder_path  = Dts.Variables["error_folder_path"].Value.ToString();
        String target_folder_path = Dts.Variables["target_folder_path"].Value.ToString();
        try
        {
           
            /* verify the existence of the source file*/
            if (!fi.Exists) {
                //MsgBox(path, MsgBoxStyle.Critical, "Invalid source_file_unc")
                throw new FileNotFoundException("The source_file_unc (" + path + ") does not exist!");
            }
       

            //folder/path verification archive_folder_path
            if (!System.IO.Directory.Exists(archive_folder_path)){
                //'MsgBox(archive_folder_path, MsgBoxStyle.Critical, "Invalid archive_folder_path")
                throw new FileNotFoundException("The archive_folder_path (" + archive_folder_path + ") does not exist!");
            }

            if (!System.IO.Directory.Exists(dups_folder_path)){
            //'MsgBox(dups_folder_path, MsgBoxStyle.Critical, "Invalid dups_folder_path")
                throw new FileNotFoundException("The dups_folder_path (" + dups_folder_path + ") does not exist!");
            }

            if (!System.IO.Directory.Exists(error_folder_path)){
                //'MsgBox(error_folder_path, MsgBoxStyle.Critical, "Invalid error_folder_path")
                throw new FileNotFoundException("The error_folder_path (" + error_folder_path + ") does not exist!");
            }
          

            if (!System.IO.Directory.Exists(target_folder_path)){
                //'MsgBox(target_folder_path, MsgBoxStyle.Critical, "Invalid target_folder_path")
                throw new FileNotFoundException("The target_folder_path (" + target_folder_path + ") does not exist!");
            }
              //' extract file information to create the working directory (package variable: target_file_path)
            file_name = fi.Name;
            String []file_Name_Parts  = file_name.Split('.');
            int upper = file_Name_Parts.GetUpperBound(0);
            int lower = file_Name_Parts.GetLowerBound(0);
            Dts.Variables["extract_date"].Value = fi.CreationTime.ToString();
            Dts.Variables["file_extension"].Value = file_Name_Parts [upper];
            Dts.Variables["file_name"].Value = file_name;
            Dts.Variables["target_file_path"].Value = System.IO.Path.Combine(target_folder_path, file_name);
            
        }
        catch
        {
        }
        finally
        {
        }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }

}

November 19, 2014

File Compression using SSIS

Ok,
I have been a little busy lately doing a lot of learning from a financial background into retail and as part of the been a developer I have a task to compress very large files for sftp. So, after a couple of hours a wrote the below code. Hopefully it will safe someone's the pain of trying to this for sql 2008.

/*
   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.IO.Compression;
using System.IO;

namespace ST_e131a658cb92485b8392d785449083bc.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.
*/
        
        public void Main()
        {
            // TODO: Add your code here

            String FullFilePath = Dts.Variables["User::strFullFilePath"].Value.ToString();
            Compress(FullFilePath); 
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public static void Compress(string FileSelected)
        {

            int BufferSize = 2048;
            Int64 bytesRead = 0;
            byte[] buffer = new byte[BufferSize];

            FileInfo fileToCompress = new FileInfo(FileSelected);
            Int64 bytesInFile = fileToCompress.Length;
            if (fileToCompress.Exists)
            {
                using (FileStream originalFileStream = fileToCompress.OpenRead())
                {
                    if ((File.GetAttributes(fileToCompress.FullName) &
                       FileAttributes.Hidden) != FileAttributes.Hidden & fileToCompress.Extension != ".gz")
                    {
                       
                        using (FileStream compressedFileStream = File.Create(fileToCompress.FullName + ".gz"))
                        {
                            using (GZipStream compressionStream = new GZipStream(compressedFileStream,
                               CompressionMode.Compress, true))
                            {
                                do
                                {
                                    int FileBytes = originalFileStream.Read(buffer, 0, buffer.Length);
                                    if (FileBytes == 0)
                                    {
                                        break;
                                    }
                                    compressionStream.Write(buffer, 0, buffer.Length);
                                    bytesRead += FileBytes;
                                } while (bytesRead < bytesInFile);

                            }
                        }
                        //FileInfo info = new FileInfo(directoryPath + "\\" + fileToCompress.Name + ".gz");
                        //Console.WriteLine("Compressed {0} from {1} to {2} bytes.",
                        //fileToCompress.Name, fileToCompress.Length.ToString(), info.Length.ToString());
                    }

                }
            }
        }
            
           
        
        
       

    }
    
      
}

Contact Form

Name

Email *

Message *