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());
                    }

                }
            }
        }
            
           
        
        
       

    }
    
      
}

November 02, 2014

Parent Package calling a child package

As my first month at Fanatics I have to get used to the concept of parent child packages using standard ssis. Normally I write my code for the packages on the metadata and then let c# take care of the rest. However, I didn't build it, I inherit it, so, first I'm going to create a few prototypes on how to do this using code to generate the standard templates, and also start building something more usesful, it will take time but I do love a challenge.

Contact Form

Name

Email *

Message *