Wednesday, July 6, 2016

List of Columns of a Table in CSV Format

DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)

-----------------------------------------------------------------------
-- *** User Customisation

-- Set up the name of the table here :
SET @TABLE_NAME = 'Address'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'

-----------------------------------------------------------------------

DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)

IF @SCHEMA_NAME =''
  BEGIN
  PRINT 'Error : No schema defined!'
  RETURN
  END

IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
          ON T.schema_id=S.schema_id
          WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
  BEGIN
  PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
        @SCHEMA_NAME+''' does not exist in this database!'
  RETURN
  END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT   CASE WHEN PATINDEX('% %',C.name) > 0
         THEN '['+ C.name +']'
         ELSE C.name
         END
FROM     sys.columns C
JOIN     sys.tables T
ON       C.object_id  = T.object_id
JOIN     sys.schemas S
ON       S.schema_id  = T.schema_id
WHERE    T.name    = @TABLE_NAME
AND      S.name    = @SCHEMA_NAME
ORDER BY column_id


SET @vvc_ColumnList=''

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

WHILE @@FETCH_STATUS=0
  BEGIN
  SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName

  -- get the details of the next column
  FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

  -- add a comma if we are not at the end of the row
  IF @@FETCH_STATUS=0
    SET @vvc_ColumnList = @vvc_ColumnList + ','
  END

CLOSE TableCursor
DEALLOCATE TableCursor

PRINT 'Here is the comma separated list of column names :'
PRINT '--------------------------------------------------'

PRINT @vvc_ColumnList

Wednesday, March 30, 2016

Get Directory Structure using Extended Stored Procedure xp_dirtree

Exec xp_dirtree 'C:\Windows'
Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.
Here is the quick script which will insert the data into the temptable and retrieve from the same.
CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);INSERT INTO #TempTable (Subdirectory, Depth)EXEC xp_dirtree 'C:\Windows'SELECT SubdirectoryDepthFROM #TempTable;DROP TABLE #TempTable;

List Directory Files using T-SQL xp_cmdShell Stored Procedure

Here is a example code showing how to list directory files in a file folder using sql xp_cmdshell procedure.

CREATE TABLE dirList (
  id int identity(1,1),
  line nvarchar(1000)
)
GO;

INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases'

SELECT * FROM dirList;
WITH CTE AS (
  SELECT
    id,
    SUBSTRING(line,1,17) [date],
    SUBSTRING(line,18,19) sizeordir,
    SUBSTRING(line,37,100) name
  FROM dirList
  WHERE id > (
    SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
  ) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
  id,
  [date],
  isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
  isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
  name
FROM cte

The above t-sql script can be used to list files in directory using sql code.
The isDirectory column and isFile column can be used to distinguish files and folders within the target file folder.

list-directory-files-using-sql-xp_cmdshell

By using recursive sql select statements, it is possible to list all files and all folders under the root c drive.
Again recursive SQL statement will enable to get the directory structure of the target file folder or disk drive given as parameter in the t-sql xp_cmdshell statement.

Additive, Semi-Additive and Non-Additive Measures

Let us begin with the definition of each which will be followed by examples:

1. Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy

2. Semi-Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy except the time dimension

3. Non-additive measures: These are those specific class of fact measures which cannot be aggreagated across all/any dimension and their hieracchy

For example:

A) We have sales figures...one may tend to add sales across all quareters to avail the yearly sales..hence this is an example of Additive measure

B) We have stock levels say 1000(qty of Item A) on Monday...I sell 200(qty of Item A) on Tuesday I further sell 300(qty of Item A) on Wednesday...going by basic math On Thursday I should be left with 500(qty of Item A, assuming no inventory has flown in) to obtain current stock level I cannot aggregate the Stock sales across time dimension hierarchy...If done I will have inappropriate outcomes

C) Aggregation of percentage or dates is an Ideal example of non-additive measures.

Happy Learning !!

SQL Server 2008 Integration Services Tasks

SQL Server 2008 Integration Services Tasks

SQL Server Integration Services (SSIS) is a Business Intelligence tool used by database developers or administrators to perform extract transform & load (ETL) operations. SSIS has come long way since the early days of Data Transformation Services (DTS) which was initially introduced by Microsoft in SQL Server 7.0. Most database developers and administrators had their first interaction with DTS when using the Import and Export Wizard. The primary objective of DTS was to transform data from an OLEDB data sources to another destination. In SQL Server 2008 Integration Services, there are around 28 different control flow tasks and 11 Maintenance Plan Tasks. This article is the first in a series that will cover all the Control Flow Tasks and Maintenance Plan Tasks in SQL Server 2008 Integration Services. Beginning with an overview of the Control Flow Tasks and Maintenance Plans in SSIS 2008.
Overview of Control Flow Tasks in SSIS
The Control Flow Tasks within SQL Server Integration Services (SSIS) can be grouped into 9 major categories based on usage. The different categories for control flow tasks are listed below:
  • Container Tasks
  • Data Flow Tasks
  • Data Preparation Tasks
  • Workflow Tasks
  • SQL Server Tasks
  • WMI Tasks
  • Scripting Tasks
  • Backward Compatibility Tasks
  • SQL Server Analysis Services Tasks
Container Tasks: – container control flow tasks within SSIS can be used by database developers or database administrators when providing a structure to the package and to repeat control flows within the package. It can also be used to group tasks and containers within a package into a meaningful unit of work. Different types of container tasks are For Loop Container, Foreach Loop Container, Sequence Container and Task Host. The Task Host container is not visible within the control flow toolbox, it is designed to provide service to a single task. The container tasks within SSIS are shown in the image below.



While designing SSIS packages to group two or more tasks together; select the tasks , right click on them and select the Group option from the drop down. This will group the tasks together as a group as shown in the image below.



Data Flow Tasks: – Data flow tasks within SSIS can be used to move data from a source to destination and at the same time use certain transformation tasks to clean and modify the data while it is transferred. By adding a data flow task to the package control flow the package can be made to perform data extraction, transformation and load (ETL) operations. The data flow tasks available within SSIS are shown in the image below.





Data Preparation Tasks: – data preparation tasks within SSIS can be used to retrieve data or validate it to determine the quality of data. The different types of data preparation tasks available in SSIS are File System Task, FTP Task, Web Service Task, XML Task and Data Profiling Task. The list of data preparation tasks within SSIS are shown in the image below.



Workflow Tasks: – Workflow tasks within SSIS can be used to send email messages or to communicate with other processes to run other packages, run some other programs or batch files. Use Message Queue Task to send and receive messages between packages. The workflow tasks within SSIS are shown in the snippet below.



SQL Server Tasks: – SQL Server tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. Use the Bulk Insert Task to insert data from a text file or a flat file into an SQL Server database table the same way that the BULK INSERT statement or the BCP.EXE command line tool is used. The Execute SQL Task can be used to execute any TSQL code such as truncating a staging table, running queries, stored procedures etc. The Transfer Database Task can be used to transfer database objects from one database to another. The Transfer Error Message Task can be used to transfer user defined error messages from one instance of SQL Server to another. The Transfer Job Task can be used to transfer SQL Server Agent Job between instances of SQL Server. The Transfer Login Task can be used to transfer logins between instances of SQL Server. The Transfer Master Stored Procedure Task can be used to transfer user defined stored procedures between the MASTER databases on instances of SQL Server. The Transfer SQL Server Objects Task can be used to transfer tables or stored procedures between instances of SQL Server.



WMI Tasks: -  Windows Management Instrumentation (WMI) Tasks within SSIS can be used by to read WMI data and watch for WMI events. Different types of WMI tasks are WMI Data Reader Task and WMI Event Watcher Tasks. The WMI Data Reader Task can be used to run WQL queries against the windows management instrumentation to read the event logs, determine hardware information etc. The WMI Event Watcher Task empowers SSIS to wait for certain WMI events and then respond to the event. The WMI Tasks within SSIS are shown in the image below.



Scripting Tasks: – Scripting tasks within SSIS can be used to perform certain functions which are not fully supported by the existing tasks in SSIS.



Backward Compatibility Tasks: – Backward compatibility tasks within SSIS can be used to execute legacy application scripts with the SSIS packages. The different types of backward compatibility tasks are ActiveX Script Task and the Execute DTS 2000 Package Task. The ActiveX Script Tasks is deprecated and is included in SSIS 2005 and SSIS 2008 for backward compatibility with DTS 2000. The Execute DTS 2000 Package Tasks can be used to execute DTS packages (SQL Server 2000) from within an SSIS 2005 or SSIS 2008 packages.



SQL Server Analysis Services Tasks: – SQL Server Analysis Services Tasks within SSIS can be used to create, modify, delete and process Analysis Services objects. The different types of Analysis Services tasks are Analysis Services Execute DDL Task, Analysis Services Processing Task and Data Mining Query Tasks. The SQL Server Analysis Services Execute DDL Task is much similar to Execute SQL Task; however use of Analysis Services Execute DDL Task can issue Data Definition Language statements against an Analysis Services system. The DDL statements can be used to create cubes, dimensions, KPI’s or any other analytical processing OLAP objects. The Analysis Services Processing Task can be used to process analysis services objects such as cubes, dimensions and mining models. The Data Mining Query Task can be used to run prediction queries based on data mining models built in analysis services. A prediction query creates a prediction for new data by using the mining models chosen.



Overview of Maintenance Plan Tasks in SSIS
Within SQL Server 2008 Integration Services there are around 11 Maintenance Plan Tasks available. Different types of Maintenance Plan Tasks are shown in the image below.



Database Administrators can create database maintenance plans either by using Maintenance Plan Wizard or by using SSIS designer. By using the Maintenance Plan Wizard a very basic maintenance plan can be created for all the system and user databases. However, to create an enhanced workflow it is advised to create maintenance plan using SSIS designer. To learn more about creating a Maintenance Plan, refer to the earlier article titled Overview of Maintenance Plans in SQL Server 2008.

Back Up Database Task: – Back Up database task different types of database backups to be performed; such as Full, Differential or Transactional Log backups based on the Recovery Model of the System or User databases. To learn more about the different recovery models in SQL Server refer to the article titled “Database Recovery Models in SQL Server”.

Check Database Integrity Task: – Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. This task internally executes DBCC CHECKDB statement.

Execute SQL Server Agent Job Task: – Execute SQL Server Agent Job Task can be used to run SQL Server Agent Jobs that are created on the SQL Server Instance.

Execute T-SQL Statement Task: – Execute T-SQL Statement Task can be used to execute some Transact SQL queries against databases. This task is only available when creating Maintenance Plans using SSIS designer.

History Cleanup Task: -  History Cleanup Task deletes the historical data related to database backups and restore activities; SQL Server Agent Job history, database maintenance plan history etc. This task basically uses sp_delete_backuphistory system stored procedure to clean up the history prior to the number of days, weeks or months from the current system date.

Maintenance Cleanup Task: – Maintenance Cleanup Task can be used to remove the older files like maintenance plan execution reports, database backup files etc. Use this task when creating maintenance plans it will remove old files which are not required. 

Notify Operator Task: – Notify Operator Task can be used to send messages to the SQL Server Agent Operator when a task has successfully completed or failed. The operator can be notified by an email, pager or by net send method.

Reorganize Index Task: – Reorganize Index Task is used to defragment & compact clustered and non-clustered indexes on tables and views. The index reorganise is best suited when the index are not heavily fragmented. This process usees fewer system resources compared to rebuilding an index. If the indexes are heavily fragmented then it is best to rebuild indexes using the Rebuild Index Task.

Rebuild Index Task: – Rebuild Index task can be used to organise data on the data and index pages by rebuilding indexes. This helps to improve the performance of index seeks and scans. This task also optimises the distribution of data and free space on the index pages, thereby allowing faster future growth. If this task is used to rebuild indexes in a single database, it allows selection of views and tables to rebuild the index. This task also has options such as “Sort results in tempdb” and “Keep index online while reindexing”. However these operations require sufficient disk space in TempDB database. 

Shrink Database Task: – Shrink Database Task can be used to reduce the disk space which is consumed by the database and log files by removing the empty data and log pages. Using this task the space gained after shrinking the database can either be returned to the operating system or it can be retained within the database for its future growth. This task executes the DBCC SHRINKDATABASE statement.

Update Statistics Task: – Update Statistics task ensures that the query optimiser has up-to-date information about the distribution of data values within the tables. This allows the optimizer to make better judgments about the data access strategies it needs to choose. The Update Statistics task basically executes UPDATE STATISTICS statement.

Conclusion - 

This introductory article has contained an overview of the 28 different control flow tasks and 11 Maintenance Plan Tasks which are available in SQL Server 2008 Integration Services. The next articles in this series will walk through the steps to configure and use all the above mentioned Control Flow and Maintenance Plan Tasks.

Saturday, June 14, 2014

Use of Script Task to create SQL table using VB.Net in SSIS

The Below Code will be useful in the cases when we need to deal with flat files, wherein the file structure may be dynamic and we are dealing with multiple files each one with a different schema definition.

The approach will be here is to loop through each file and then read the first line of each file and read the column names from the file and then dynamically create the table and then load the data.

The below code is used to read the column information from the file and then generate a SQL Command which will create the table in the database.

Here it goes - 

Code to dynamically create a table using the Input File Data –

Public Sub Main()
        Dim objReader As New StreamReader(Dts.Variables("SRCDATAFILE").Value.ToString)
        Dim sLine As String = ""
        Dim arrText As New ArrayList()
        sLine = objReader.ReadLine()
        'MsgBox(sLine)
        sLine = sLine.Replace(ControlChars.Tab, "|")
        'MsgBox(sLine)
        sLine = Trim(sLine.Replace(" |", "|").Replace("| ", "|"))
  sLine = "IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE     TABLE_NAME = My_Table') DROP TABLE RBI_Product_stage_tmp; Create Table My_Table ([" & Replace(sLine, "|", "] Varchar(150), [") & "] Varchar(150))"
        'MsgBox(sLine)
        Dts.Variables("CREATESQLCMD").Value = sLine
        If CBool(Dts.Variables("I").Value) Then
            Dts.Variables("CREATESQLCMD").Value = ""
        End If
        objReader.Close()

        Dts.TaskResult = ScriptResults.Success
End Sub

Happy Learning !!

Wednesday, August 14, 2013

Scripting Indexes in SQL 2000

DECLARE IndexCursor CURSOR FOR
SELECT OBJECT_NAME(SI.ID), SI.ID, SI.Name, SI.indid
FROM dbo.sysindexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.ID, 'IsUserTable') = 1
AND INDEXPROPERTY( SI.[id], SI.[name], 'IsStatistics' ) = 0
AND OBJECT_NAME(SI.ID) in
(
'Table1',
'Table2'
)
ORDER BY OBJECT_NAME(SI.ID), SI.indid


DECLARE @IxTable varchar(50)
DECLARE @IxTableID INT
DECLARE @IxName varchar(50)
DECLARE @IxID INT

-- Loop through all indexes
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000)

   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '

   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON [' + @IxTable + '] ('

   -- Get all columns of the index
   --DECLARE IndexCursorColumn CURSOR FOR
   --SELECT SC.Name,0,0
   --FROM dbo.sysindexkeys IC
   --JOIN dbo.syscolumns SC ON IC.ID = SC.ID AND IC.Column_ID = SC.Column_ID
   --WHERE IC.ID = @IxTableID AND indid = @IxID
   --ORDER BY IC.Index_Column_ID--,IC.is_included_column
   DECLARE IndexCursorColumn CURSOR FOR
   SELECT SC.Name,0,0
   FROM dbo.sysindexkeys IC
   JOIN dbo.syscolumns SC ON IC.ID = SC.ID AND IC.colid = SC.colid
   WHERE IC.ID = @IxTableID AND indid = @IxID
   ORDER BY IC.indid--,IC.is_included_column

   DECLARE @IxColumn varchar(50)
   DECLARE @IxIncl bit
   DECLARE @Desc bit
   DECLARE @IxIsIncl bit set @IxIsIncl = 0
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN IndexCursorColumn
   FETCH NEXT FROM IndexCursorColumn INTO @IxColumn, @IxIncl, @Desc
   WHILE (@@FETCH_STATUS = 0)
   BEGIN

      IF (@IxFirstColumn = 1)
            BEGIN
         SET @IxFirstColumn = 0
            END
      ELSE
            BEGIN
                  --check to see if it's an included column
                  IF ((@IxIsIncl = 0) AND (@IxIncl = 1))
                  BEGIN
                   SET @IxIsIncl = 1
                   SET @IXSQL = @IXSQL + ') INCLUDE ('
                  END
                  ELSE
                  BEGIN
                   SET @IXSQL = @IXSQL + ', '
                  END
            END

      SET @IXSQL = @IXSQL + '[' + @IxColumn + ']'
            --check to see if it's DESC
            IF @Desc = 1
                  SET @IXSQL = @IXSQL + ' DESC'

      FETCH NEXT FROM IndexCursorColumn INTO @IxColumn, @IxIncl, @Desc
   END
   CLOSE IndexCursorColumn
   DEALLOCATE IndexCursorColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM IndexCursor INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE IndexCursor

DEALLOCATE IndexCursor