Ajan's BI Blog

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 possi

  You might have an SQL Server database with lot of unnecessary tables. But you are unable to remove them because you don't know if it is still being used or not. Sometimes they have been created as a backup and not deleted so now it's there as a redundant and taking up your space. In order to identify such tables the below query comes in handy. You can check the Last Interaction date and the table size to determine what to do. As a best practice it is better to ha ve a last updated date as a column in tables to identify the current relevant tables. S ELECT   last_user_scan LastInteraction,   t.NAME AS TableName,   s.Name AS SchemaName,   p.rows AS RowsCount,   SUM(a.total_pages) * 8 AS TotalSpaceKB,   CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS TotalSpaceMB,   SUM(a.used_pages) * 8 AS UsedSpaceKB,   CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS UsedSpaceMB,   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,