August 1, 2017

Converting String to GUID

DECLARE @STRING NVARCHAR(50) = '447d16b308b24073ac5a7bf07c14bc8g'

SET @STRING  = REPLACE(REPLACE(@str,'0x',''), '-' ,'')

SET @STRING  = STUFF(STUFF(STUFF(
STUFF(@str,21,0,'-')
,17,0,'-')
,13,0,'-')
,9,0,'-')

SELECT CAST(@STRING AS UNIQUEIDENTIFIER)

April 28, 2017

SSIS: Script Component Asynchronous Transformation

We can configure the SSIS Script component output as a Synchronous or Asynchronous as required. Using the Synchronous setting each input row is processed as it passes through the component, where as Asynchronous setting waits until multiple input rows have been received before processing.

I had to use this when I was integrating two system using SSIS package. The scenario was to send the massive amount of data in a chunk from the source system into the other system in the JSON format. This Asynchronous setting helped to send the data in a limited batch size.

In order to configure in Asynchronous setting, we need to configure Output 0 to be an asynchronous output. Click on Output 0 in the Inputs and outputs and set the Synchronous property to None.



February 6, 2017

Loading Dll from Embedded Resource to SSIS Script Component

1) Add dll file as a reference.
2) Added dll must be saved in the project as a Embedded Resource.
        a) Right click on the added dll file
        b) Build Action -> Embedded Resource
        c) Copy to Output Directory -> Do not Copy


Use the script below:

 static ScriptMain()
    {
        AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
    }
    static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
        Assembly executingAssembly = Assembly.GetExecutingAssembly();

        if (args.Name.Contains("AddedFile1"))
        {
            if (AddedFile1 == null)
            {
                using (Stream stream = executingAssembly.GetManifestResourceStream("AssemblyName.AddedFile1.dll"))
                {
                    byte[] assemblyRawBytes = new byte[stream.Length];
                    stream.Read(assemblyRawBytes, 0, assemblyRawBytes.Length);
                    IdentityModel = Assembly.Load(assemblyRawBytes);
                }
            }
            return AddedFile1;
        }
        else if (args.Name.Contains("AddedFile2"))
        {
            if (Newtonsoft == null)
            {
                using (Stream stream = executingAssembly.GetManifestResourceStream("AssemblyName.AddedFile2.dll"))
                {
                    byte[] assemblyRawBytes = new byte[stream.Length];
                    stream.Read(assemblyRawBytes, 0, assemblyRawBytes.Length);
                    Newtonsoft = Assembly.Load(assemblyRawBytes);
                }
            }
            return AddedFile2;
        }
        return null;
    }

We can get AssemblyName from the properties of the script component project.