Thursday, February 18, 2016

How to read data from multiple Excel files with SQL Server Integration Services

I have data in multiple Excel files and all my Excel files are placed in the same folder. I want to create a SQL Server Integration Services (SSIS) Package which can read data from multiple Excel files and load the data into a SQL Server destination table. How can I achieve this using SSIS?
This tip explains how we can read data from multiple Excel files using SSIS and load the data into a SQL Server destination table. Please follow all the steps below to understand the solution.
Click here to read the article.

Wednesday, February 3, 2016

How to read data from multiple Excel worksheets with SQL Server Integration Services

My Excel source file has data in multiple worksheets and I need to read data from all of these sheets and load it into SQL Server destination table, how can I do this using SSIS?This tip explains how we can load data from multiple Excel sheets and load it into a SQL Server destination table.

Data Source

I have created a sample Excel source file and named it Excel_Souce.xls. This sample Excel file has three sheets and all three sheets contain data. The first row of each Excel sheet contains the column names and the data starts in the second row. My sample data looks like the below image.
Click here to read complete article

Table Variable in SQL Server

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table.

In SQL Server we have a Data Type Table. We can make use of this data type to create temporary tables in database. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table. We don’t use CREATE command to Create Temporary Table Variable, we use DECLARE keyword to create Temporary Table Variable. As I have already mentioned that Table is a data type in SQL Server that is why we use declare keyword just like we use DECLARE keyword for any data type. Functions and variables can be declared to be of type Table.

A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements.

However, table variable cannot be used in the following statement:

SELECT select_list INTO table_variable;

Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

Here is the syntax for temporary table variable.

DECLARE @TempTableVariable (Column Data_Type Width,  n)

Let’s declare a Table variable and perform Insert and Select operation on it.


USE TEACHMESQLSERVER
GO
DECLARE @EMPTABLEVARIABLE TABLE(EMP INT, EMPNAME VARCHAR(MAX))
INSERT INTO @EMPTABLEVARIABLE VALUES(1,'SUMIT')
SELECT * FROM @EMPTABLEVARIABLE
GO

We can’t run INSERT and SELECT commands separately, we have to include DECLARE Table variable command otherwise it will give below error.

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@EMPTABLEVARIABLE".

If you have less than 100 rows generally use a table variable.  Otherwise use a temporary table.  This is because SQL Server won't create statistics on table variables.


Click here to know about Differences Between Temporary Tables and Table Variable. Don’t forget to share on Facebook if you like the article.

Sunday, January 17, 2016

How to read data from an Excel file starting from the nth row using SSIS

I have Excel files I need to read using SQL Server Integration Services (SSIS), but the first few rows in the file are just information about the data and these rows should be ignored. How can I read data from an Excel file starting from the nth row in SQL Server Integration Services.
The Excel source is one of the most used data sources in SQL Server Integration Services (SSIS). In this tip I will demonstrate how can we read Excel data starting from any row. Consider a scenario like the Excel file below screen where the data starts on row number 7
Click here to read complete article.

Sunday, January 10, 2016

MSSQLTips.com Rookie of 2015 Award

Thank you world for your vote. I won MSSQLTips.com Rookie of 2015 Award just because of you!

2015 was a great year for the MSSQLTips.com community and dedicated team of authors who provide value to the SQL Server Community every second of the day.

Please visit here for more detail.

https://www.mssqltips.com/sqlservertip/4153/mssqltipscom-author-and-rookie-of-2015/

Thursday, January 7, 2016

SSAS Interview Questions

1.      What is Data Source?
2.      Can we create more than one Data Source?
3.      What is Impersonation information?
4.      What are options available in Impersonation information tab?
5.      What is Isolation in Data Source?
6.      What is default maximum number of connection in Data Source?
7.      What is query timeout in Data Source?
8.      What is data source reference in Data Source?
9.      What are different types of provider in Data Source?
10.  What language is used to query cube?
11.  What is Data Source View?
12.  What is named query?
13.  What is named calculations?
14.  How can we view data in DSV?
15.  How can we create relationship between two tables in DSV?
16.  Can we create relationship between two tables if data type doesn’t match?
17.  What is Dimensional modeling?
18.  What are the types of schema?
19.  What are the types of dimensions?
20.  What are the different types of dimensions you have used?
21.  What is user friendly name in DSV?
22.  How can you stop joining two tables automatically in DSV
23.  Can you create more than one DSV?
24.  What is attributes?
25.  What is hierarchy?
26.  What is attribute hierarchy?
27.  What is the maximum level of attribute hierarchy?
28.  What is user hierarchy?
29.  What are the types of hierarchy?
30.  What is attribute relationship?
31.  What is rigid relationship?
32.  What is flexible relationship?
33.  If customer user hierarchy is created with rigid relationship, will it work? How it will impact on cube performance and aggregation.
34.  What will be the impact on performance if attribute relationship are wrongly used?
35.  What is translation in Dimension?
36.  What is Error configuration in dimension table?
37.  What is processing?
38.  What are the different types of processing options?
39.  What is unknown member?
40.  What is to enable Writeback in Dimension?
41.  What is different dimension type option available in Dimension tab?
42.  What is collation designator?
43.  What is MDXmissingmember mode?
44.  Aggregation created only on key column; will it show aggregation for named column?
45.  What is proactive caching?
46.  What are different options available in proactive caching?
47.  What is different storage mode available in SSAS?
48.  What is attributehierarchyenabled property in dimension?
49.  What is default member?
50.  What is discretization method property?
51.  What is isaggregratable property?
52.  What are options available to order by?
53.  What is Parent – child in dimension?
54.  What is key named and value column in dimension?
55.  What is UDM?
56.  What is semi additive measure?
57.  How can you create cube for two fact tables?
58.  What is degenerated dimension?
59.  What is role playing dimension?
60.  What is conformed dimension?
61.  What are the steps you follow to create a cube?
62.  What is duplicate key found error? How can you resolve it?
63.  What is key attribute is missing error? How can you fix it?
64.  Do you create duplicate dimensions, if one dimension is used multiple times?
65.  What is measure group?
66.  How to create measure group?
67.  How to create new measure?
68.  What are aggregation functions available on measure?
69.  What is measure expression?
70.  What are different tabs available in cube design window?
71.  What is Dimension Usage?
72.  What are different relationship type options available in dimension usage?
73.  What is null processing? What are all option available to handle NULL?
74.  What is the purpose of calculation tab in cube design tab?
75.  What is calculated member?
76.  What is calculated set?
77.  What is cube metadata?
78.  What is KPI tab in cube design window?
79.  What are all steps required to create a KPI?
80.  What is KPI Value Expression, Goal expression, status, trend and additional properties?
81.  What are different types of actions?
82.  Can we create two partitions on same measure group?
83.  What are all steps you follow to create a partition?
84.  What are the best practices you must follow to create a partition?
85.  How would you know if partition is created for limited row or complete data?
86.  What is writeback property in partition?
87.  In what case we can’t use writeback property?
88.  What are different partition process options?
89.  How processing works?
90.  What id dimension key log file?
91.  What is process affected objects?
92.  What is aggregation tab in cube design?
93.  What are two types of aggregation design?
94.  What are steps to create or redesign aggregation?
95.  What is perspective?
96.  What is translation?
97.  How currency conversion works?
98.  What would be your currency dimension structure?
99.  Can we access cube while processing?
100.                      How did you deploy cube from Dev to production?
101.                      What are different types of deployment methods?
102.                      What are different types of security roles in cube?
103.                      How will you process your cube for incremental load?
104.                      What is semi additive measure?
105.                      How will you know who is accessing your cube?
106.                      What is SSAS architecture?
107.                      What is difference between SSAS 2008 R2 and 2012?
108.                      What are the best practices you will follow to create a cube?
109.                      What is cell level, dimension level, member level security?
110.                      How would you create a log file for SSAS?
111.                      How does locking works in SSAS?
112.                      What are the best practices to create dimension in SSAS?
113.                      What is meant by incremental processing?
114.                      Why do we use incremental processing?
115.                      How does processing works?
116.                      How does processing works on dimensions?
117.                      Why do we do cube partition?
118.                      How will you handle insert update in cube?
119.                      What is mining structure?
120.                      What is authentication mode for SSAS?
121.                      I have processed the cube but there is no data, how would you troubleshoot?