SQL-MSBI(SSIS,SSAS)-ASP.NET-MONGODB: SSIS

Showing posts with label SSIS. Show all posts

Create Batch file to Execute SSIS Package

SET PackageDir = D:\Job\Package
SET PackageName = Package.dtsx
SET ConfigDir = D:\Job\Config
SET ConfigName = date.dtsConfig

DTEXEC /F %PackageDir%\%PackageName% /CONFIGFILE %ConfigDir%\%ConfigName
/CHECKPOINTING OFF /REPORTING V

If you need to change the variable value in the package using batch you need to add following code
/SET "\Package.Variables[vServerType].Value";"P"

Full code:
DTEXEC /F %PackageDir%\%PackageName% /CONFIGFILE %ConfigDir%\%ConfigName /SET "\Package.Variables[vServerType].Value";"P" /CHECKPOINTING OFF /REPORTING V

Note: If this "vServerType" variable present in your config file , the value of this variable will not change during the execution time.

Note: If we use same Config file for different package,we should ensure the variables present in your config file whether present in package which are used.

Delete Files From A Particular Folder in Script Task using C# Code

Step 1
Create a SSIS variable "Folder_Path" and assign the value of file path
Step 2
Use Script Task
Step 3
Read the variable in ReadOnlyVariables Pane.

Step 4
Add the following code

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

namespace ST_21af426c5ff24863a648aab041051234.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

//To Delete the files from folder

public void Main()

{

String Folder = Dts.Variables["Folder_Path"].Value.ToString(); // Path

Array.ForEach(Directory.GetFiles(Folder), delegate(String path) { File.Delete(path); });

Dts.TaskResult = (int)ScriptResults.Success;

}

How to Send Web API request Through Script Component

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 + "&registration_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);

}
-------------------------------------------

Connection String in Script Task in SSIS

Connection String in Script Task in SSIS

Create ADO.net connection in Connection Managers Tab

(That is .net Providers\ SqlClient Data Provider)
(Connection String: Data Source= Server Name;Initial Catalog= Data base Name;Integrated Security=True;)

using System.Data.SqlClient;

SqlConnection Connection = (SqlConnection)Dts.Connections["Name of Connection manager(Ado connection)"].AcquireConnection(null);
SqlCommand sqlCommand = new SqlCommand();
if (Connection.State == ConnectionState.Closed)
Connection.Open();
sqlCommand.Connection = Connection;
sqlCommand.CommandText = "Write query here or give Stored procedure";
sqlCommand.CommandType = CommandType.StoredProcedure; // give type if it is stored procedure(SP)
sqlCommand.Parameters.AddWithValue("Parameter1", DbType.String).Value = variableName1.Trim(); // passing input parameter into SP
sqlCommand.Parameters.AddWithValue("EParameter2", DbType.String).Value = variableName2.Trim();

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataSet ds1 = new DataSet();
sqlDataAdapter.Fill(ds1);
Dts.Connections["Name of Connection manager(Ado connection)"].ReleaseConnection(null);
Connection.Close();
sqlCommand.Dispose();

How can we split the rows using Script Component transformation

We need to split the records according to Seats

Sample records:

1) Use Data Flow Task
a) Oledb Source
b)Script component transformation
c) Row sampling

Script to Split the Seats in row wise

/*
To split the rows with startValue and endValue and to store in Sql Server

*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using System.Text ;
using System.Text.RegularExpressions;
using System.Windows.Forms;

[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)
{

string sPattern = "to";
Regex reg = new Regex(sPattern, RegexOptions.IgnoreCase); // for regular expressions we need to add namespace using System.Text.RegularExpressions;

var mString =Row.Seats;
Match mMatch = reg.Match(mString); //checking for match with pattern and String

string FirstString="";
string LastString="";

int FirstLength=0;
int LastLength=0;

int startSeat = 0;
int endSeat = 0;

if (mMatch.Success)
{
FirstLength = mString.IndexOf(sPattern); // give the position of string
LastLength = FirstLength + sPattern.Length;
FirstString = mString.Substring(0, FirstLength);
startSeat = Convert.ToInt32(Regex.Replace(FirstString, @"[^\d]+", "")); // remove non numeric data
LastString = mString.Substring(LastLength, mString.Length - LastLength);
endSeat = Convert.ToInt32(Regex.Replace(LastString, @"[^\d]+", ""));
if (startSeat > endSeat)
{
int swap = startSeat;
startSeat = endSeat;
endSeat = swap;
}

while (startSeat <= endSeat)
{
ResultBuffer.AddRow(); // adding splitted rows into buffer
ResultBuffer.EventName1 = Row.EventName;
ResultBuffer.OpponentName1 = Row.OpponentName;

ResultBuffer.EventDate1 = Row.EventDate;
ResultBuffer.EventTime1 = Row.EventTime;
ResultBuffer.SeatSection1 = Row.SeatSection;
ResultBuffer.SeatRow1 = Row.SeatRow;
ResultBuffer.Seats1 = startSeat.ToString();
ResultBuffer.NbrOfSeats1 = Row.NbrOfSeats;

startSeat = startSeat + 1;

}

}
else
{
startSeat = Convert.ToInt32(Regex.Replace(mString, @"[^\d]+", ""));
ResultBuffer.AddRow();
ResultBuffer.EventName1 = Row.EventName;
ResultBuffer.OpponentName1 = Row.OpponentName;

ResultBuffer.EventDate1 = Row.EventDate;
ResultBuffer.EventTime1 = Row.EventTime;
ResultBuffer.SeatSection1 = Row.SeatSection;
ResultBuffer.SeatRow1 = Row.SeatRow;
ResultBuffer.Seats1 = startSeat.ToString();
ResultBuffer.NbrOfSeats1 = Row.NbrOfSeats;

}

}

}

Splitted Result:

How we can download data from website using Script task with C# code

How can we download data from website using Script task with C# code

/*
We have customer details in CSV files on monthly basis in http site of jcubefinance.com.
We need to download CSV files on the basis of date and create same date folder and load the files.
Example of files in http:
http://jcubefinance.com/customer/2012-01-01/customer.csv,
http://jcubefinance.com/customer/2012-02-01/customer.csv,
http://jcubefinance.com/customer/2012-03-01/customer.csv
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web;
using System.Net;
using System.Collections.Generic;

namespace ST_b30a48fe352a48ddbef16d2da49c11ae.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{

WebClient mySSISWebClient = new WebClient();
mySSISWebClient.Credentials = new NetworkCredential("username", "password"); // Give Credentails username and password
String URI = @"http://jcubefinance.com/customer/"; // Give http site name.

string URIPath = String.Empty;
string URIFullpath = String.Empty;

string sfile1 = @"C:\Test\Test.html";
string sfile2 = @"C:\Test\NewTest.html";
string sfile3 = @"C:\Test\";

mySSISWebClient.DownloadFile(URI, sfile1); // It download data from this http://jcubefinance.com/customer/ website and save into C:\Test\Test.html
//Sample data <a href="2012-01-01/">2012-01-01/</a>
// <a href="2012-02-01/">2012-02-01/</a>

string data = string.Empty;

StreamReader reader = new StreamReader(sfile1); // read the data from sfile1

string line;
int len = 0; ;
int len1 = 0; ;

while ((line = reader.ReadLine()) != null) // read line by line
{
if (line.StartsWith("<a href"))
{
len = line.IndexOf(">");
len = len + 1;
len1 = line.IndexOf("</a>");
data = line.Substring(len, len1 - len); // data : 2012-01-01/
URIPath = URI + data; //URIPath : http://jcubefinance.com/customer/2012-01-01/

mySSISWebClient.DownloadFile(URIPath, sfile2); // It download data from this http://jcubefinance.com/customer/2012-01-01/ website and save into C:\Test\NewTest.html
string path = string.Empty;
path = sfile3 + data;
if (!Directory.Exists(path)) // Creating Date folder in local Server according to Date if directory is not exists. path: C:\Test\2012-01-01
{
Directory.CreateDirectory(path);
}

StreamReader sreader = new StreamReader(sfile2);
string sdata = string.Empty;
string sline;
int slen = 0; ;
int slen1 = 0; ;

while ((sline = sreader.ReadLine()) != null)
{
if (sline.StartsWith("<a href"))
{
slen = sline.IndexOf(">");
slen = slen + 1;
slen1 = sline.IndexOf("</a>");
sdata = sline.Substring(slen, slen1 - slen); //sdata: customer.csv
URIFullpath = URIPath + sdata; //URIFullpath: http://jcubefinance.com/customer/2012-01-01/customer.csv

mySSISWebClient.DownloadFile(URIFullpath, path + sdata); // It download data from this http://jcubefinance.com/customer/2012-01-01/customer.csv website and save into C:\Test\2012-01-01\customer.csv

}
}
sreader.Close(); //close the sfile2

}
}
reader.Close(); // close the sfile1

Dts.TaskResult = (int)ScriptResults.Success;
}

}

}

Execute Process Task – Compress File Using 7-Zip

How to Zip multiple files using Execute Process Task and move into desired location
1) Create Foreach loop container And Execute Process Task

2)Make Collection for Each loop container

3) Variable Mappings

4) Make executable path in Execute Process Task for 7-Zip

5)Make Arguments with Expression

6)Make Variables

Deszippath : zipped files where need to store.

Sourcepath: Original path of files

filename: we are taking the file names with extension

fileShortName: we get short filename using this expression Replace(@[User::filename],".txt","")

Command for zip : a "C:\Test\test.zip" "C:\Test\01-01-2012\test.txt"

zip command In Expression : "a "+"\"" + @[User::Deszippath] + @[User::fileShortName]+ ".zip"+"\""+" "+"\"" + @[User::Sourcepath] +@[User::filename]+"\""

Unzip File
-----------
variable @[User::FullFileName] contains zipped file. example "\\job\test.txt.gn"
Arguments: "e "+ @[User::FullFileName] +" -aoa"
Executable: C:\Program Files (x86)\7zip\7zip.exe
WorkingDirectory: C:\Test\ (we load unzipped file in working Directory).

Command for unzip: e \job\test.txt.gn -aoa

How We can pass Result Set from Execute SQL Task into Script Task

How We can pass Result Set from Execute SQL Task into Script Task
1) In Execute SQL Task, create Oledb Connection ,write query in SQLStatement and select Result Set. Create variable with 'Object' data type for passing the Result Set. Add the variable for Result Set.

Create Variable with Object data type

Execute Sql task Properties

Add the object variable for result set in Execute sql task

2) Create Script Task

Pass the Object variable into Script Task

In Script we need to use following name space
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ST_30eda6583bac466382cfc9fb2148f408.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
string str1;
string str2;
OleDbDataAdapter oledb = new OleDbDataAdapter();
DataTable tb = new DataTable("Emp");
oledb.Fill(tb, Dts.Variables["Result"].Value); //filling the data into table from Result Object
foreach (DataRow rw in tb.Rows )
{
str1 = rw[0].ToString();
str2 = rw[1].ToString();
MessageBox.Show(str1 + " " + str2);
}
Dts.TaskResult = (int)ScriptResults.Success;

}
}
}

How We can Download Data From Remote Through SSIS Using SFTP

For Downloading data using SFTP we need to follow below steps.

1. We need to use a free tool called WinSCP which provides a command line utility to communicate with SFTP sites. Install WinSCP.

2. The Command line utility is located at C:\Program Files\WinSCP\WinSCP.Com.

3. Create the script (C:\Download.txt)

Download.txt
---------------
option batch on
option confirm off
open sftp://user:password@ipaddress:port --- username,password( add : in fornt of password),ipaddress( add @ in fornt of ipaddress) and port number(add : in fornt of portnumber)
cd /mydir
option transfer binary
get *.dat c:\mylocaldir --- Download the files into c:\mylocaldir folder
mv *.dat ./backup/*     --it is optional --- Move the contents on remove folder
close
exit

4. Invoke the WinSCP.com  executable using Execute Process Task in SSIS

5. In Execute process task in general
Arguments: -script=C:\Download.txt
  Executable:C:\Program Files\WinSCP\WinSCP.com.

Encryption of Files Using SSIS

Encryption is the conversion of data into a form, called a ciphertext, that cannot be easily understood by unauthorized people.
The Execute Process task provides flexibility in your package workflow, by allowing you to run a Windows application or batch script.

We need to encrypt all text files that are contained in folder "C:\job"

Step1: Check whether GnuPG is installed or not. If GnuPG is not installed , install the GnuPG.
GnuPG commands are using to encrypt the files.

Step2: Add ForEachLoop Container(Use to iterate files in the folder) and Execute process task

Step3 : Create SSIS variable FileName (string type).

Step4: Create For each loop container.
           Select enumerator type: "For each File Enumerator".
   In Enumerator Configuration
   - Give the folder name in place of Folder or browse the location of files.
               -Give *.txt in place of Files. Retrieve file names as Fully qualified.

Step5: Click on Variable Mappings
Select variables to map to the collection value.
Select variable User::FileName from variable dropbox.

Step6: Add Execute process task.
Click on Process
Select Executable property and add the path of exe. C:\Program Files\GNU\GnuPG\gpg2.exe

Select Argument property and add encrypting command.
- Goto Expressions. Click on expressions button. Select Arguments from property Expressions editor. Add the value in expression window. "--recipient jobpynadath --encrypt "+@[User::FileName]

Step7 : Execute the package

Output: C:\job\test.txt.gpg

Excel Values Returned as NULL when the underlying Value Is Not NULL using Excel as Source in SSIS

Excel Values Returned as NULL when the underlying Value Is Not NULL using Excel as Source in SSIS

When connecting to an Excel Source using SSIS, some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.

This problem is caused by a limitation of the Excel ISAM (Indexed Sequential Access Method) driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling.

Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

Example

1. Our Original Excel data

2. Data became NULL when we used original Excel source in SSIS

Now Excel Connection Manager ->Connection String property is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Job pynadath\Desktop\CWP.xls;Extended Properties="Excel 8.0;HDR=YES";

How To Solve This

1. Select the properties of Excel Connection manager of Excel Source

2. Add IMEX=1 to the value of Extended Properties in the connection string

3. Save

Note: If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
Select the properties of Excel Connection manager of Excel Source
Then We need to Following
1) On the Start Menu
2) Type regedit Click Ok
3) In the Registry Editor , Expand the following Key depending on the version of Excel that you are running. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Right Click on "TypeGuessRows" and Select Modify and change the value of HExadecimal to 0 Or change the value of Decimal to 1.

4) Click Ok.