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 
IF NOT EXISTS(  
        select 1
        from msdb.dbo.sysjobs_view job
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
        where
            activity.run_Requested_date is not null
        and activity.stop_execution_date is null
        and job.name = <SQL Job name> in single quotes
        )
BEGIN    
    EXEC msdb.dbo.sp_start_job <SQL Job name> in single quotes;
END

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.

NameSpaces
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 ?

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

Problem
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

Solution
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.