|
#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 |
|
// Add References: Microsoft.CSharp, System.Net.Http, System.Web.Extensions |
|
using System; |
|
using System.Data; |
|
using System.Collections.Generic; |
|
using System.Net; |
|
using System.Net.Http; |
|
using System.Net.Http.Headers; |
|
using System.Web.Script.Serialization; |
|
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; |
|
using Microsoft.SqlServer.Dts.Runtime.Wrapper; |
|
#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 |
|
|
|
private String sfAPIVersion, sfConsumerKey, sfConsumerSecret, sfUserName, sfPassword, sfToken, sfOauthToken, sfServiceUrl; |
|
private HttpClient sfClient; |
|
|
|
/// <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(); |
|
|
|
// Better to take these from sensitive project parameters |
|
sfUserName = "email@example.com"; // = Variables.UserName; |
|
sfPassword = "examplepassword"; // = Variables.Password; |
|
sfToken = "xxxxxxxxxxxxxxxxxxxxx"; // = Variables.Token; |
|
sfAPIVersion = "v44.0"; // = Variables.APIVersion; |
|
sfConsumerKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // = Variables.ConsumerKey; |
|
sfConsumerSecret = "xxxxxxxxxxxxxx"; // = Variables.ConsumerSecret; |
|
|
|
Authenticate(); |
|
} |
|
|
|
/// <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 |
|
*/ |
|
} |
|
|
|
public override void CreateNewOutputRows() |
|
{ |
|
String sfQuery = "SELECT+Id,IsDeleted,Name,SystemModstamp+FROM+Account"; |
|
// Want to select * from table? Use the AllFields method to extract them from the table definition |
|
// String sfQuery = "SELECT+" + AllFields(Variables.TableName) + "+FROM+Account"; |
|
|
|
// Want to select only changed records? Date needs to be in ISO 8601 UTC format: 2001-01-31T21:01:50.000Z |
|
// sfQuery += "+WHERE+SystemModstamp+>=+" + Variables.ExtractFromDateTime.ToString("yyyy-MM-ddTHH:mm:ss.000Z"); |
|
|
|
String sfRestQuery = sfServiceUrl + "/services/data/" + sfAPIVersion + "/query?q=" + sfQuery; |
|
|
|
Boolean sfQueryDone = false; |
|
while (sfQueryDone == false) |
|
{ |
|
HttpRequestMessage sfRequest = new HttpRequestMessage(HttpMethod.Get, sfRestQuery); |
|
sfRequest.Headers.Add("Authorization", "Bearer " + sfOauthToken); |
|
sfRequest.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); |
|
HttpResponseMessage sfResponse = sfClient.SendAsync(sfRequest).Result; |
|
String sfResponseString = sfResponse.Content.ReadAsStringAsync().Result; |
|
|
|
JavaScriptSerializer serialiser = new JavaScriptSerializer(); |
|
serialiser.MaxJsonLength = 50 * 1000000; // Increased to deserialize large number of fields |
|
dynamic sfResult = serialiser.DeserializeObject(sfResponseString); |
|
|
|
foreach (var sfRecord in sfResult["records"]) |
|
{ |
|
Output0Buffer.AddRow(); |
|
|
|
// Sample fields |
|
if (sfRecord["Id"] != null) |
|
Output0Buffer.Id = (String)sfRecord["Id"]; |
|
if (sfRecord["IsDeleted"] != null) |
|
Output0Buffer.IsDeleted = (Boolean)sfRecord["IsDeleted"]; |
|
if (sfRecord["Name"] != null) |
|
Output0Buffer.Name = (String)sfRecord["Name"]; |
|
if (sfRecord["SystemModstamp"] != null) |
|
Output0Buffer.SystemModstamp = DateTime.Parse(sfRecord["SystemModstamp"]); |
|
} |
|
|
|
sfQueryDone = (Boolean)sfResult["done"]; |
|
if (!sfQueryDone) |
|
sfRestQuery = sfServiceUrl + (String)sfResult["nextRecordsUrl"]; |
|
} |
|
} |
|
|
|
private void Authenticate() |
|
{ |
|
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; |
|
HttpClientHandler sfClientHandler = new HttpClientHandler(); |
|
sfClientHandler.Proxy = WebRequest.GetSystemWebProxy(); |
|
sfClientHandler.Proxy.Credentials = CredentialCache.DefaultCredentials; // or new NetworkCredential("username","password","DOMAIN"); |
|
sfClientHandler.UseProxy = true; |
|
sfClient = new HttpClient(sfClientHandler); |
|
|
|
HttpContent sfRequestContent = new FormUrlEncodedContent(new Dictionary<string, string> |
|
{ |
|
{"grant_type","password"}, |
|
{"client_id",sfConsumerKey}, |
|
{"client_secret",sfConsumerSecret}, |
|
{"username",sfUserName}, |
|
{"password",sfPassword + sfToken} |
|
} |
|
); |
|
|
|
HttpResponseMessage sfResponse = sfClient.PostAsync("https://login.salesforce.com/services/oauth2/token", sfRequestContent).Result; |
|
|
|
String sfResponseString = sfResponse.Content.ReadAsStringAsync().Result; |
|
|
|
JavaScriptSerializer sfSerialiser = new JavaScriptSerializer(); |
|
dynamic sfResult = sfSerialiser.DeserializeObject(sfResponseString); |
|
|
|
sfOauthToken = (String)sfResult["access_token"]; |
|
sfServiceUrl = (String)sfResult["instance_url"]; |
|
} |
|
|
|
private String AllFields(String sfObjectName) |
|
{ |
|
String sfRestQuery = sfServiceUrl + "/services/data/" + sfAPIVersion + "/sobjects/" + sfObjectName + "/describe/"; |
|
HttpRequestMessage sfRequest = new HttpRequestMessage(HttpMethod.Get, sfRestQuery); |
|
sfRequest.Headers.Add("Authorization", "Bearer " + sfOauthToken); |
|
sfRequest.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); |
|
HttpResponseMessage sfResponse = sfClient.SendAsync(sfRequest).Result; |
|
String sfResponseString = sfResponse.Content.ReadAsStringAsync().Result; |
|
|
|
JavaScriptSerializer serialiser = new JavaScriptSerializer(); |
|
dynamic sfResult = serialiser.DeserializeObject(sfResponseString); |
|
|
|
String outputFieldList = ""; |
|
foreach (var sfField in sfResult["fields"]) |
|
{ |
|
if (outputFieldList.Length != 0) |
|
outputFieldList += ","; |
|
outputFieldList += (String)sfField["name"]; |
|
} |
|
|
|
return outputFieldList; |
|
} |
|
} |
I'm struggling to understand the part "Add output columns with correct types" - are you referring to adding an Outpout column on the script component itself - and if so "what columns"? My first test was to see if I could connect to my SF instance
Edit: I'm a DW SQL guy - so what was biting me was the case sensitive Output columns - it seems to compile after many hours of wacking at it. Back to getting it talking to my SF instance!