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

Generating a Table Creation Script using SQL Query


DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @CUR_TABLE_NAME CURSOR

DELETE FROM ISGSQLWT01.MONet.dbo.Table_Schema_Info_Prod

SET @CUR_TABLE_NAME = CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where table_name in ('TableName1','TableName2')
OPEN @CUR_TABLE_NAME
FETCH NEXT
FROM @CUR_TABLE_NAME INTO @TABLE_NAME
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  INSERT INTO ISGSQLWT01.MONet.dbo.Table_Schema_Info_Prod
                  SELECT
                        @TABLE_NAME as 'Table Name',
                        c.name as 'Column Name',
                        t.Name as 'Data type',
                        c.[length] as 'Max Length',
                        c.[prec] ,
                        isnull(c.scale,0) ,
                        columnproperty(c.id,c.name,'AllowsNull'),
                        columnproperty(c.id,c.name,'IsIdentity')
                  FROM   
                        syscolumns c , systypes t
                  WHERE
                        c.xtype = t.xtype AND
                        OBJECT_NAME(c.id) = @TABLE_NAME
                       
                FETCH NEXT
                FROM @CUR_TABLE_NAME INTO @TABLE_NAME
            END
CLOSE @CUR_TABLE_NAME
DEALLOCATE @CUR_TABLE_NAME

Thursday, March 28, 2013

Basics of SQL Server Job Scheduling


To create and attach a schedule to a job

1.    In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2.    Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3.    Select the Schedules page, and then click New.
4.    In the Name box, type a name for the new schedule.
5.    Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6.    For Schedule Type, select one of the following:
·         Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
·         Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
·         Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
·         Click one time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

To attach a schedule to a job

1.    In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2.    Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3.    Select the Schedules page, and then click Pick.
4.    Select the schedule that you want to attach, and then click OK.
5.    In the Job Properties dialog box, double-click the attached schedule.
6.    Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7.    In the Job Properties dialog box, click OK.

To prevent a scheduled job from running, you must do one of the following:

·         Disable the schedule.
·         Disable the job.
·         Detach the schedule from the job.
·         Stop the SQL Server Agent service.
·         Delete the schedule.
If the schedule is not enabled, the job can still run in response to an alert or when a user runs the job manually. When a job schedule is not enabled, the schedule is not enabled for any job that uses the schedule.
You must explicitly re-enable a schedule that has been disabled. Editing the schedule does not automatically re-enable the schedule.

Monday, March 25, 2013

Deleting Duplicate Data From SQL Server Table

Duplicate data is a very well known and frequently occurring issue in any DBMS and dealing with deletion of duplicate data is something not that easy for one specially one who is dealing with crucial and huge data.

There are 2 scenarios which can come while deleting duplicate data -

1. when we have an identity column in our table containing duplicates.

The problem becomes much easier here and we can just keep the data with MIN or MAX keys removing all other.


--Create New Table
create table Person
(
ID int identity(1,1),
Name varchar(30),
age int
)

--Insert Data into Table with multiple Duplicate entries

insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Gaurav',26)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)

--Check the data before deleting duplicates
select * from Person

DELETE FROM Person
WHERE ID NOT IN
(
SELECT MIN(ID) FROM Person
GROUP BY Name,age HAVING COUNT(1) > 1
UNION
SELECT MIN(ID) FROM Person
GROUP BY Name,age HAVING COUNT(1) = 1
)

--Check the data After deleting duplicates
select * from Person


Use Below Query --

2. when we don't have any unique identifier in our table and it's a bit tricky here -

there are 2 ways of doing it here again --

(i) using the Row_Number() function comes with only SQL Server 2005 and later versions


--Check if Table already exists

IF EXISTS(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Person')
BEGIN
      --Drop if already exists
      Drop Table Person
END

--Create New Table
create table Person
(
Name varchar(30),
age int
)

--Insert Data into Table with multiple Duplicate entries

insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Gaurav',26)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)

--Check the data before deleting duplicates
select * from Person

;WITH CTE (Name,Age, DuplicateCount)
AS
(
SELECT Name,Age,
ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Name) AS DuplicateCount
FROM Person
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

--Check the data After deleting duplicates
select * from Person


(ii) Here is some piece of code which i will suggest as best to deal with this and can be used anywhere in even the older version of SQL Server like 2000 without using any special functions.


--Check if Table already exists

IF EXISTS(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Person')
BEGIN
      --Drop if already exists
      Drop Table Person
END

--Create New Table
create table Person
(
Name varchar(30),
age int
)

--Insert Data into Table with multiple Duplicate entries

insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Ashish',24)
insert into Person(Name,age) values('Gaurav',26)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)
insert into Person(Name,age) values('Nayan',25)

--Check the data before deleting duplicates
select * from Person

--Declare 2 variables as @name (which is duplicate key) and @duplicateCount as Number of Duplicates
DECLARE @name varchar(30), @duplicateCount int

--Declare a Cursor to iterate through each duplicate name
DECLARE Cursor_Person CURSOR FOR
select Name,(count(1)-1) cnt
from Person
group by Name
having count(1) > 1
OPEN Cursor_Person

FETCH NEXT FROM Cursor_Person INTO @name, @duplicateCount
WHILE @@FETCH_STATUS = 0
BEGIN

--delete all duplicate entries for each duplicate key keeping only one row
delete top (@duplicateCount) from Person where Name = @name

FETCH NEXT FROM Cursor_Person INTO @name, @duplicateCount
END
CLOSE Cursor_Person
DEALLOCATE Cursor_Person

--Check the data After deleting duplicates
select * from Person

Please do comment if you find this post useful, suggestion are appreciated.
Happy Coding !!