Excel source can contain a table anywhere within a sheet, but
for each table there is a starting cell and an end cell. In SSIS Excel SQL
Command we can precisely select this table by defining those two cells.
Consider the following scenario where the table within an excel sheet
starts from the cell W7 and goes till AJ38
For this purpose, we can execute the SQL command option in SSIS and
let’s discuss more on this.
First of all, in a table within an excel sheet following are the
parameters to be known
Sheet Name - Monthly SSC Report$ (Sheet name can be noticed
in the normal selection pane)
Starting Cell - W7
Ending Cell - AJ38
Using the above information, we can form the following SQL command for Excel and extract the data, once it is extracted we can define column names manually and automate it but if there is a scenario where there is more data than the range specified in future it will not be extracted so it is better to have have the ending cell as far down as possible something like AJ500.
SELECT * FROM [Monthly
SSC Report$W7:AJ38]
Comments
Post a Comment