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.