How to Read Multiple Sheets of an Excel File Using SSIS

[hardik]

How to Read Multiple Sheets of an Excel File Using SSIS

When working with SSIS (SQL Server Integration Services) and Excel files, there are instances where you need to read data from multiple sheets within an Excel workbook. This guide will show you how to accomplish this using the ZappySys Excel Source component, which supports the use of regular expressions to dynamically read data from multiple sheets.

Prerequisites

• SQL Server Data Tools (SSDT): Install SQL Server Data Tools, the development tool for SSIS.
• ZappySys SSIS PowerPack: Download and install the ZappySys SSIS PowerPack.
• Basic understanding of SSIS package development

Steps to Read Multiple Sheets

1. Drag and Drop the ZS Excel Source Component:

   • Open your SSIS package in Visual Studio.
   • From the SSIS Toolbox, drag and drop the ZS Excel Source component onto the Data Flow Task.

2. Configure Connection Manager:

   • Double-click on the ZS Excel Source component to open the editor.
   • Click on the New button to create a new Excel Connection Manager.
   • In the Connection Manager, specify the path to your Excel file and configure any other necessary settings (e.g., specifying that the first row has column names).

3. Configure Excel Source:

   • In the ZS Excel Source editor, select the newly created connection manager.
   • For the Data Access Mode, choose SQL Command.

4. Write the SQL Query:

   • In the SQL Command text box, write a query to select data from the sheets you are interested in.

For Example, If you’ve sheet names like this:

1. Default
2. Lex Autolease
3. Lex Autolease - Return Ve…
4. LL - Novuna Vehicle Solut…
5. LL - Tuskerdirect Ltd
6. LL - ALD Automotive
7. LL - Arval UK Ltd

Use the following query as an example:

     SELECT * FROM [(^\d+\.\s.*$)--regex]

This query uses a regular expression to select data from sheets whose names match the pattern. For instance, this pattern matches sheets that start with a number followed by a period and a space.

5. View Example Queries:

   • If you need more examples of queries or regular expressions, click on the View Examples button within the ZS Excel Source editor. This will provide you with additional sample queries that can be customized to suit your needs.
     Below is the sample screenshot:
     
     

6. Test the Configuration:

   • Click on the Preview button to test your configuration and ensure that the data from the intended sheets is being read correctly.
   • If everything looks good, click OK to close the editor.
     Below is the sample screenshot:
     
     

7. Proceed with Package Development:

   • Continue developing your SSIS package, adding any necessary transformations, destinations, and other components to process the data read from the Excel sheets.

Conclusion

Using the ZS Excel Source component in SSIS with regular expressions allows you to efficiently read data from multiple sheets in an Excel workbook. This method is especially useful when dealing with workbooks that have dynamically named sheets or a large number of sheets. By following the steps outlined in this article, you can streamline your ETL processes and make your SSIS packages more versatile and powerful.

Feel free to share your experiences or ask questions in the community forum below. Happy ETL-ing!