Cheers.
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using Amazon.KeyManagementService;
using Amazon.KeyManagementService.Model;
using Amazon.S3;
using Amazon.S3.Model;
using System.Security.Cryptography;
using System.Threading.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
#endregion
namespace ST_69f7c19bae0d4e359409f7ed3cdecd0f
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* 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 task, according to whether or not your
* code needs to write to the variable. To add the variable, 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:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
try
{
/*
User::objRawData,User::strAmazonKey,User::strAmazonSecretKey,
* User::strARN,User::strBucketDirectory,
* User::strBucketName,User::strFileName
* */
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
//Variables
var tbl = GetJsonRawData(Dts.Variables["User::objRawData"].Value); // Table Object
int val = (int)Dts.Variables["User::intLoopController"].Value; // File Interactiption
var FileName = Dts.Variables["User::strFileName"].Value.ToString()+val.ToString()+".txt"; // File Name
String BucketDirectory = Dts.Variables["User::strBucketDirectory"].Value.ToString();// Bucket Directory
if (!String.IsNullOrEmpty(BucketDirectory))
{
FileName = BucketDirectory + "/" + FileName;
}
String AmazonKey = Dts.Variables["User::strAmazonKey"].Value.ToString();
String AmazonSecKey = Dts.Variables["User::strAmazonSecretKey"].Value.ToString();
String ARN = Dts.Variables["User::strARN"].Value.ToString();
String BucketName = Dts.Variables["User::strBucketName"].Value.ToString();
Stream input = GetRawData(tbl);
Encrypt(input, AmazonKey, AmazonSecKey, ARN,FileName,BucketName);
input = null;
Dts.Variables["User::intLoopController"].Value = val + 1;
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
private DataTable GetJsonRawData(object DataSetData)
{
OleDbDataAdapter oleDb = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDb.Fill(dt, DataSetData);
return dt;
}
private Stream GetRawData(DataTable dt)
{
StringBuilder rawdata = new StringBuilder();
try
{
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
byte[] json = (byte[])dr[dc];
rawdata.Append(Encoding.UTF8.GetString(json));
}
rawdata.Append(Environment.NewLine);
}
}
return new MemoryStream(Encoding.UTF8.GetBytes(rawdata.ToString()));
}
catch (Exception e)
{
Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
return new MemoryStream();
}
}
/*
* //string dk = Convert.ToBase64String(dataKey.CiphertextBlob.ToArray());
//algorithm.Key = dataKey.Plaintext.ToArray();
//string k = Convert.ToBase64String(dataKey.Plaintext.ToArray());
//byte[] Iv = ASCIIEncoding.UTF8.GetBytes(System.Guid.NewGuid().ToString().Substring(0, 12));
//string siv = Convert.ToBase64String(algorithm.IV);
//byte[] iv = Encoding.UTF8.GetBytes("0123456789123456");
//algorithm.IV = iv;
**/
private void Encrypt(Stream input, string AmazonKey,string AmazonSecretKey,string arn,String s3FileName,String s3BucketName)
{
try
{
string meta_datakey = "x-amz-meta-datakey";
string meta_iv = "x-amz-meta-iv";
string meta_encoder = "x-amz-meta-encoder";
string meta_encrypt = "x-amz-meta-clientside-algorithm";
string ProxyHost = Dts.Variables["User::strProxyHost"].Value.ToString();
Int32 ProxyPort =(Int32) Dts.Variables["User::intProxyPort"].Value;
using (var output = new MemoryStream())
{
var conf = new AmazonKeyManagementServiceConfig();
conf.ProxyHost = ProxyHost;
conf.ProxyPort = ProxyPort;
conf.RegionEndpoint = Amazon.RegionEndpoint.USEast1;
using (var kms = new AmazonKeyManagementServiceClient(AmazonKey, AmazonSecretKey, conf))
using (var algorithm = Aes.Create())
{
var dataKey = kms.GenerateDataKey(new GenerateDataKeyRequest
{
KeyId = arn,
KeySpec = DataKeySpec.AES_256,
});
using (var cryptoStream = new CryptoStream(
output,
algorithm.CreateEncryptor(dataKey.Plaintext.ToArray(), algorithm.IV),
CryptoStreamMode.Write))
{
input.CopyTo(cryptoStream);
cryptoStream.FlushFinalBlock();
var s3conf = new AmazonS3Config();
s3conf.ProxyHost = ProxyHost;
s3conf.ProxyPort = ProxyPort;
s3conf.RegionEndpoint = Amazon.RegionEndpoint.USEast1;
using (var s3client = new AmazonS3Client(AmazonKey, AmazonSecretKey, s3conf))
{
PutObjectRequest request = (new PutObjectRequest()
{
BucketName = s3BucketName,
Key = s3FileName,
InputStream = output
});
request.Metadata.Add(meta_encrypt, "AES_256");
request.Metadata.Add(meta_encoder,"Base 64");
request.Metadata.Add(meta_iv,Convert.ToBase64String(algorithm.IV));
request.Metadata.Add(meta_datakey,Convert.ToBase64String(dataKey.Plaintext.ToArray()));
s3client.PutObject(request);
}
}
}
}
}
catch (Exception e)
{
Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
void Decrypt(Stream output, string AmazonKey, string AmazonSecretKey, string arn, String s3FileName, String s3BucketName)
{
try
{
using (var input = new MemoryStream())
using (var kms = new AmazonKeyManagementServiceClient(AmazonKey, AmazonSecretKey, Amazon.RegionEndpoint.USEast1))
using (var algorithm = Aes.Create())
{
var length = input.ReadByte();
var buffer = new byte[length];
input.Read(buffer, 0, length);
var decryptedData = kms.Decrypt(new DecryptRequest
{
CiphertextBlob = new MemoryStream(buffer)
});
algorithm.Key = decryptedData.Plaintext.ToArray();
var iv = algorithm.IV;
input.Read(iv, 0, iv.Length);
algorithm.IV = iv;
using (var cryptoStream = new CryptoStream(input,
algorithm.CreateDecryptor(), CryptoStreamMode.Read))
{
cryptoStream.CopyTo(output);
}
}
}
catch (Exception e)
{
Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
if (args.Name.Contains("AWSSDK.Core"))
{
string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.Core.dll"));
}
if (args.Name.Contains("AWSSDK.KeyManagementService"))
{
string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.KeyManagementService.dll"));
}
if (args.Name.Contains("AWSSDK.S3"))
{
string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.S3.dll"));
}
return null;
}
#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
}
}
