SSIS - [ Read Excel Cell Value In SSIS ( Execute SQL Task) ]

Scenario : 

We have an excel file with Sheet1. We need to get the CreatedBy value that exists in C3 Cell. After reading this information we can use against our validation table to find out if it matches with our requirement so we can proceed with loading Or we can read this information and insert into our Audit table for our record. In short we will be learning how to read a Cell value from Excel in SSIS.

Our input file look like this

If you are interested to use Script Task, Check this post.

Solution : 

We will be using Execute SQL Task in SSIS Package to perform this and Script task to display the value.

Step 1: 

Lets create a variable with name VarCreatedBy as shown below

Step 2:

Place Execute SQL Task on Control Flow Pane and Configure as shown below. Double Click on Execute SQL Task and then Choose Excel in ConnectionType and Click next to Connection that will lead to Source File.

Step 3:

Write Query as shown below and map the Result Set to VarCreatedBy. As you can see in query even I am reading C3 , I did not use C3:C3. we have to provide the range C3:C4 ( means read C3 cell value).

Map the variable

Step 4: 

Lets check the value of VarCreatedBy variable by using Script task

Write the highlighted code as shown below in Script task to display the value at run time.

Final Output :

Lets run the Package and see the final output. As we can see it read the Cell value (C3) and displayed

Struggling with Excel Source and Destinations, Specially Dynamic Columns etc. Check out our posts/videos on Dynamic Excel Source and Destination with real time scenarios

1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package
27. How to read Cell Value from Excel by using Script Task in SSIS Package