How to download a facebook video?

Friday, 27 February 2015

C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation using ADO.NET ?

This implementation is regarding how to fetch the data from available excel files using System.Data.Oledb name space. Below are two examples which contains fetching the data from .xls file as well as with .xlsx file.

Fetch the data from an excel .xls file -

Let's create an excel file with few rows and save it as Excel 97-2003 Workbook (*.xls) in your system.

For the reference both the excel files will look like with some sample records-













Fetch the records from excel file using C# .Net, Asp.Net -

Step 1 - Let's create an interface which will contain two buttons for Refresh data from .xls and Refresh data from .xlsx respectively.

Step 2 -  Create a gridview in Asp.Net which will present the data fetched from the excel.

Source code for the reference
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Button ID="btnGetData" runat="server" Text="Refresh xls" OnClick="btnGetData_Click" />
    <asp:Button ID="Button1" runat="server" Text="Refresh xlsx" OnClick="Button1_Click" />
    <br />
    <asp:GridView ID="grdExcel" runat="server">
    </asp:GridView>
</asp:Content>


Step 3 - Required name spaces -
using System.Data.OleDb; //To operate the Oledb operations
using System.Data;       //To have the data objects like data tables/sets


Step 3 - Write the logic under click event for both buttons respectively.

For .xls file type
/// <summary>
/// Click event: To fetch data from the excel file of .xls type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnGetData_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xls file type and the file path which contain the excel file
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xls;Extended Properties='Excel 8.0;HDR=Yes;'";

using (OleDbConnection connection = new OleDbConnection(con))
   {
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
      {
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
      }
   }
}


For .xlsx file type
/// <summary>
/// Click event: To fetch data from the excel file of .xlsx type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xlsx file type and the file path which contain the excel file
string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xlsx;Extended Properties=Excel 12.0 Xml";


using (OleDbConnection connection = new OleDbConnection(con))
   {
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
      {
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
      }
   }
}

Difference in logic - In both the logic only difference is in the connection strings which is highlighted in the above explanation else everything remains same.

At the Interface - Once all the source code writing and logic building have been completed then run the application. and click on the respective buttons. The user screen or web site should be look like the below screen shot -



















I hope the above explanation make you comfortable with the data handling on excel file types .xls and .xlsx respectively.

Please share your comments for any further information/help.

Wednesday, 25 February 2015

Can we open a SSIS package 2012 solution in BIDS 2008 or 2008 R2 ?

I got a scenario in which few SSIS packages have been created in BIDS 2012. Now I need to run those SSIS packages in BIDS 2008 R2 from BIDS 2012. I tried hard with the Google but I did not see any positive response. Can anyone please provide the help.



Tuesday, 6 January 2015

Access to the remote server is denied because no login -mapping exists.

I had a scenario in which there was a configuration error in a Linked Server. There was an error populating when tried to access few SSRS reports.

Error was as per the below screen shot -


The above error was coming due to the access/mapping problem of the system/administrator account with Linked server, To resolve this problem, it is required to map the system/administrator account with the particular linked server.

Right click to the linked server and select Properties :


Under security tab, change the radio button selection from Be made without using a security context to Be made using this security context, and enter your system/administrator account and password.


After successful configuration, click on OK button. it will resolve the problem.

Please mark as comment if it solved your problem.

Thursday, 8 May 2014

Create Linked Servers (SQL Server)

Scenario:
In an application there is a 'SSRS' report which is fetching data from 'SSAS Cube'. The cube refresh is happened every morning in the week. Once cube is refreshed then latest data is available to 'SSRS' reports. For user interface, this 'SSRS' report is integrated with .Net page, contains few filters.

Issue
Filters data should dynamic and available data range should based on data into 'SSRS' report. Some times it is observed that filters data is more than available data into 'SSRS' reports.

Example
Filter's data is available till December 2013 while in 'SSRS' data is present only till November. As filters data is dynamic so it is expected that filters data and 'SSRS' data range should be same.

Reason
It is happening because filters data is coming from database(Relational database) while 'SSRS' data is coming from 'SSAS' cube(OLAP).

Solution
To keep data same in 'filters area' and 'SSRS' report, data source should be same. Data source for 'SSRS' cannot be changed. So better if we fill filters area with cube data.

In order to implement same it is required to configure a linked sever which will help to fetch data from cube using simple T-SQL.

Configuration of Linked Server

Step 1 – Connect SQL server, using ‘Database Engine’ as server type along with proper Server name and authentication.

Step 2 – Into object explorer, navigate to linked server under ‘Server Objects’.
(Refer the below screen shot for reference)

















Step 3 – Right click on ‘Linked Servers’ - select ‘New Linked Server’. A dialog will come as below -

General Tab - Fill the required details as per below screen shots. 
(Refer the below screen shot for reference)

















Security Tab - Provide account detail for remote login “Here administrator account has been provided”.
(Refer the below screen shot for reference)


















Server Options -
(Refer the below screen shot for reference)
























Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn’t respond, once restart the SQL server.

Fetch data from SSAS Cube using T-SQL

Below is the sample query which i used to solve my problem-
declare @GeoMDX nvarchar(max) --Declare a variable to hold the SQL query
 
SET @GeoMDX = 'SELECT "[Measures].[TC_Caption]" AS TC_Caption,"[Measures].[TC_Value]" AS TC_Value,
"[Measures].[TSC_Caption]" AS TSC_Caption,  
"[Measures].[TSC_Value]" AS TSC_Value, "[Measures].[Lab_Caption]" AS Lab_Caption, "[Measures].[Lab_Value]" AS Lab_Value, 
"[Measures].[Brand_Caption]" AS Brand_Caption, "[Measures].[Brand_Value]" AS Brand_Value,
"[Measures].[Reimbursement_Caption]" As Reimbursement_Caption,"[Measures].[Reimbursement_Value]" As Reimbursement_Value SQL Query
FROM OPENQUERY(ASSERVER--Linked server object
''WITH MEMBER [Measures].[TC_Caption] AS 
 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[TC_Value] AS  
 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.UNIQUE_NAME 
MEMBER [Measures].[TSC_Caption] AS  
 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[TSC_Value] AS  
 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Lab_Caption] As 
 [Product Details].[Laboratories - Thea].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[Lab_Value] As  
 [Product Details].[Laboratories - Thea].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Brand_Caption] As 
 [Product Details].[Product Consolidated].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[Brand_Value] As 
 [Product Details].[Product Consolidated].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Reimbursement_Caption] As
Case [Product Details].[Reimbursement].CURRENTMEMBER.MEMBER_CAPTION
When ''''R'''' then ''''Reimbursed'''' when ''''NR'''' then ''''Not Reimbursed'''' else ''''Not Applicable''''end
MEMBER [Measures].[Reimbursement_Value] As
[Product Details].[Reimbursement].CURRENTMEMBER.UNIQUE_NAME
SELECT { 
 [Measures].[TC_Caption], 
 [Measures].[TC_Value], 
 [Measures].[TSC_Caption], 
 [Measures].[TSC_Value], 
 [Measures].[Lab_Caption], 
 [Measures].[Lab_Value], 
 [Measures].[Brand_Caption], 
 [Measures].[Brand_Value],
 [Measures].[Reimbursement_Caption],
 [Measures].[Reimbursement_Value] 
 } 
ON 0, 
[Product Details].[Therapeutic Class - Thea].CHILDREN* 
[Product Details].[Therapeutic Sub Class].CHILDREN* 
EXCEPT([Product Details].[Laboratories - Thea].CHILDREN, [Product Details].[Laboratories - Thea].&[])* 
EXCEPT([Product Details].[Product Consolidated].CHILDREN, [Product Details].[Product Consolidated].&[])*
[Product Details].[Reimbursement].CHILDREN
} ON 1 
FROM  
 [TheaCube] MDX Query      
'')' 
exec sp_executesql @GeoMDX  --Execute T-SQL Query

Wednesday, 30 April 2014

SSRS: line chart is not displaying during integration in live server

Scenario: I created one SSRS (SQL Server Reporting Services) report which was containing few line charts. During quality check of these reports, it was observed that some graphs were not displaying which were containing less or no data.
(Refer the below screen shot: SSRS Chart)

We did some analysis on this and observed that an unwanted style sheet has been applied into this chart, which is affecting visibility of the graph.

This style sheet was containing the following properties -
(Refer the below screen shot)


We tried to override these CSS properties with custom style sheet but unfortunately it couldn't get succeed.

Then i tried to change height and width for chart components(Horizontal axis ) and found an easy solution.

Easy solution:
If font size would be increased in chart horizontal axis then charts were coming fine. So i changed in all the charts and it solved my problem.
(Refer the below screen shot)

Change in font size -
(Refer the below screen shot)



After changing the font size -
(Refer the below screen shot)

And also when checked the style now from developer tool- 
(Refer the below screen shot)

Please share your thoughts if you experienced the same kind of problem and found better solution. Your knowledge sharing will be appreciated.

Other links
SSRS: Use single transaction when processing the queries

SSRS: Use single transaction when processing the queries

Question 1: In SSRS report, What is the functionality behind the check box displaying 
"Use single transaction when processing the queries" in data source properties?
(Follow the below screen shot for your reference)



















Answer: This option will be useful if any report is using more than one data set under common datasource. By checking this option we can reduce the network traffic. If this option is checked then all datasets under a report will use same open connection to hit the database. 

Example - If any report is using more than one datasets under a common data source along with checked box "Use single transaction when processing the queries".
So only one connection will maintain for all data set.

In the other hand if same option is unchecked then all data sets will open separate connections to hit the database.

 Please share your comments if this post is useful.


Monday, 21 April 2014

Difference between DTS and SQL Server Integration Services (SSIS)

1. DTS stands for 'Data Transformation Services', while SSIS stands for 'Sql Server Integration Services'.

2. DTS is a set of objects using an ETS tool(tool to extracts, transforms, and loads information into a database for warehousing)in order to extract, transform, and load this information to and/or from a database, while SSIS is an ETL tool provided by Microsoft to extra data from different data sources.

3. DTS was originally part of the Microsoft SQL Server 2000, while SSIS is a component of the Microsoft SQL Server 2005.

4. In order solve complex calculations, DTS uses Activex Script which is similar to vb script, while SSIS uses programming languages like as c# or VB.NET.

5. There is no deployment wizard available in DTS, while SSIS contains Deployment wizard.

6. Very limited Set of Transformation available under DTS, while in SSIS almost all possible transformations are available.


7. In DTS there is no parallel processing available(Only one task at a time), while in SSIS multiple tasks can execute parallel. 


8. DTS doesn' support BI in all aspects, while SSIS supports end to end process of BI.


9. In DTS script is Unmanaged(Not running under CLR), while  in SSIS script is managed and runs under CLR.


10. DTS can develop using Enterprise manager, while SSIS can develop using BIDS(Business Intelligence Development Studio).



11. DTS can be deploy in local server only, while SSIS   package can be deploy in all available servers(local/remote) using BIDS.

12. In DTS designer there is single pane available, while SSIS designer contains four pane - 
  • Control Flow
  • Data Flow
  • Event Handlers
  • Package Explorer
13. There is no event handler available in DTS, while in SSIS event handlers are available like as 'OnError', 'OnProgress'.

14. There is no solution explorer available in DTS, while in SSIS solution  explorer is available including packages, connectionsData Source and Data Source Views (DSV).

15. There is no variables in DTS so connections and other values are always static, while in SSIS these values can be dynamic as can be achieved using variables.