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".
*/
}
}
語襴
json schema
谿語＾
- 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;
}
}
}