Sunday, 20 March 2016

How to Send Email Using ASP.Net and C#.Net (Gmail Service)

Tuesday, 23 February 2016

How to check if a SQL Server Agent job is running

I was getting an error that "SQLServerAgent Error: Request to run job <Job name>  from User <User name>  refused because the job already has a pending request from User <other User name>.

To avoid the above error, it is always required to check the running status of a SQL job, if it is already running or not. It also might be the scenario that it is already have a pending  request to execute by some other user. So below script will help to check the status of any SQL job.

Below T-SQL script is an If condition which is identifying the job status by the SQL job name. This If condition will be true if current job is not running.

For returning the value of the same job you need to make an inner join between msdb.dbo.sysjobs_view and msdb.dbo.sysjobactivity 
        select 1
        from msdb.dbo.sysjobs_view job
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
            activity.run_Requested_date is not null
        and activity.stop_execution_date is null
        and = <SQL Job name> in single quotes
    EXEC msdb.dbo.sp_start_job <SQL Job name> in single quotes;

I wish above script would help you to find the running status of any SQL job.

Download a data file from a Claims based authentication SharePoint 2010 or SharePoint 2013 site programmatically using C# programming language.

It was required to download few data files from a SharePoint site which is using claim based authentication. Here we can implement this feature in script task by using C# programming language. The below script task in SSIS package with C# code  is placed under a ForEach loop container. This ForEach loop container will have all the file names and pass them to script task one by one. Script will have below parameters to full fill this requirement -

1. tFileName - Name of the file which will be downloaded from SharePoint site (ADFS claim based authentication).
2. tSPSiteRoot - Folder location of SharePoint site where all files are existing.
3. tLoginId - Login id, which will be user id or user name for SharePoint authentication.
4. tLoginPwd - Password, which will be password  for SharePoint authentication.
5. tDomain - Domain name, which will be used for SharePoint authentication.

Using System
Using System.Net (for WebClient class and NetworkCredential).

If you have described all the Parameters and NameSpaces, then go through the below C# programming -

Script Task body

// TODO: Add your code here 
//Initialize or get all the parameters values//********************************************************************
//Assign the local variables from package variables
//File name in SharePoint site, it is only the file name
string tFileName = Dts.Variables["User::strFileName"].Value.ToString();
//SharePoint site root path, it is folder location where files are existing.
string tSPSiteRoot = Dts.Variables["User::str_SPSite_Root"].Value.ToString();
//Login id, it will be used to login to the above sharepoint site
string tLoginId = Dts.Variables["User::strLoginId"].Value.ToString();
//Login Password, it will be used to login to the above sharepoint site
string tLoginPwd = Dts.Variables["User::strLoginPwd"].Value.ToString();
//Domain for Login, it will be used to login to the above sharepoint site
string tDomain = Dts.Variables["User::strDomain"].Value.ToString();

//Create instance of WebClient class,  it will be used to perform further download operations in  C# code.
//Create object of web client class
WebClient tWebClient = new WebClient();

//Below header is much important to add, without it may download operation of the files wouldn't be able to complete.
//Include the appropriate header for web client object
tWebClient.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");

Create the fully qualified path of the particular file in SharePoint site by concatenating the sharepoint folder locatino and particular file name 
//Get the File URL which is available at SharePoint location
string tRemoteFileName = tSPSiteRoot + tFileName;

//Get a folder location into local system, so here file will download from SharePoint location. you can use any location in your local system hard drive.
//Get the File location which is going to store locally
string LocalFileName = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "/" + tFileName;

Now authenticate the SharePoint ADFS claim based authentication by providing appropriate user name, password and domain name like you have set through above parameters.
//Authenticate the login to SharePoint site which has the data files
tWebClient.Credentials = new NetworkCredential(tLoginId, tLoginPwd, tDomain);

//Download the file into location system folder location
//Download the files into locally, DownloadFile is a function in WebClient class. it contain two parameters which are fully qualified SharePoint file path and local file path.

tWebClient.DownloadFile(tRemoteFileName, LocalFileName);

//Just adding downloaded path of the file into SSIS variable so it would be used further in SSIS package for moving this file from local download path to appropriate location like as shared folder. //Assign the path of the locally downloaded file
Dts.Variables["User::strLocalFileName"].Value = LocalFileName;

Above peice of code will download the file into a local system folder, but due to project purpose, it may be required to move the downloaded file to any other appropriate location so at the last step of the above code locally download  path of the file has been restored into one SSIS package variable.

I wish it will help you to download any number of files from SharePoint ADFS (Active Directory Federation Services) claim based authentication.Thanks and good luck.

Tuesday, 9 February 2016

SSIS - How To Load Multiple Files ( .txt or .csv or .excel ) To a Table in SQL server ?

Data upload from multiple files formats into one destination table using SSIS.

Most of the time in SSIS, the data upload process is designed for the same type of file's format, but how to manage the data upload process if there are multiple file's formats available in a folder. In this situation we need to prepare a SSIS logic which will help to design the dynamic process to take care of different file's formats.

Let's assume a scenario in which there are multiple data files of different formats which contains excel, csv and text files. There can be multiple approaches to implement this scenario

The above scenario can be achieved by the following approaches.

Approach 1 - Keep multiple data flow tasks inside the single For each loop container.

Maintain multiple Data Flow Tasks inside For each loop container and each data flow task will handle one particular file format. 

In the above screen shot there are three data flow tasks are taken to handle with three kind of files like as excel, csv and text file. Task flow to the appropriate data flow task will be handled based on the expression written into the Precedence constraints.

Approach 2 - Maintain only one Data Flow Task and create multiple data sources based on the available file types. 
Example - There will be three type of data sources for excel, csv and text files.

Approach 3 - There will be a separate For Each Loop container for each format and will be a parallel execution on For each loops. 
Example - all the excel files will be processed through a separate for each loop, in the same manner all the csv files will be processed through a separate for each loop and all the text files will be processed through a separate for each loop. 

Friday, 26 June 2015

How to create a new SSIS Package using SQL Server Business Intelligence?

In this section we will talk about creating a new SSIS package from the begining. We will also look into some important SSIS package properties.


Step 1 - Launch SQL Server Business Intelligence Studio from the start program.

Step 2 - Go to File --> New and click on Project...

Step 3 - Select Integration Services Project from the New Project wizard. Provide a proper name ie. MyFirstSSISSolution and click on OK button.

Step 4 - It will create a new SSIS solution with a default package called Package.dtsx.

Now your SSIS Package is ready for the design. From the tool box, You can drag and drop the required component  into the control flow tab as well as in to the data flow tab.

SSIS package contains few tabs like 
a. Control flow - It defines the work flow between tasks.

b. Data flow - It defines the flow of data from source to destination.

c. Event Handlers - It helps to implement the custom logging if required.

d. Package Explorer - It helps to check on hierarchical view of configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables.

Adding a new SSIS Package into the existing solution
You can create a new SSIS package into the existing solution. To do that -
Right click on the SSIS Package folder and select New SSIS Package. It will create a new SSIS Package.

Renaming the existing SSIS Package
You can rename an existing SSIS package by right click on it and select Rename 

Important Points 
Other than the above explanation, SQL Server Integration Services (SSIS) has other wonderful features -

Transaction Option - It provides the Transaction features into the SSIS Package like as database transactions. The default value for the TransactionOption is Supported. Here the SSIS package will execute within the existing transaction(i.e. called from the other package) and will join the existing default transaction.
  Two other modes are available as Required and NotSupported.  TransactionOption Required means that the package will always execute within the transaction, either joining any existing transaction or creating a new one.  TransactionOption NotSupported means the package will never bother with the transaction feature. 

Isolation Level - IsolationLevel property in SSIS works like the Transaction Isolation Level in database. Default value is Serializable which may not be required always. In Serializable Isolation level, read locks are held until a transaction will not be committed or rolls back and also No data can be inserted due to additional lock. Purpose of the Serializble Isolation level is to decreased the data concurrency but also keep in mind that it may not be required always.

Protection LevelThe protection level in SSIS provides various options to apply encryption on sensitive information like database connection string. Default value is EncryptSensitiveWithUserKey which may not requrie always. Data base connection string with the default protection level will encrypt with the user key of person who created the package. When other user executes the same package it will fail due to decryption of the connection string. To avoid this consequence , use the protection level as DontSaveSensitive which means you are not keeping any sensitive information in your package. The main benefit to do this change is that the other user can also execute the package.

I hope the above article will help you to create a new SSIS package with some additional information.

Provide your suggestions through the comment box.

Saturday, 13 June 2015

Implementing Registry entry configuration in SQL Server Integration Services

Scenario - Registry entry configuration is used very rarely. In my experience I never got the situation in which it is compulsory to use Registry entry configuration. I also did not see any particular advantage to have the Registry entry configuration in SQL Server Integration Services. According to few people Registry entry configuration may useful to create the system dependency in which it can store any code value into Value data in Registry key and later be validated for running the SSIS Package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Registry entry configuration. To verify this configuration let's create a key in the windows registry and fetch it from the SSIS package using a script task.

Step 1 - Press    + R and type Regedit then press Enter, It will open the Windows Registry 


Step 2 - Expand the HKEY_CURRENT_USER then right click Go to New then choose Key in order to create a new key. We could also use any existing key but for clear understanding let's create a new one.

Step 3 - Let's name of the key SSIS_Reg. Right click on it and choose New then choose String Value. Provide name Value as name for the String Value

Step 4  Right click on Value and select Modify... then we will provide some value into it. 

Step 5 - Let's provide the value as 'This is registry entry configuration in SSIS' in the Value data then click on OK button. 

Step 6 - Registry entry value has been created till the Step 6. Now from then Step 7 on wards we will fetch this Registry entry value from a SSIS package. Now  open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 7 - Open the default package and create a Variable of string type. let's say Var_Regedit.

Step 8 - Now we need to integrate this variable with the Windows Registry key (SSIS_Reg) which is created at Step 3

Step 9 - Open the package and select Package configurations by right clicking on the package.
Check on Enable package configuration and click on Add button -

Step 10 - Select Registry entry from the Configuration type dropdown list and specify the Window Registry Key name "SSIS_Reg" which we have already declared at the Step -3 then click on Next button.

Step 11 - Now it's time to integrate the Registry entry key with the package variable Var_Regedit which is created at Step 7. To do that select it's Value  and click on Next button.

Provide the Configuration name and click on Finish button,

Step 12 - Let's drag and drop a Script task from the toolbox. Provide Var_Regedit as ReadOnlyVariables then click on Edit Script button.

Step 13 - Provide the below lines of code into script editor -
public void Main()
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;

Close the script task editor and click on OK button into the script task.

Step 14 -  Now we are done with all the changes. Let's run the package, It should populate the message box containing the value of Windows Registry Entry which we have created at Step 5.

As per all the above configuration, Message box should contain the value as per below screen shot.

So in this way we have completed successfully the Registry entry configuration in SQL Server Integration Services.

Important points -

1. Registry entry configuration value can be stored either in an existing registry key or in the new registry key created.

2. This configuration type in SSIS used very rarely.

3. It should not use to store the database name or any user credentials if there is any security concern.

4. At the time of deployment same registry entry key must be available with the appropriate value data.

5. Only a single value can be stored in one registry key.

Please provide your comments if the above post is useful for you,

Thursday, 11 June 2015

Implementing Parent package configuration in SQL Server Integration Services

Scenario - Parent package configuration is very useful when it is required to pass the value from the parent package to it's child package. The main advantage to using this configuration is to avoid the calculation of the variable again and again in every package, we can get the value of such variable from the parent package. For example the main database connection string, Any global variable or Database name and so on.

Usage of SQL Server configuration
Parent package configuration is more useful whenever it is required to access any variable from the child package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Parent package configuration. To verify this configuration let's pass the value of a variable from the Parent to it's Child package.

Step 1 - Open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 2 - Let's create two packages,  parent package as well as child package -

Step 3 - Open the Parent package and create a variable called Var_Global_DB and assign the value 'Global Database Name'. Later we will fetch this variable from the child package -

Step 4 - Drag an Execute Package Task from the tool box and double click on it. A wizard for Execute Package Task Editor will appear.

Step 5 - Select the Package tab from the left side bar and choose File system as Location in the right side bar.

Step 6 - Create a new connection for File system location by selecting the New connection from the connection drop down list.

Step 7 - Provide the path of the child package with the help of browse button and Click twice on OK  button -

Now the parent package can call the Child package through the Execute Package Task

Step 8 - In the child package create a variable called Var_Child. It will hold the value of the parent package variable.

Step 9 - Open the Child package and select Package configuration by right clicking on the package.
Check on Enable package configuration and click on Add button -

Step 10 - Select Parent package variable from the Configuration type dropdown list and specify the variable name which we have already declared at the Step -3 then click on Next button.

Step 11 - Expand the variable properties from Var_Child and select it's Value then click on Next button.

Step 12 -  Provide the Configuration name and click on Finish button.

Also close the main Package configuration wizard. Parent package configuration has been completed here.

Step 13 - To Verify the Parent package configuration, let's check the value of the variable Var_Child in the child package which would be holding the value from the parent package variable using Parent package configuration.

Step 14 - Drag and drop a script task from the toolbox into child package and double click on it. Pass Var_Child as  ReadOnlyVariables then click on Edit Script button.

Step 15 - Write the below line of code into the script task -
public void Main()
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;            MessageBox.Show(Dts.Variables["User::Var_Child"].Value.ToString());

Close the script task editor and click on OK button into the script task.

Step 16 -  Now we are done with all the changes. Let's run the parent package, It should populate the message box from the child package with the variables's value which has initialized in the parent package -

We are done here with the parent package configuration. I believe this article will help you to understand on Parent package configuration is SQL Server Integration Services.

Please provide your comments if this post is useful for you,