A lot of organization and individuals store data into Ms-Excel. This is because Ms-Excel is popular and easy to use data storage tool and it provides a lot of functions and formulas to play with the data. Reading data from Ms-Excel using SSIS is something SSIS developers come across many times. In this post, we will learn to read and export data from Ms-Excel using SSIS, the specific thing is we will read data that starts from nth row and goes till nth row into Excel.
When it comes to read data from a specific cell or specific row we often go ahead with OpenRowset attribute that comes with Excel Source into SSIS. In this post we will examine an alternative way to achieve this.
Consider this scenario we have following data into Ms-Excel and we want to read data that starts from 2nd row and goes till 5th row. What it means is we want to read data of productname column that has values Desktop, Coffee, Tea and Mobile only.
Solution approach:
I have created two SQL table to store the data.
Staging Table:
Product Table:
In SSIS, I have added one Execute SQL task and two Data Flow task.
Execute SQL Task truncates all data from Staging table. Basically it cleans all the data from Staging table and due to Truncate statement it reset the identity seed value to (1, 1).
On first Data Flow task, I have added following components.
Excel Source component read the entire data from Sheet1.
The Data Conversion component converts the Excel Colum data into varchar and eight byte sign int. This is to avoid Unicode truncation issue.
On OLE DB Destination task, we are dumping all data into Staging table.
On second Data Flow task, I have added following two components – OLE DB Source and OLE DB Destination.
On second data flow task I have create two variables startRow and endRow. I have assigned the value 2 and 5 as the requirement is to read data that starts from 2nd row and goes till 5th row.
On OLE DB Source, I have uses SQL Command as Data access mode. The SQL command we are using is following:
SELECT TOP 1000 [Sno] ,[productName] ,[quantity] FROM [SinghVikash].[dbo].[tmp_tblProducts] WHERE Sno>=? and Sno<=?
I have added two place holders for which we need to supply two parameters values.
I have assigned user defined variables as parameter values.
On OLEDB Destination component, I have selected the product table as destination table.
After setting up the SSIS package, I run the package and it executed successfully.
I checked the data into SQL Table and as expected, the data of 2nd row till 5th row was inserted in the table.
Related Articles:
SSIS: How to read nth Row from Flat File?
SSIS: Read and Export Excel data from nth Row
When it comes to read data from a specific cell or specific row we often go ahead with OpenRowset attribute that comes with Excel Source into SSIS. In this post we will examine an alternative way to achieve this.
Consider this scenario we have following data into Ms-Excel and we want to read data that starts from 2nd row and goes till 5th row. What it means is we want to read data of productname column that has values Desktop, Coffee, Tea and Mobile only.
- SSIS: How to load multiple excel files into multiple SQL tables?
- SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to generate HyperLink in Excel output?
Solution approach:
| • | The approach we will take is to dump all the data of Ms-Excel into a Staging table (temporary table). | ||
| • | Add an identity column in the staging table to store the row numbers with each row. | ||
| • | Read data that starts from 2nd row (identity column value is >=2) and ends till 5th row (identity column value is <=5). |
I have created two SQL table to store the data.
Staging Table:
CREATE TABLE [dbo].[tmp_tblProducts]( [Sno] [int] IDENTITY(1,1) NOT NULL, [productName] [varchar](100) NULL, [quantity] [int] NULL ) ON [PRIMARY]
Product Table:
CREATE TABLE [dbo].[tblProducts]( [productName] [varchar](100) NULL, [quantity] [int] NULL ) ON [PRIMARY]
In SSIS, I have added one Execute SQL task and two Data Flow task.
Execute SQL Task truncates all data from Staging table. Basically it cleans all the data from Staging table and due to Truncate statement it reset the identity seed value to (1, 1).
On first Data Flow task, I have added following components.
Excel Source component read the entire data from Sheet1.
The Data Conversion component converts the Excel Colum data into varchar and eight byte sign int. This is to avoid Unicode truncation issue.
On OLE DB Destination task, we are dumping all data into Staging table.
On second Data Flow task, I have added following two components – OLE DB Source and OLE DB Destination.
On second data flow task I have create two variables startRow and endRow. I have assigned the value 2 and 5 as the requirement is to read data that starts from 2nd row and goes till 5th row.
On OLE DB Source, I have uses SQL Command as Data access mode. The SQL command we are using is following:
SELECT TOP 1000 [Sno] ,[productName] ,[quantity] FROM [SinghVikash].[dbo].[tmp_tblProducts] WHERE Sno>=? and Sno<=?
I have added two place holders for which we need to supply two parameters values.
I have assigned user defined variables as parameter values.
On OLEDB Destination component, I have selected the product table as destination table.
After setting up the SSIS package, I run the package and it executed successfully.
I checked the data into SQL Table and as expected, the data of 2nd row till 5th row was inserted in the table.
Related Articles:
SSIS: How to read nth Row from Flat File?
SSIS: Read and Export Excel data from nth Row
