Step 1 : Create Data flow Task
1) Oledb Source
2) Script Component
3) Oledb Destination
Main Part of Script Component
--------------------------------
/* 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.IO;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Web;
using System.Net;
using System.Xml;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string XmlPath = @"D:\Error.xml";
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)
{
string newsletterNames1 = "";
string newsletterNames2 = "";
if (File.Exists(XmlPath))
{
File.Delete(XmlPath);
}
Int64 Cust_SK = Row.CustSK;
string SourceName = Row.SourceName;
string dob = Row.DateOfBirth;
string url = Row.RegistrationUrl;
string state = Row.State;
string password = Row.Password;
string mobilenumber = Row.MobileNumber;
string userName = Row.UserName;
string firstName = Row.FirstName;
string addressLine2 = Row.AddressLine2;
string addressLine1 = Row.AddressLine1;
string zip = Row.Zip;
string lastName = Row.LastName;
string status = Row.Status;
string city = Row.City;
string country = Row.Country;
string gender = Row.Gender;
string Email = Row.EmailAddress;
string acceptTerms = Row.AcceptTerms;
string newsletters = Row.NewsLetterNames;
if ((newsletters != "") && newsletters.Contains(","))
{
string[] words = newsletters.Split(',');
newsletterNames1 = words[0];
newsletterNames2 = words[1];
}
string requestUrl1 = "user.dateOfBirth=" + dob + "®istration_url=" + url + "&user.address.state=" + state + "&passwordCriteria.password=" + password;
string requestUrl2 = "&user.mobileInfoList[0].number=" + mobilenumber + "&user.userName=" + userName + "&user.firstName=" + firstName;
string requestUrl3 = "&user.address.addressLine2=" + addressLine2 + "&user.address.addressLine1=" + addressLine1;
string requestUrl4 = "&passwordCriteria.confirmPassword=" + password + "&user.address.zip=" + zip + "&user.lastName=" + lastName;
string requestUrl5 = "&user.userAttributes['status']=" + status + "&user.address.city=" + city + "&user.address.country=" + country;
string requestUrl6 = "&user.userAttributes['gender']=" + gender + "&user.primaryEmailAddress.value=" + Email + "+&confirmEmail=" + Email;
string requestUrl7 = "&options['acceptTerms']=" + acceptTerms + "&newsletterCriteria.newsletterNames=" + newsletterNames1 + "&newsletterCriteria.newsletterNames=" + newsletterNames2;
string requestUrl = @"https://addresses.com/services/flow/ext-register?" + requestUrl1 + requestUrl2 + requestUrl3 + requestUrl4 + requestUrl5 + requestUrl6 + requestUrl7;
HttpWebRequest request = WebRequest.Create(requestUrl) as HttpWebRequest;
HttpWebResponse response = request.GetResponse() as HttpWebResponse;
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(response.GetResponseStream());
XmlDocument doc = xmlDoc;
XmlElement root = doc.DocumentElement;
ResultBuffer.AddRow(); /// Add row() we are adding each row for output for these we //need to make SynchronousInput_ID as "None".
// Output 0 in Inputs and Outputs Pane.
ResultBuffer.CustSK = Cust_SK;
ResultBuffer.SourceName = SourceName;
string type = root.Attributes["type"].Value;
if (type == "failure")
{
ResultBuffer.MSIBResponseID = 0;
doc.Save(XmlPath);
getXMLResponse(Cust_SK, SourceName, XmlPath);
}
if (type == "success")
{
ResultBuffer.MSIBResponseID = 1;
}
}
public void getXMLResponse(Int64 Cust_SK, string SourceName, string XmlPath)
{
var conn = this.Connections.Connection.AcquireConnection(null);
SqlConnection Connection = (SqlConnection)conn;
SqlCommand sqlCommand = new SqlCommand();
if (Connection.State == ConnectionState.Closed)
Connection.Open();
sqlCommand.Connection = Connection;
String updateQuery = "Update A Set A.Xml_Response= B.Xml_Response From (Select " + Cust_SK + " Cust_SK, '" + SourceName + "' Source_Name,CONVERT(xml, BulkColumn) Xml_Response FROM OPENROWSET(Bulk '" + XmlPath + "', SINGLE_BLOB) [rowsetresults])B Inner join Response A On A.cust_sk= B.cust_sk and A.Source_Name=B.Source_Name";
sqlCommand.CommandText = updateQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
String insertQuery = "Insert Into MSIB_Response(Cust_SK,Source_Name,Xml_Response) Select Cust_SK,Source_Name,Xml_Response From (Select " + Cust_SK + " Cust_SK,'" + SourceName + "' Source_Name,CONVERT(xml, BulkColumn) Xml_Response FROM OPENROWSET(Bulk '" + XmlPath + "', SINGLE_BLOB) [rowsetresults])B Where not exists (Select 1 from Response A where A.cust_sk= B.cust_sk and A.Source_name=B.Source_name)";
sqlCommand.CommandText = insertQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
Connection.Close();
sqlCommand.Dispose();
}
}
----------------------------------
To find CData message and Attributes we need to use this code. It reads data from XML response
XmlNode node = doc.DocumentElement.SelectSingleNode(@"//errors");
XmlNodeList pNode = node.ChildNodes;
foreach (XmlNode xn in pNode)
{
string Code = xn.Attributes["code"].Value;
string Field = xn.Attributes["field"].Value;
XmlNode childNode = xn.ChildNodes[0];
if (childNode is XmlCDataSection)
{
XmlCDataSection cdataSection = childNode as XmlCDataSection;
// MessageBox.Show(cdataSection.Value);
}
//MessageBox.Show(Code + ':' + Field);
}
-------------------------------------------
