#title SSIS JSON

{{{
gacutil –i Newtonsoft.Json.dll
}}}

{{{
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Newtonsoft.Json.Linq;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */

        var blobLength = Convert.ToInt32(Row.jsonlog.Length);
        var blobData = Row.jsonlog.GetBlobData(0, blobLength);
        var json = System.Text.Encoding.Unicode.GetString(blobData);
        //System.Windows.Forms.MessageBox.Show(json);

        JObject obj = JObject.Parse(json);

        Output1Buffer.AddRow();
        foreach (var pair in obj)
        {
            switch (pair.Key)
            {
                case "cnsm_tp":
                    Output1Buffer.cnsmtp = int.Parse(pair.Value.ToString());
                    break;
                case "a":
                    Output1Buffer.a = pair.Value.ToString();
                    break;
            }
        }        
    }


    

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
    }

}

}}}


예외처리
 * http://msdn.microsoft.com/ko-kr/library/kw5aaea4(v=vs.80).aspx

json schema
 * http://james.newtonking.com/projects/json/help/html/JsonSchema.htm

참조
 * System.Web.Extensions
 * Microsoft.CSharp
{{{
#region Help:  Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services data flow.
 *
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script component. */
#endregion

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization; //추가
using Microsoft.CSharp; //추가
#endregion

/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Help:  Using Integration Services variables and parameters
    /* To use a variable in this script, first ensure that the variable has been added to
     * either the list contained in the ReadOnlyVariables property or the list contained in
     * the ReadWriteVariables property of this script component, according to whether or not your
     * code needs to write into the variable.  To do so, save this script, close this instance of
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
     * Script Transformation Editor window.
     * To use a parameter in this script, follow the same steps. Parameters are always read-only.
     *
     * Example of reading from a variable or parameter:
     *  DateTime startTime = Variables.MyStartTime;
     *
     * Example of writing to a variable:
     *  Variables.myStringVariable = "new value";
     */
    #endregion

    #region Help:  Using Integration Services Connnection Managers
    /* Some types of connection managers can be used in this script component.  See the help topic
     * "Working with Connection Managers Programatically" for details.
     *
     * To use a connection manager in this script, first ensure that the connection manager has
     * been added to either the list of connection managers on the Connection Managers page of the
     * script component editor.  To add the connection manager, save this script, close this instance of
     * Visual Studio, and add the Connection Manager to the list.
     *
     * If the component needs to hold a connection open while processing rows, override the
     * AcquireConnections and ReleaseConnections methods.
     * 
     * Example of using an ADO.Net connection manager to acquire a SqlConnection:
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
     *
     * Example of using a File connection manager to acquire a file path:
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
     *  string filePath = (string)rawConnection;
     *
     * Example of releasing a connection manager:
     *  Connections.SalesDB.ReleaseConnection(rawConnection);
     */
    #endregion

    #region Help:  Firing Integration Services Events
    /* This script component can fire events.
     *
     * Example of firing an error event:
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
     *
     * Example of firing an information event:
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
     *
     * Example of firing a warning event:
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
     */
    #endregion

    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called once for every row that passes through the component from 입력0.
    ///
    /// Example of reading a value from a column in the the row:
    ///  string zipCode = Row.ZipCode
    ///
    /// Example of writing a value to a column in the row:
    ///  Row.ZipCode = zipCode
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void 입력0_ProcessInputRow(입력0Buffer Row)
    {
        /*
         * Add your code here
         */       

        var jss = new JavaScriptSerializer();
        var data = jss.Deserialize<dynamic>(Row.json);
        
        if (data["family"].ContainsKey("father"))
                System.Windows.Forms.MessageBox.Show(data["family"]["father"]);

/*
        if (data.ContainsKey("p_key")) Row.pkey = Int64.Parse(data["p_key"].ToString());
        Row.playenddate = Convert.ToDateTime(data["play_end_date"].ToString());
        Row.speeditemyn = Convert.ToBoolean(int.Parse(data["speed_item_yn"].ToString()));
        Row.plusexp = int.Parse(data["plus_exp"].ToString());
        Row.hands = data["hands"].ToString();

        string s = (string)data["hands"];
        string[] hands = s.Split(',');
        Int64 s_key = Int64.Parse(data["s_key"]);

        foreach (string word in hands)
        {
            handsBuffer.AddRow();
            handsBuffer.skey = s_key;
            handsBuffer.card = word;
        }
*/

    }
}

}}}


{{{

select
    'Row.' + replace(column_name, '_', '') + ' = ' + 
    case 
        when data_type = 'varchar' then '(string)' + 'data["' + column_name + '"];'
        when data_type = 'int' then 'int.Parse(' + 'data["' + column_name + '"]);'
        when data_type = 'bigint' then 'Int64.Parse(' + 'data["' + column_name + '"]);'
        when data_type = 'smallint' then 'Int16.Parse(' + 'data["' + column_name + '"]);'
        when data_type = 'tinyint' then 'Convert.ToByte(' + 'data["' + column_name + '"]);'
        when data_type = 'bit' then 'Convert.ToBoolean(int.Parse(' + 'data["' + column_name + '"]));'
        when data_type = 'datetime' then 'Convert.ToDateTime(' + 'data["' + column_name + '"]);'
    end
,   *
from information_schema.columns
where table_name = 'room_instance_inout_history'
and column_name not in ('reg_dt', 'std_dt')


}}}

{{{
/*
 * Created by SharpDevelop.
 * User: dwa2007
 * Date: 2012-12-21
 * Time: 오후 4:04
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using Newtonsoft.Json.Linq;

namespace json
{
	class Program
	{
		public static void Main(string[] args)
		{
			string json = @"{
			   ""이름"": ""테스트"",
			   ""나이"": 25,
			   ""성별"": ""여"",
			   ""기혼"": true,
			   ""주소"": ""서울특별시 양천구 목동"",
			   ""특기"": [""농구"", ""도술""],
			   ""가족관계"": {""#"": 2, ""아버지"": ""홍판서"", ""어머니"": ""춘섬""},
			   ""회사"": ""경기 안양시 만안구 안양7동""
			}";
			
			//Newtonsoft.Json.
			JObject obj = JObject.Parse(json);
			
			string name = (string)obj["이름"];
			int age = (int)obj["나이"];
			string gender = (string)obj["성별"];		

			//Type
			Console.WriteLine(obj["특기"].Type);	
			Console.WriteLine(obj["가족관계"].Type);	
			
			//Array
			JArray specialty = (JArray)obj["특기"];
			string specialty1 = (string)specialty[0];
			string specialty2 = (string)specialty[1];
			
			//Object
			JObject obj_family = JObject.Parse(obj["가족관계"].ToString());
			int family_cnt = (int)obj_family["#"];
			
			
			Console.WriteLine("이름= {0}, 나이={1}, 성별={2}, 특기={3}, 가족수={4}", name, age, gender, specialty1, family_cnt);
			
			// TODO: Implement Functionality Here
			
			string dt = "2013-01-22 10:17:25.917";
			Console.WriteLine(Convert.ToDateTime(dt));
			
			Console.Write("Press any key to continue . . . ");
			Console.ReadKey(true);
		}
	}
}
}}}

ssis 2008 r2 version(.net framework 3.5)
{{{
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization; //추가
using System.Collections.Generic; //추가

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        //Row.jsonlog의 데이터 타입이 NTEXT인 경우 blob -> string으로 바꿔줘야 한다.
        var blobLength = Convert.ToInt32(Row.jsonlog.Length);
        var blobData = Row.jsonlog.GetBlobData(0, blobLength);
        var json = System.Text.Encoding.Unicode.GetString(blobData);

        var jss = new JavaScriptSerializer();
        var data = jss.Deserialize<Dictionary<string, string>>(json);

        Output1Buffer.AddRow();
        if (data.ContainsKey("mbid")) Output1Buffer.mbid = int.Parse(data["mbid"].ToString());      

    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
    }

}
}}}

NT_TEXT to string
{{{
var blobLength = Convert.ToInt32(blobColumn.Length);
var blobData = blobColumn.GetBlobData(0, blobLength);
var stringData = Encoding.Unicode.GetString(blobData);
}}}


{{{
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Newtonsoft.Json.Linq;

    public override void 입력0_ProcessInputRow(입력0Buffer Row)
    {
        /*
         * Add your code here
         */

        //Blob To String
        var blobLength = Convert.ToInt32(Row.pcdata.Length);
        var blobData = Row.pcdata.GetBlobData(0, blobLength);
        var json = System.Text.Encoding.Unicode.GetString(blobData);
        //System.Windows.Forms.MessageBox.Show(json);

        JObject data = JObject.Parse(json);
        JArray  preset_array = (JArray)data["Preset"];

        //Output1Buffer.AddRow();
        foreach (JObject pair in preset_array)
        {
            if (pair.ContainsKey("Tier"))
            {
                JArray tier_array = (JArray)pair["Tier"];
                foreach (JObject arr in tier_array)
                {
                    Output1Buffer.AddRow();
                    if (pair.ContainsKey("Id")) Output1Buffer.id = (int)pair["Id"];
                    if (pair.ContainsKey("Option")) Output1Buffer.option = pair["Option"].ToString(Newtonsoft.Json.Formatting.None);
                    Output1Buffer.datekey = Row.datekey;

                }
            }
        }
}}}