Skip to main content

Posts

SSIS to load excel table from any place in a sheet.

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
Post a Comment
Read more
Recent posts

Identifying unused space consuming tables in SQL Server

  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,
Post a Comment
Read more

Handling an over sized db to save space(SQL Server)

Sometimes having too many unwanted backup and temporary tables will increase the space by huge volumes so it is a best practice to often clean them and keep it tidy. This will improve the performance as well as reduce memory issues. The below stack question contains a query which can be used to identify the space consuming tables in SQL Server. I have attached the question and the query to be executed. https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database/2094454 SELECT      t.name AS TableName,     i.name as indexName,     sum(p.rows) as RowCounts,     sum(a.total_pages) as TotalPages,      sum(a.used_pages) as UsedPages,      sum(a.data_pages) as DataPages,     (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,      (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,      (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM      sys.tables t INNER JOIN           sys.indexes i ON t.object_id = i.object_id INN
Post a Comment
Read more

Removing all types of blanks of a value in TSQL

Blanks are hectic when it comes to joins, you will see absolutely no different between two values but they won't map with each other. This is a common scenario when you have data loaded from flat files and specially Excel. My below replace statement will work fine and remove all sort of blanks and leave you with pure value for a hassle free join. LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([ Value to be cleaned ] , CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), CHAR(160 ), '')))
1 comment
Read more

Type guessing cancellation in SSIS Excel Connection Manager.

One of the widely noticed issue in SSIS Excel connection manager is type guessing. For example, if there is a column with both numbers and characters and characters are coming after 10 rows of numbers. SSIS Excel connection manager predicts the column as of type numeric and outputs the character values as NULL. In order to handle this, we can edit the connection string and include these additional highlighted phrases to disable type guessing and set the type as TEXT so that all the values get captured properly Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Excel Sources\Distributors\Customer.xlsx;Extended Properties="Excel 12.0 XML; IMEX=1 ;HDR=YES; TYPEGUESSROWS=0;IMPORTMIXEDTYPES=TEXT\ "; The connection string for an excel source is found by right-clicking the excel connection manager and going to properties.
Post a Comment
Read more

Temporal tables of SQL Server 2016 not temporary

Temporal table is an add on to SQL Server 2016 which allows history maintaining. In simple terms it allows us to have a system versioning. For an example if we have a person's salary as 1000 usd as per today and tomorrow if it is updated as 1005 usd in such scenario the temporal table maintains two tables namely the generic one and a history table where the present record will be stored in the normal table and the previous one will be stored in the history table. Let’s dig in deeper that with an example, First off I will create a employee temporal table with system versioning enabled. In this case even though we are creating a single table it creates two identical tables with one having a history part concatenated to it's name. ***Note : For easier identification the queries are given in italic. CREATE TABLE [master] . dbo . Employee    (       [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED      , [Name] nvarchar ( 100 ) NOT NULL     , [Positio
Post a Comment
Read more

Querying JSON data from SQL Server 2016

                                                                                                  Sources are heterogeneous and SQL Server allows various types of manipulations and one of the interesting feature in SQL Server 2016 is Querying data of JSON Format.  There are four basic syntax which we can use against a JSON data type. We can demonstrate the use of these syntax with a simple scenario, first off I will define a simple JSON array and walk through each syntax one by one. Use of ISJSON()  ISJSON() syntax is much similar to the ISNUMERIC()  where the latter one checks for a numeric data type and outputs one if it’s true like wise ISJSON() out puts one if the evaluated value is in valid JSON Format. Try This DEMO DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N'{       "info":{           "type":1,         "address":{             "town":"Colombo",            "country":"Sri
1 comment
Read more