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.

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.

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.

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/

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?

Nominated for MSSQLTips.com Rookie of the Year Award – Please Vote!

As Most of you are aware that I have been writing at MSSQLTIPS.COM. It’s a wonderful community with highly experienced authors worldwide and it is a pleasure for me to be part of this great community.

This year MSSQLTIPS has come up again with two awards – MSSQLTips.com Author of the Year and MSSQLTips.com Rookie of the Year.

I have been nominated for the MSSQLTips.com Rookie of the Year award. I urge each one of you to follow the voting link and vote for  me (Ghanesh Prasad).

Voting Link: https://www.mssqltips.com/mssqltips-author-voting.asp

Voting Directions: Select the Author of the Year in the left column and the Rookie of the Year in the right column (Ghanesh Prasad) then press the "Submit" button at the bottom of the page to cast your vote. NOTE - One vote per day per IP address.

Interactive Sorting for a SQL Server Reporting Services Report

You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.

Check out complete article here.

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

You created a report in SQL Server Reporting Services that has four parameters Year, Month and two Calendar Date Picker parameters StartDate and EndDate. The default values for StartDate and EndDate are determined by theYear and Month parameters. So when a new Month or Year is selected the value for StartDate should be the first day of the Month and the EndDate should be the last day of the Month. When you set default values for StartDateand EndDate the parameter values are correct the first time, but if you change the Month or Year the values forStartDate and EndDate don't change. In this tip we show how to solve this problem.

Check out complete article here.https://www.mssqltips.com/sqlservertip/3560/dynamically-refresh-sql-server-reporting-services-report-date-parameters/

SSIS Toolbox is not visible in SQL Server Data Tools

The SSIS Toolbox is very important in SQL Server Integration Services to allow you to add components to the SSIS package. If you close the SSIS Toolbox sometimes making it display again doesn't work. In this tip we look at what is happening and how to resolve this issue.

check out complete article here.

SQL Server Encrypt Column data using Symmetric Key

USE TEACHMESQLSERVER
GO

-- STEP 1 CREATING A DUMMY TABLE

CREATE TABLE EMP(EMPID VARCHAR(12))
GO

-- STEP 2 INSERTING DUMMY ROW

INSERT INTO EMP VALUES('111-111-111')
GO

-- STEP 3 CREATING MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='ABCD1234'
GO

-- STEP 4 CREATING CERTIFICATE

CREATE CERTIFICATE CERTIFICATE1 WITH SUBJECT ='PROTECT DATA'
GO

-- STEP 5 CREATING SYMMETRIC KEY

CREATE SYMMETRIC KEY SYMMETRICKEY WITH ALGORITHM =AES_128
ENCRYPTION BY CERTIFICATE CERTIFICATE1
GO

-- STEP 6 CREATING A NEW COLUMN WITH DATA TYPE VARBINARY

ALTER TABLE EMP ADD ENCRYPTEDEMPID VARBINARY(MAX)
GO

-- STEP 7 ENCRYPT THE COLUMN DATA USING UPDATE COMMAND

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
GO
UPDATE EMP SET ENCRYPTEDEMPID = ENCRYPTBYKEY(kEY_GUID('SYMMETRICKEY'),EMPID) FROM EMP
GO
SELECT * FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY


-- STEP 8 DECRYPT AND READ THE COLUMN DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
SELECT CAST(DECRYPTBYKEY(ENCRYPTEDEMPID) AS VARCHAR(MAX)) AS DECRYPTED FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY

-- STEP 9 INSERT THE ENCRYPTED  DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
INSERT INTO EMP(ENCRYPTEDEMPID) VALUES(ENCRYPTBYKEY(KEY_GUID('SYMMETRICKEY'),CAST('121-345-765' AS varchar)))
CLOSE SYMMETRIC KEY SYMMETRICKEY
SELECT * FROM EMP
GO

Get detailed explanation here.

SSRS Report Subscription Important Points to remember.

There are two option to create report subscription in Report Manager.

1- New Subscription
2- Data Driven Subscription


New Subscription 

-Report Subscription will not work with Window Authentication.
-Report Subscription will work with Store Database Credentials.
-SQL Server Agent must be running to create Report Subscription.
-Once Report Subscription is created, a SQL Server Agent will be created to run the report on scheduled time.
-Path to share report will be \\MachineName\Users\UserName\Documents
-Once subscription is created, you can find it in MY Subscription list in Report Manager.
-You can create Subscription Schedule for Once or Monthly, Weekly, Day, Hourly basis.
-If subscription is deleted from Report Manager, SQL Server Agent job to run the report will also be deleted.
-If SMTP connection connection is not created, then you will not get option to send report through email.
-If report has parameter and parameter default value is not set then you can't create subscription. You have to define it at the subscription creation time.


Data Driven Subscription

-Report can be shared via email and windows file sahre.
-Null Delivery Provider, will be used for report caching. In this mode, report will not be rendered but will be stored in the report server temporary database.
-Steps to create Data Driven Subscription
Step 1 - Specify how recipients are notified:, Specify a data source that contains recipient information:
Step 2 - Create connection to source which holds data driven subscription information.
Step 3 - Write SQL query to return FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 4 - Specify delivery extension settings for Report Server FileShare, assign values for FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 5 - Specify Report Parameters default value
Step 6 - Specify when subscription will be processed

OLTP VS OLAP Systems

OLTP stands for Online Transaction Processing, OLTP System deals with operational data. Operational Data is business transaction or activities which happen every day in the business i.e. In a banking system, you withdraw amount from your bank account, then withdrawal amount, available balance, account number, transaction number etc is operational data. In OLTP system, data is frequently inserted, updated and queried. OLTP system is most optimized to perform DML operation.

OLAP stands for Online Analytical Processing, OLTP System deals with historical data or archival data. OLAP systems are highly optimized for read operation. OLAP systems collects data over a period of time and store it in a very large database called Data Warehouse. This historical data is used for analysis purpose and to understand the trend of important KPI i.e. if we collect last 5 years of flight reservation data, then data can give us much meaningful information such as trends in reservation, peak reservation time, peak booking hours etc. 

Handle Excel exceeds maximum 65,536 rows in SSRS 2008R2

You have created report in SSRS2008R2 and your report contains more than 65,536 records. When you export the report into Excel you get Export Error message “Exceeding 65,536 rows limit”. How can we resolve this issue?

SQL Server Reporting Services allows you to export data into multiple formats (i.e. Excel, PDF, XML, etc.) and you can find all the supported formats here. SSRS 2008R2 supports the .xls file extension for Excel, but these Excel files support a maximum of 65,536 records per sheet. When you try to export a SSRS report which has more than 65,536 records it will fail to export the report into Excel.

In this tip I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report. To demonstrate the solution, I will use theAdventureworks2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.

Read complete article here.

Freeze Excel Column Header for SQL Server Reporting Services Report

You created a SQL Server Reporting Services report and the requirement is to output to Excel format. As you may know, SQL Server Reporting Services (SSRS) provides functionality to export a SSRS report into Excel, but for long reports the column headers disappear in Excel when you scroll through the data. In this tip we look at how to freeze the column headers in Excel when the report is created using SSRS.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report.

In this tip I will describe how to freeze the Tablix column headings in SSRS, so that when viewing the data in Excel the column names can be seen even after scrolling down. SQL Server Reporting Services doesn't provide any out of box functionality to freeze the column headings when exporting to Excel, so we will show a workaround to do this.

Click here to read complete article.

Execute a SQL Server Reporting Services report from Integration Services Package

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

This tip assumes that you have previous real world work experience building a simple SSRS Report and SSIS package. I will use AdventureworksDW2008R2 sample database and SQL Server 2012 to demonstrate the problem and solution.

I have divided this tip in two parts.

Part 1: I will create a sample SSRS report and deploy it to the Report Server.
Part 2: I will create a SSIS Package which will execute the SSRS report created in Part1.

Read complete article here.

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?

This tip assumes that you have previous experience building a simple SSRS Report.

In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.

I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Read complete article here.

Creating a Tabbed Report in SSRS

Have you ever struggled with creating tabs for a SQL Server Reporting Services report? If yes, then you will most likely know that SQL Server Reporting Services does not provide any built in feature to accomplish this task. Finding a suitable solution can be very tricky. So how can we create a tabbed report in SSRS?

In this tip I will give a demo on how to create a tabbed report in SSRS? To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

SQL Server Reporting Services Text Box Orientation

If a SQL Server Reporting Services report has many columns with text descriptions in a horizontal direction then those headings will need significant space in the report. There are situations where report data will not fit in a single page and the users will have to scroll horizontally to see the data. Unfortunately, this is very irritating and users may ask you to modify the report in such a way that report data can fit in a single page. How can we modify the report and what approach you should follow in this case?

Proper orientation of a Textbox saves a lot of space in a report layout; if a report has 10 columns with text descriptions in a horizontal direction then it will take more space in the report as compared to the same report having those columns in a vertical direction.

Read complete article here.

Export SQL Server Reporting Services Report Data into Multiple Excel with Worksheets

Problem

Sometimes SQL Server Reporting Services users want to export SSRS reports into Excel with multiple sheets, but when the SSRS report data region (Tablix/Matrix) doesn't have any grouping then all the data will be exported into a single sheet. How can we export a SSRS report into Excel with multiple sheets?

Solution

In this tip we will first review the problem and then demonstrate the solution. To demonstrate, I will use theAdventureworksDW2008R2 sample database and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.